Knowledgebase: TroubleShooting
[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 operation directly in MS Excel because Excel is just exporting table in the spreadsheet. Excel is not creating link table like MS Access. If you want to insert/update using MS Excel, then you need to use MS Query which will execute the statement which you enter in the editor or you can write VBA code in excel.

Please refer article for how to write SQL statement 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 below sample code for inserting records through VBA:

Sub test()
Const adOpenStatic = 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.

Please click here to download 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).