[QODBC-ALL] Excel Automation - How to open, refresh and close a company file
Posted by Jack - QODBC Support on 09 August 2016 02:40 PM
Excel Automation - How to open, refresh and close a company file
Before using the MS Excel application, please ensure you have installed the MS Office Component named "Microsoft Query." About MS Query, please refer to Microsoft Office with MS Query and QODBC for more.
It would be best if you opened QuickBooks & load the company file in QuickBooks.
Open MS Excel & create a new spreadsheet.
And then, in the "Choose Data Source" Window, please select a DSN to continue. (Here, I choose the default data source with a sample company file open. And check to Use the Query Wizard to create/edit queries.).
'QuickBooks Data' as an example:
The first time used MS Excel to access QuickBooks. You will be asked to grant access permission for QODBC. After you grant permission for QODBC, you will be guided by Query Wizard. Choose any table and click "Next" until you reach Query Wizard.
Select View data or edit query in Microsoft Query & click Finish:
Write a query & click OK. In this example, I am writing a Trial Balance report query.
You will get the below message box about SQL Query. Click OK.
Report data is available in Microsoft Query:
Click on File & Return Data to Microsoft Excel to save the export report in an Excel spreadsheet.
When you finish the query, choose to save it so that the next time you can directly use it. Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:
Report Data is exported to an Excel spreadsheet.
Now we will disable auto-refresh of data from Data-->Connection-->Properties-->Uncheck Enable Background Refresh & Click OK:
Now we will add a button & we will write code for the button click event. On button click, it will open the connection, refresh the report data & close the connection.
You can add a button from the Developer tab in MS Excel. If the Developer tab is not available, display it.
On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Command Button.
Command Button is added. I have renamed it to Refresh Data. Double-click on the command button to write code.
Write the below code to open the connection, refresh the report data & close the connection.
Report Data will be refreshed by clicking the Refresh Data button.