Re: Connecting Excel to mySQL on worksheet open

[Andrew Ogilvie]

Thanks for the response again Javier, sorry if I misunderstood the info you need.

See below for the settings.config:



<?xml version="1.0" encoding="utf-16"?>
<MySQLForExcel>
<ConvertedSettingsStoredMappingsCasing>True</ConvertedSettingsStoredMappingsCasing>
<GlobalConnectionCommandTimeout>60</GlobalConnectionCommandTimeout>
<EditSessionsReuseWorksheets>True</EditSessionsReuseWorksheets>
<ImportExcelFormatTime>hh:mm:ss</ImportExcelFormatTime>
<ExportShowAllMySqlDataTypes />
<AppendGenerateMultipleInserts />
<GlobalSqlQueriesShowQueriesWithResults>False</GlobalSqlQueriesShowQueriesWithResults>
<GlobalSqlQueriesPreviewQueries>False</GlobalSqlQueriesPreviewQueries>
<AppendConfirmColumnMappingOverwriting />
<ImportCreateExcelTable>True</ImportCreateExcelTable>
<StoredDataMappings />
<ImportExcelTableStyleName>MySqlDefault</ImportExcelTableStyleName>
<ImportEscapeFormulaTextValues>True</ImportEscapeFormulaTextValues>
<AppendSqlQueriesDisableIndexes />
<ImportConnectionInfosList>&lt;?xml version="1.0" encoding="utf-16"?&gt;
&lt;ArrayOfImportConnectionInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
&lt;ImportConnectionInfo ConnectionId="{45b7d3f0-2639-4083-81ff-b8efa19e8d39}" ExcelTableName="dbt_std.Standards_work_orders" HostIdentifier="Mysql@192.168.1.199:3306" ImportColumnNames="true" LastAccess="2016-04-06T16:23:02.9142647-04:00" OperationType="ImportTableOrView" ProcedureResultSetIndex="0" SchemaName="dbt_std" SelectQuery="SELECT * FROM `dbt_std`.`Standards_work_orders`" ConnectionInfoError="None" TableName="Standards_work_orders" WorkbookFilePath="C:\Users\DAN\Desktop\Production - Copy.xlsx" WorkbookGuid="9bbab514-a506-4d88-9592-31af08878c77" WorkbookName="Production - Copy.xlsx" WorksheetName="pcsStandardsDB" /&gt;
&lt;ImportConnectionInfo ConnectionId="{45b7d3f0-2639-4083-81ff-b8efa19e8d39}" ExcelTableName="dbt_std.Standards_work_orders.2" HostIdentifier="Mysql@192.168.1.199:3306" ImportColumnNames="true" LastAccess="2016-04-11T09:46:59.7450251-04:00" OperationType="ImportTableOrView" ProcedureResultSetIndex="0" SchemaName="dbt_std" SelectQuery="SELECT * FROM `dbt_std`.`Standards_work_orders`" ConnectionInfoError="None" TableName="Standards_work_orders" WorkbookFilePath="C:\Users\DAN\Desktop\Production - Copy.xlsx" WorkbookGuid="a79d47e5-889b-4755-a1a4-6e89f6a3665c" WorkbookName="Production - Copy.xlsx" WorksheetName="pcsStandardsDB" /&gt;
&lt;ImportConnectionInfo ConnectionId="{45b7d3f0-2639-4083-81ff-b8efa19e8d39}" ExcelTableName="dbt_std.Standards_work_orders" HostIdentifier="Mysql@192.168.1.199:3306" ImportColumnNames="true" LastAccess="2016-04-07T15:22:03.1327761-04:00" OperationType="ImportTableOrView" ProcedureResultSetIndex="0" SchemaName="dbt_std" SelectQuery="SELECT * FROM `dbt_std`.`Standards_work_orders`" ConnectionInfoError="None" TableName="Standards_work_orders" WorkbookFilePath="C:\Users\DAN\Desktop\tzz.xlsx" WorkbookGuid="f260989e-fc71-40a7-baa8-1fadb79d81d9" WorkbookName="tzz.xlsx" WorksheetName="Sheet1" /&gt;
&lt;/ArrayOfImportConnectionInfo&gt;</ImportConnectionInfosList>
<ExportUseFormattedValues />
<ExportSqlQueriesCreateIndexesLast>False</ExportSqlQueriesCreateIndexesLast>
<ImportPreviewRowsQuantity>10</ImportPreviewRowsQuantity>
<EditConnectionInfosList>&lt;?xml version="1.0" encoding="utf-16"?&gt;
&lt;ArrayOfEditConnectionInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xmlns:xsd="http://www.w3.org/2001/XMLSchema"; /&gt;</EditConnectionInfosList>
<ExportGenerateMultipleInserts />
<HideLocalizedDateFormatNames />
<ConnectionInfosLastAccessDays>30</ConnectionInfosLastAccessDays>
<ExportDetectDatatype />
<DeleteAutomaticallyOrphanedConnectionInfos>False</DeleteAutomaticallyOrphanedConnectionInfos>
<AppendUseFormattedValues />
<EditUseOptimisticUpdate>True</EditUseOptimisticUpdate>
<ExportAutoAllowEmptyNonIndexColumns />
<AppendReloadColumnMapping />
<ExportAddBufferToVarchar />
<EditSessionsRestoreWhenOpeningWorkbook>True</EditSessionsRestoreWhenOpeningWorkbook>
<AppendPerformAutoMap />
<EditPreviewMySqlData>True</EditPreviewMySqlData>
<ExportLimitPreviewRowsQuantity />
<SchemasDisplayCollations />
<ImportExcelFormatLongDates>m/d/yyyy h:mm</ImportExcelFormatLongDates>
<ImportPrefixExcelTableText>MySQL</ImportPrefixExcelTableText>
<AdjustedMultipleInsertFlags>True</AdjustedMultipleInsertFlags>
<AppendAutoStoreColumnMapping />
<AppendLimitPreviewRowsQuantity />
<ImportExcelFormatShortDates>m/d/yyyy</ImportExcelFormatShortDates>
<ImportPrefixExcelTable>False</ImportPrefixExcelTable>
<AppendShowDataTypes />
<GlobalConnectionConnectionTimeout>15</GlobalConnectionConnectionTimeout>
<ExportAutoIndexIntColumns />
</MySQLForExcel>


Of the 3 listings in the connections area, the one called tzz.xlsx was just a test sheet
For some reason there are two instances of the Production - Copy.xlsx
The kpi_macro.xlsm does not appear in the list.

I attempted saving the macro enabled file as a normal xlsx, and re-importing the SQL data.

When I open the file I get an error:
"Excel Tables Reconnection"
with details:
"The following connections were not found:
Mysql@192.168.1.199:3306"