[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 MS Excel application, please make sure you have installed MS Office Component named "Microsoft Query". About MS Query, please refer to Microsoft Office with MS Query and QODBC for more.
You need to open QuickBooks & load 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 Use the Query Wizard to create/edit queries.).
'QuickBooks Data' as an example:
The first time uses 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 click "Next" until you reach to Query Wizard.
Select View data or edit query in Microsoft Query & click Finish:
Write query & click OK. In this example, I am writing Trial Balance report query.
You will get 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 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 Excel spreadsheet.
Now we will disable auto-refresh of data from Data-->Connection-->Properties-->Uncheck Enable Background Refresh & Click OK:
Now we will add button & we will write code for button click event. On button click, it will open connection, refresh report data & close connection.
You can add button from 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 below code to open connection, refresh report data & close connection.
Report Data will be refreshed on clicking Refresh Data button.