Connecting multiple QuickBooks companies via Excel

Download Sample

There is a limitation with the QuickBooks SDK: it can connect only one company file to a user session at a time.

It means you cannot access multiple company files at the same time.

You need to close the connection with one company file and switch to another. ( i.e., connection close for company A and Connection open for company b should have a gap of 15-30 seconds for switching operation.)

In this article, We will connect to two different company files using two different QODBC DSN with auto login configured. In this example, we will use two auto logins, DSN CompanyA & CompanyB.

Please Refer:
How to setup QODBC for Auto-login into QuickBooks
How to set up QODBC Driver to auto-connect to QuickBooks. (Video)

Here is my DSN configuration for the First Company file.

It would be best if you opened QuickBooks & load the company file which you have chosen in auto login DSN. The company file path in QuickBooks & DSN should be matched.

Open MS Excel & create a new spreadsheet.
First, please select the "Data" tab, Navigate to "From Other Sources"->" From Microsoft Query."

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.)

'CompanyA' as an example:

The first time use 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.
sp_report TrialBalance show Debit_Title, Credit_Title, Label, Debit, Credit parameters DateMacro = 'LastMonth', ReportBasis = 'Cash'

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 Excel sheet1.

Repeating the above steps will export another report in sheet 2 using DSN CompanyB.

We need to close the first company file, which is opened in QuickBooks.

Here is my DSN configuration for the Second Company file.

It would be best if you opened QuickBooks & load the company file which you have chosen in auto login DSN. The company file path in QuickBooks & DSN should be matched.

Navigate to sheet2
First, please select the "Data" tab, Navigate to "From Other Sources"->" From Microsoft Query."

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.)

'CompanyB' as an example:

The first time use 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 Profit & Loss report query.
sp_report ProfitAndLossStandard show Amount_Title, Text, Label, Amount parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'

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 Excel sheet2.

After the export is complete, close the company file opened in QuickBooks.

Now we will disable auto-refresh of data from Data-->Connection-->Properties-->Uncheck Enable Background Refresh & Click OK:

We need to disable auto-refresh data for both company file connections (i.e., Query from CompanyA & Query from CompanyB ).

Now we will add a button & we will write code for the button click event. On button click, it will open the connection for CompanyA, refresh the report data & close the connection for CompanyA, sleep for 15 seconds, open the connection for CompanyB, refresh the report data & close the connection for CompanyB.

You can add a button from the Developer tab in MS Excel. If the Developer tab is not available, display it.

Display the Developer tab
Click the File tab, click Options, and then click the Customize Ribbon category.
Select the Developer check box in the Main Tabs list, and click OK.

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 a connection for Company A, refresh the report data & close the connection for Company A, sleep for 15 seconds, open the connection for CompanyB, restore the report data & close the connection for CompanyB.

Fifteen seconds of sleep is required to close the company file and swiswitch to another private Sub CommandButton1_Click()
ActiveWorkbook.Connections("Query from CompanyA").Refresh
Application.Wait (Now + TimeValue("0:00:15"))
ActiveWorkbook.Connections("Query from CompanyB").Refresh
End Sub

Report Data will be refreshed by clicking the Refresh Data button.

Keywords: QB win log with multiple companies, log in with various companies