Knowledgebase: Tutorials
[QODBC-Desktop] How to Insert information from MS Excel spreadsheet to QuickBooks using QODBC
Posted by Jack - QODBC Support on 02 November 2015 01:05 PM

Problem Description:

How to insert employee time information from MS Excel time spreadsheet into QuickBooks using QODBC

Solution:

You cannot perform insert/update operations directly in MS Excel because Excel is just exporting tables in the spreadsheet. Excel is not creating link tables like MS Access. If you want to insert/update using MS Excel, then you need to use MS Query, which will execute the statement you enter in the editor, or you can write VBA code in excel.

Please refer article for how to write SQL statements using MS Query: Microsoft Office with MS Query and QODBC.

You can use ADO in VB connection to connect to QuickBooks via VBA in MS Excel.

Please refer to the sample code for inserting records through VBA:

Sub test()
Const adopt static = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
sSQL = "INSERT INTO TimeTracking (EntityRefListID, DurationMinutes, TxnDate, CustomerRefFullName, ItemServiceRefFullName, PayrollItemWageRefFullName) VALUES ( '370000-933272659', 480, {d'2007-12-18'}, 'Pretell Real Estate:75 Sunset Rd.', 'Removal,' 'Salary')."
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oConnection.Execute (sSQL)
sMsg = sMsg & "Record Added!!!"
MsgBox sMsg
Set oRecordset = Nothing
Set oConnection = Nothing
End Sub

Please Note: All query values are coming from sample company files. Please change it according to your company file value before using them.

Please click here to download a sample MS Excel file.

(1 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).