下面的函数基于我用来查找旧产品ID并将其替换为我们新系统中的产品ID的类似函数。这还要求输入文件具有标题行,例如:
'sraDOH.txt:
'SSN, DOH
'578171533,2015-01-01 00:00:00
'213396391,2015-06-01 00:00:00
'077966385,2015-01-01 00:00:00
'216418521,2015-01-01 00:00:00
Function getDOH(consumerSSN)
Dim fso : Set fso = CreateObject("Scripting.FileSystemObject")
Dim iConnection 'Connection objects for input
Dim iRecordset 'RecordSets for input
Dim dDOH ' date to look up
Dim sInputFileDir ' Directory containing the TXT file to process
Dim sInputTXT ' Name of the TXT file to open (without a pathname)
sInputFileDir = fso.GetAbsolutePathName("..\Test Files\")
sInputTXT = "sraDOH.txt"
'
'Open text file
'
Set iConnection = CreateObject("ADODB.Connection")
Set iRecordset = CreateObject("ADODB.Recordset")
' Open a txt file using ODBC
iConnection.Provider = "MSDASQL"
iConnection.Open "Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=" & sInputFileDir & "\;"
iRecordset.Open "SELECT DOH FROM sraDOH.txt Where SSN = "& consumerSSN, _
iConnection, adOpenStatic, adLockOptimistic, adCmdText
dDOH = iRecordset("DOH")
If IsNull(dDOH) Or dDOH = "" Then
stdout.WriteLine "ERROR: Bad date on record for: "& consumerSSN
End If
iRecordset.Close
iConnection.Close
getDOH = dDOH
End Function
这是我的原始函数:
Function setInvoiceLineItemRefListID(dpassedProductID)
Dim iConnection 'Connection objects for input
Dim iRecordset 'RecordSets for input
Dim dProductID 'Foundry Product ID
Dim dListID 'Quickbooks Online Product ID
Dim sInputFileDir ' Directory containing the CSV file to process
Dim sInputCSV ' Name of the CSV file to open (without a pathname)
' Spreadsheet columns
'productID, sproductAbbrev, sechoDataItemNumber, sQBItemCode, slistID
sInputFileDir = "C:\ThinkwellApps\Programs"
sInputCSV = "products.csv" 'Name of the spreadsheet containing the list of ProductIDs
'
'Open spreadsheet
'
Set iConnection = CreateObject("ADODB.Connection")
Set iRecordset = CreateObject("ADODB.Recordset")
' Open a CSV file
iConnection.Provider = "MSDASQL"
iConnection.Open "Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=" & sInputFileDir & "\;"
iRecordset.Open "SELECT ListID FROM products.csv Where ProductID = "& dpassedProductID, _
iConnection, adOpenStatic, adLockOptimistic, adCmdText
dListID = iRecordset("ListID")
If IsNull(dListID) Or dListID = "" Then
stdout.WriteLine "ERROR: Bad listID for: " & dpassedProductID
iRecordset.Close
iConnection.Close
setInvoiceLineItemRefListID = dListID
End Function