MySQL Forums
Forum List  »  MySQL for Excel

Re: Connecting Excel to mySQL on worksheet open
Posted by: Andrew Ogilvie
Date: April 11, 2016 10:10AM

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"

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Connecting Excel to mySQL on worksheet open
1560
April 11, 2016 10:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.