I have created an 2007 Excel spreadsheet

Here is the macro:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Dim oConnection
Dim oRecordset
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open "DSN=Quickbooks Data;OLE DB Services=-2"
oRecordset.CursorLocation = adUseClient
oRecordset.Open "SELECT InvoiceLine.TxnNumber, InvoiceLine.CustomerRefListID, InvoiceLine.CustomerRefFullName, InvoiceLine.TxnDate, InvoiceLine.RefNumber, InvoiceLine.PONumber, InvoiceLine.FQSaveToCache, InvoiceLine.InvoiceLineType, InvoiceLine.InvoiceLineSeqNo, InvoiceLine.InvoiceLineItemRefFullName, InvoiceLine.InvoiceLineDesc, InvoiceLine.InvoiceLineQuantity, InvoiceLine.InvoiceLineRate, InvoiceLine.InvoiceLineAmount, InvoiceLine.CustomFieldInvoiceLineOther1 FROM InvoiceLine InvoiceLine WHERE (InvoiceLine.PONumber='T481')", oConnection, adOpenStatic, adLockOptimistic
oRecordset.Open "INSERT INTO INVOICELINE  (PONumber,InvoiceLineType,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineQuantity,InvoiceLineRate,CustomFieldInvoiceLineOther1,FQSaveToCache, CustomerRefListId,CustomerRefFullName,TxnDate,RefNumber) VALUES ( 'T481','Item','Vinyl','01201a - 41.25"" Crown Football',3,50.220,'06173',0,'1D00000-1206727740','DC Media',{d'2009-01-20'},'2')", oConnection, adOpenStatic, adLockOptimistic

oRecordset.Close
oConnection.Close

I can not figure out why this does not work.  Without the insert statement, it should at least return something.  It returns data but it is not the data from my select.  It returns other data from the invoice line table.  It is as if it is using another connection query.

If I try to re-run query I get the message Operation not allowed when open on INSERT.  Do I mis-understand macros completely.

I think the Excel Macro is fine, it's just your ADO code for the INSERT which really needs to be a Recordset.Update. Try looking at:  Can I get some examples of how to use QODBC via Visual Basic? 

Here is new macro

Sub Macro1()
'
' Macro1 Macro
'
'This creates one invoice with three lines. Note the FQSaveToCache field, set to True except on the last line.

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Dim oConnection
Dim oRecordset
Dim sLastVendor
Dim dTotalApplied
Dim dAmountDue

Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

oConnection.Open "DSN=Quickbooks Data;OLE DB Services=-2"
oRecordset.CursorLocation = adUseClient
oRecordset.Open "SELECT * FROM InvoiceLine WHERE TxnId = 'X'", oConnection, adOpenStatic, adLockOptimistic

oRecordset.AddNew()
oRecordset.Fields("RefNumber").Value = "1"
oRecordset.Fields("PONumber").Value = "T1234"
oRecordset.Fields("InvoiceLineType").Value = "Item"
oRecordset.Fields("InvoiceLineDesc").Value = "Building permit 1"
oRecordset.Fields("InvoiceLineQuantity").Value = 2
oRecordset.Fields("InvoiceLineRate").Value = 1.25
oRecordset.Fields("CustomFieldInvoiceLineOther1").Value = "12345"
oRecordset.Fields("CustomerRefListID").Value = "1D00000-1206727740"
oRecordset.Fields("InvoiceLineItemRefListID").Value = "900000-1188165755"
oRecordset.Fields("FQSaveToCache").Value = True
oRecordset.Update()

oRecordset.AddNew()
oRecordset.Fields("RefNumber").Value = "1"
oRecordset.Fields("PONumber").Value = "T1234"
oRecordset.Fields("InvoiceLineType").Value = "Item"
oRecordset.Fields("CustomerRefListID").Value = "1D00000-1206727740"
oRecordset.Fields("InvoiceLineItemRefListID").Value = "900000-1188165755"
oRecordset.Fields("InvoiceLineDesc").Value = "Building permit 2"
oRecordset.Fields("InvoiceLineQuantity").Value = 2
oRecordset.Fields("InvoiceLineRate").Value = 1.25
oRecordset.Fields("CustomFieldInvoiceLineOther1").Value = "12345"
oRecordset.Fields("FQSaveToCache").Value = True
oRecordset.Update()

