Re: Connecting Excel to mySQL on worksheet open

[Andrew Ogilvie]

I will start with the second error as it is the larger issue for me (for the workbook it is working in)

-open the file (KPI_macro.xlsm)

-password protected file, input password

-excel popup:
"This workbook contains links to one or more external sources that could be unsafe.
If you trust the links, update them to get the latest data. Otherwise, you can keep working with the data you have."

-Select update
-On the ribbon, go to Data

-Press refresh dropdown, and select refresh all

-An error ocurred:
An error ocurred trying to refresh data in Excel table "dbt_stf.Standards_work_orders.2"

-Show details:
"This error is most likely happening because data was imported from a MySQL table or view, and when the Excel Workbook was saved MySQL for Excel could not store the connection information necessary to refresh the data of the Excel table (ListObject).

In order to avoid receiving this error message each time the Excel table is refreshed, please delete the Excel table and import the data again before saving the Excel Workbook."




When the data was first imported through the MySQL for Excel plugin, I created the new connection, and input the login credentials. The connection name, I named the same as the default schema. It is a remote connection.

Once logged in, I go to schemas, dbt_std (its name)

I then have a handful of "Views" which I have in the database which I am importing.

I select a blank cell (A1) on one of my worksheets, click the view I want to import, and select Import MySQL Data.

The preview is correct to the data I want imported, I do not change any settings, and press import.

I then have the data in my workbook, and the data will refresh.

If I save the workbook, close excel, and reopen the workbook, the same error happens





The connections.xml data is below:

<?xml version="1.0" encoding="utf-8"?>
<data grt_format="2.0">
<value type="list" content-type="object" content-struct-name="db.mgmt.Connection">
<value type="object" struct-name="db.mgmt.Connection" id="{45b7d3f0-2639-4083-81ff-b8efa19e8d39}" struct-checksum="0xefea20d5">
<link type="object" struct-name="db.mgmt.Driver" key="driver">com.mysql.rdbms.mysql.driver.native</link>
<value type="string" key="hostIdentifier">Mysql@192.168.1.199:3306</value>
<value type="dict" key="parameterValues">
<value type="string" key="hostName">192.168.1.199</value>
<value type="int" key="port">3306</value>
<value type="string" key="schema">dbt_std</value>
<value type="string" key="userName">***removed***</value>
</value>
<value type="string" key="name">dbt_std</value>
</value>
</value>
</data>