Knowledgebase: Microsoft Products
[QODBC-Desktop] QODBC and Microsoft Excel
Posted by Juliet (QODBC Support) on 05 January 2010 05:31 AM

QODBC and Microsoft Excel

Microsoft Excel 2010 and QODBC

For MS Access 64-bit, Please refer: To how to Configure QODBC to Work With 64-bit MS Excel.

Setup QODBC to use with MS Excel 2010

Note: QODBC allows you to quickly merge live QuickBooks data into the cells of your Microsoft Excel worksheets for quick calculations and graphs. The data will retain a live link to QuickBooks, and one click will update the worksheet to the most current data in the linked QuickBooks table.

Before using the MS Excel application, please make sure you have installed MS Office Component named "Microsoft Q."ry." About MS Query, please refer to Microsoft Office with MS Query and QODBC for more.

Before starting, please make use you have the 32-bit version of MS Excel:

First, please select the "Data" tab, and Navigate to  "From Other Sources"->"From Microsoft Q.uery."

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 you use MS Excel to access QuickBooks, you will be asked to grant access permission for this application:

Select the "Yes, always" option, and then click the "Continue" button, in the next window, click the "Done" button to finish authorizing.

Import QuickBooks data into MS Excel via MS Query

After you grant permission for QODBC, you will be guided by Query Wizard. In the Choose Columns window, select a table and the columns you want to import. Choose the table you wish to import and select the columns from the table to import by pressing the ">" button. (Here, I choose table Customer as a sample.) You can add or remove columns at the correct list "Columns in your q.uery."

You can set a filter for your query in this window. If you don't want to set any query, please click the "Next" button:

After the setup is finished, MS Excel will ask you to choose an edit method for the records:

Return Data to Microsoft Excel:

This method will directly export the data to Excel, you can view and edit these records, but the data is static, modification will not be uploaded into QuickBooks.

You can view the detailed connection properties by clicking "connection" button in the Data menu:

In the Workbook Connection Window, click the "Properties" button to view detailed information:

Now you can view the connection string and its SQL statement in the definition window:

View Data or Edit Query in MS Query:

This method will lead you to MS Query window and you can view or edit data via SQL Statement.

Click the "SQL" button to view the SQL Statement of this query:

The SQL window will show the SQL Statement, and the user can also modify the statement to extract other records:

 

The desired recordset is to contain only the records with a Name having a value equaling "Residential." Select the field header name and click on it, next select Criteria -> Add Criteria. In the popped-out, Add Criteria window, specify the criteria Field as "Name," Operator as "equals," then click the Value button, and from the list of values, select "Residential" -> OK -> Add.

 

 

 

 

The following screen reflects the applied criteria to the query and the resulting QuickBooks recordset:

 

When you finish the query, choose to save, so that next time you can directly use the same query:

Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:

 

      Clicking Data -> Refresh Data or clicking the External Data toolbar will update the data in the worksheet from the latest information in the linked QuickBooks tables as shown above. Chanade to the QuickBooks files will not affect this worksheet until you refresh the data with the Refresh option.

Also Refer:
How to Use QODBC with MS Excel 2007
Using QuickBooks Data with Excel 2013 32-bit
How to Configure QODBC to Work With 64-bit MS Excel

(334 vote(s))
Helpful
Not helpful

Comments (11)
Dave Rupp
10 May 2013 08:38 PM
I install the trial 32 bit version on Windows 7 Pro(32 bit) using Office 2007. The VB Demo works fine, but when I try to access QB online using Excel it fails after the Import Data popup with [QODBC]invalid connection string. I know the string is correct since the VB demo works. How can I get this working in Excel. The next step will be to make it work using Access 2013 on a 64 bit Windows 8 Pro PC.
Scott
26 December 2013 05:49 PM
Do I understand correctly that to push changes back into QB, you must use MS Query and not Excel?
Jack
08 July 2014 10:16 AM
Hi Scott,

You can push data in QuickBooks either using MS Query or you can use VBA in MS Excel.

Or

You can use linked table in MS Access which directly push data to QuickBooks.

Please refer:
http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2448/0/how-to-use-qodbc-with-microsoft-access-2007
Alexie
30 June 2015 04:55 PM
Hi,

How do I get the information that imports to be a report like a balance sheet, rather than just being customer or account information?

Thanks,
Alexie
Jack
09 July 2015 08:20 AM
Hi Alexie,

Please refer below mentioned article for exporting reports in MS Excel:

http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2405/57/how-to-create-sp_reports-using-microsoft-excel-2007
Chris
06 September 2015 05:19 PM
I would like to export a single value from quickbooks to a single cell. I'm trying to do a budget-to-actual schedule in excel.

For example, I've budgeted 20k in office expenses in the first quarter of the fiscal year. This has been documented in excel in Cell A2. In cell A3, I'd like to retrieve the actual figure from the quickbooks file for the office expense account for the same date range. How would I do this? Is crystal reports a better option?

Many thanks,
Jack
08 September 2015 08:06 AM
Hi Chris,

You need to maintain report column sequence in sp_report to achieve this.

For Example:
sp_report ProfitAndLossBudgetVsActual show required_column1,required_column2 parameters FiscalYear = 2003, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

Note: You need to change required_column1, required_column2 as per your requirement. Also, you can change parameters as per your requirements.
Orry Skelton
25 September 2015 07:31 PM
Can I simply add a profit and loss report in excel and have it update automatically as the information changes in quickbooks?
Jack
28 September 2015 07:44 AM
Hi,

You can export report in MS Excel & can refresh report by clicking refresh button in excel.

Please refer below mentioned article for exporting reports in MS Excel:

http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2405/57/how-to-create-sp_reports-using-microsoft-excel-2007

Please refer below mentioned article for How to use the QuickBooks Reporting Engine with QODBC:

http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2369/0/how-to-use-the-quickbooks-reporting-engine-with-qodbc
alex Lin
17 February 2016 03:05 PM
Why I can't access my invoice table in Excel and Access 2016? QODBC connection fine.
Jack
18 February 2016 07:30 AM
Hi Alex,

For Office 2016 issue:

Please refer below mentioned link:

http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2718

If above does not resolve the issue, then please follow below alternate solution(Manual Steps):

http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2600

http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2504

If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:

http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, I kindly request you to attach below-listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).