oRecordset.AddNew()
oRecordset.Fields("RefNumber").Value = "1"
oRecordset.Fields("PONumber").Value = "T1234"
oRecordset.Fields("InvoiceLineType").Value = "Item"
oRecordset.Fields("CustomerRefListID").Value = "1D00000-1206727740"
oRecordset.Fields("InvoiceLineItemRefListID").Value = "900000-1188165755"
oRecordset.Fields("InvoiceLineDesc").Value = "Building permit 3"
oRecordset.Fields("InvoiceLineRate").Value = 3
oRecordset.Fields("InvoiceLineQuantity").Value = 3
oRecordset.Fields("CustomFieldInvoiceLineOther1").Value = "12345"
oRecordset.Fields("FQSaveToCache").Value = False
oRecordset.Update()

oRecordset.Close
oConnection.Close
'
End Sub

Now I get  Syntax error on AddNew

I am still getting errors

Sub Macro1()
'
' Macro1 Macro
'
'This creates one invoice with three lines. Note the FQSaveToCache field, set to True except on the last line.

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Dim oConnection
Dim oRecordset
Dim sLastVendor
Dim dTotalApplied
Dim dAmountDue

Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

oConnection.Open "DSN=Quickbooks Data;OLE DB Services=-2"
oRecordset.CursorLocation = adUseClient
'oRecordset.Open "SELECT * FROM InvoiceLine WHERE TxnId = ""160DC-1231779028""", oConnection, adOpenStatic, adLockOptimistic
oRecordset.Open "SELECT * FROM InvoiceLine WHERE TxnNumber = 1110085", oConnection, adOpenStatic, adLockOptimistic

oRecordset.AddNew()
oRecordset.Fields("RefNumber").Value = "1"
oRecordset.Fields("PONumber").Value = "T1234"
oRecordset.Fields("InvoiceLineType").Value = "Item"
oRecordset.Fields("InvoiceLineDesc").Value = "Building permit 1"
oRecordset.Fields("InvoiceLineQuantity").Value = 2
oRecordset.Fields("InvoiceLineRate").Value = 1.25
oRecordset.Fields("CustomFieldInvoiceLineOther1").Value = "12345"
oRecordset.Fields("CustomerRefListID").Value = "1D00000-1206727740"
oRecordset.Fields("InvoiceLineItemRefListID").Value = "900000-1188165755"
oRecordset.Fields("FQSaveToCache").Value = True
oRecordset.Update()

'oRecordset.AddNew()
'oRecordset.Fields("RefNumber").Value = "1"
'oRecordset.Fields("PONumber").Value = "T1234"
'oRecordset.Fields("InvoiceLineType").Value = "Item"
'oRecordset.Fields("CustomerRefListID").Value = "1D00000-1206727740"
'oRecordset.Fields("InvoiceLineItemRefListID").Value = "900000-1188165755"
'oRecordset.Fields("InvoiceLineDesc").Value = "Building permit 2"
'oRecordset.Fields("InvoiceLineQuantity").Value = 2
'oRecordset.Fields("InvoiceLineRate").Value = 1.25
'oRecordset.Fields("CustomFieldInvoiceLineOther1").Value = "12345"
'oRecordset.Fields("FQSaveToCache").Value = True
'oRecordset.Update()

'oRecordset.AddNew()
'oRecordset.Fields("RefNumber").Value = "1"
'oRecordset.Fields("PONumber").Value = "T1234"
'oRecordset.Fields("InvoiceLineType").Value = "Item"
'oRecordset.Fields("CustomerRefListID").Value = "1D00000-1206727740"
'oRecordset.Fields("InvoiceLineItemRefListID").Value = "900000-1188165755"
'oRecordset.Fields("InvoiceLineDesc").Value = "Building permit 3"
'oRecordset.Fields("InvoiceLineRate").Value = 3
'oRecordset.Fields("InvoiceLineQuantity").Value = 3
'oRecordset.Fields("CustomFieldInvoiceLineOther1").Value = "12345"
'oRecordset.Fields("FQSaveToCache").Value = False
'oRecordset.Update()

oRecordset.Close
oConnection.Close
'
End Sub

Now it says Compile error Expecting = on the recordset.add command