View Single Post
 
Old 10-03-2017, 10:51 PM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

When you are opening the linked file, you'll get an Excel message like "[Microsoft] [ODBC Excel Driver] The Microsoft ...", because ODBC Query points to same workbook in location, which doesn't exist for you. To get the workbook to work properly:

1. Save the attached workbook to some location (in your computer/in your network resource);
2. Open the workbook (for Excel message, click OK);
3. Select from Excel Menu Data > Connections. The Workbook Connections window opens. You see one connection - qSummary;
4. As there is a single connection, you don't need to select the connection - simply click the Properties button at right of Workbook Connections window. The Connection Properties window opens;
5. Activate Definition tab;
6. In Connection string field is current Connection string displayed (DSN=Excel Files;DBQ=C:\USERS\ALS\DOCUMENTS\QuestionBook.xlsx;DefaultDir=C:\USERS\ALS\DOCUMENTS;DriverId=1046;MaxBufferSize=2048;PageTimeout=5. Edit part of Connection string colored red according workbook location and name in your computer/network;
7. In Command text field the Query string is displayed (SELECT ... FROM `C:\USERS\ALS\DOCUMENTS\QuestionBook.xlsx`.nRegister nRegister
...). Edit part of Command text colored red according workbook location and name in your computer/network;
8. OK. Refresh (when all was done properly, the query is refreshed and you get no error messages). Close.

The ODBC query is set to be refreshed when you open the workbook. When you edit or add data to Register sheet, and you want the summary to be recalculated, select from menu Data > Refresh All, or right-click anywhere on summary table, and select Refresh from drop-down menu.

Whenever you move the workbook to new location, or rename the workbook, you have to edit the Connection string and Command text again (a drawback of ODBC queries).
Or you have to write an Open event in VBA which rewrites these automatically (NB! Then you have to save the workbook in .xlsm format).
Reply With Quote