I have this script that a co-worker that is on vaction wrote on his pc with the demo versions of Quickbooks and the QODBC driver.
I get the " Expedted Lexical element not found" error when I try to run the script.
I am on a workstation using a data file up on the server
here is the script Any advice would be appreciated
Thank you
David
-----------------------------------------------------
Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL
Const ForReading = 1
Const ForWriting = 2
strFile = "C:\OrderIn.txt"
' Create the File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strFile) Then
'Do Nothing
Else
Set objFile = objFSO.CreateTextFile(strFile)
End If
'Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\OrderNum.txt", ForReading)
strText = objFile.ReadAll
vOrderID = strText
strText = MID(strText,1,15)
strText = TRIM(strText)
strText = Date() - 30
strText = MID(strText,7,4) & "-" & MID(strText,1,2) & "-" & MID(strText,4,2)
sSQL = "SELECT * FROM INVOICE WHERE REFNUMBER > '1' AND SHIPDATE >= {d " & Chr(39) & strText & Chr(39) & "}"
sConnectString = "DSN=QuickBooks;OLE DB Services=-2;"
Set oConnection = CreateObject("ADODB.Connection")
Set objFile = objFSO.OpenTextFile("C:\OrderIn.txt", ForWriting)
oConnection.Open sConnectString
SET oRecordset = oConnection.execute(sSQL)
Do While Not oRecordSet.eof
If IsNull(oRecordSet.Fields("REFNUMBER")) = False Then
strNewText = oRecordSet.Fields("REFNUMBER") & Space(11 - Len(oRecordSet.Fields("REFNUMBER")))
Else
strNewText = Space(11)
End If
If IsNull(oRecordSet.Fields("REFNUMBER")) = False Then
strNewText = strNewText & "L" & oRecordSet.Fields("REFNUMBER") & Space(10 - Len(oRecordSet.Fields("REFNUMBER")))
Else
strNewText = strNewText & Space(11)
End If
if isNull(oRecordSet.Fields("ShipAddressAddr1")) = False then
strNewText = strNewText & oRecordSet.Fields("ShipAddressAddr1") & Space(41 - Len(oRecordSet.Fields("ShipAddressAddr1")))
else
strNewText = strNewText & Space(11)
end if
if isnull(oRecordSet.Fields("ShipAddressAddr2")) = False Then
strNewText = strNewText & oRecordSet.Fields("ShipAddressAddr2") & Space(41 - Len(oRecordSet.Fields("ShipAddressAddr2")))
else
strNewText = strNewText & Space(41)
end if
if isnull(oRecordSet.Fields("ShipAddressAddr3")) = False Then
strNewText = strNewText & oRecordSet.Fields("ShipAddressAddr3") & Space(41 - Len(oRecordSet.Fields("ShipAddressAddr3")))
else
strNewText = strNewText & Space(41)
end if
if isnull(oRecordSet.Fields("ShipAddressAddr4")) = False then
strNewText = strNewText & oRecordSet.Fields("ShipAddressAddr4") & Space(41 - Len(oRecordSet.Fields("ShipAddressAddr4")))
else
strNewText = strNewText & Space(41)
end if
if isnull(oRecordSet.Fields("ShipAddressAddr5")) = False then
strNewText = strNewText & oRecordSet.Fields("ShipAddressAddr5") & Space(41 - Len(oRecordSet.Fields("ShipAddressAddr5")))
else
strNewText = strNewText & Space(41)
end if
if isnull(oRecordSet.Fields("ShipAddressCity")) = False then
strNewText = strNewText & oRecordSet.Fields("ShipAddressCity") & Space(31 - Len(oRecordSet.Fields("ShipAddressCity")))
else
strNewText = strNewText & Space(31)
end if
if isnull(oRecordSet.Fields("ShipAddressState")) = False then
strNewText = strNewText & oRecordSet.Fields("ShipAddressState") & Space(31 - Len(oRecordSet.Fields("ShipAddressState")))
else
strNewText = strNewText & Space(31)
end if
if isnull(oRecordSet.Fields("ShipAddressPostalCode")) = False then
strNewText = strNewText & oRecordSet.Fields("ShipAddressPostalCode") & Space(13 - Len(oRecordSet.Fields("ShipAddressPostalCode")))
else
strNewText = strNewText & "jjj" & Space(13)
end if
if isnull(oRecordSet.Fields("ShipAddressCountry")) = False then
strNewText = strNewText & oRecordSet.Fields("ShipAddressCountry") & Space(31 - Len(oRecordSet.Fields("ShipAddressCountry")))
else
strNewText = strNewText & Space(31)
end if
if isnull(oRecordSet.Fields("CustomerRefListID")) = False then
strNewText = strNewText & oRecordSet.Fields("CustomerRefListID") & Space(36 - Len(oRecordSet.Fields("CustomerRefListID")))
else
strNewText = strNewText & Space(36)
end if
if isnull(oRecordSet.Fields("ShipMethodRefListID")) = False then
strNewText = strNewText & oRecordSet.Fields("ShipMethodRefListID") & Space(36 - Len(oRecordSet.Fields("ShipMethodRefListID")))
else
strNewText = strNewText & Space(36)
end if
if isnull(oRecordSet.Fields("ShipMethodRefFullName")) = False then
strNewText = strNewText & oRecordSet.Fields("ShipMethodRefFullName") & Space(15 - Len(oRecordSet.Fields("ShipMethodRefFullName")))
else
strNewText = strNewText & Space(15)
end if
'Get data from customer table
if isnull(oRecordSet.Fields("CustomerRefListID")) = False then
sSQL = "SELECT * FROM CUSTOMER WHERE LISTID = '" & oRecordSet.Fields("CustomerRefListID") & "'"
SET oERecordset = oConnection.execute(sSQL)
End If
if isnull(oERecordSet.Fields("EMAIL")) = False then
strNewText = strNewText & oERecordSet.Fields("email") & Space(60 - Len(oERecordSet.Fields("email")))
else
strNewText = strNewText & Space(60)
end if
if isnull(oERecordSet.Fields("CompanyName")) = False then
strNewText = strNewText & oERecordSet.Fields("CompanyName") & Space(41 - Len(oERecordSet.Fields("CompanyName")))
else
strNewText = strNewText & Space(41)
end if
if isnull(oERecordSet.Fields("Contact")) = False then
strNewText = strNewText & oERecordSet.Fields("Contact") & Space(41 - Len(oERecordSet.Fields("Contact")))
else
strNewText = strNewText & Space(41)
end if
if isnull(oERecordSet.Fields("Phone")) = False then
strNewText = strNewText & oERecordSet.Fields("Phone") & Space(21 - Len(oERecordSet.Fields("Phone")))
else
strNewText = strNewText & Space(21)
end if
objFile.WriteLine strNewText
intcnt = intcnt + 1
oRecordSet.movenext
Loop
oConnection.Close
objFile.Close
Set oConnection = Nothing
MsgBox("Total Orders found " & intcnt)
| 
