here is the entirety of what I've done, which includes the import, the subsequent processing of those imported tables, and then the eventual export of a query and two tables into a spreadsheet.
Problem: Shouldn't the import lines of code (acTransferText) be overwriting the tables it creates each time? It does not appear to be doing so... I can insert that in somewhere else, but it was my impression that this should be overwriting, not appending. What setting should be changed?
Code:
Public Sub ImportProcessExportCSVFiles() On Error GoTo ErrHandler
Const conVEND = "Vendor"
Const conVOCHR = "Voucher"
'*** change the table names to the correct tables
Const contblVEND = "tbl_Temp_Vendor"
Const contblVOCHR = "tbl_Temp_Voucher"
'*** change the ImportSpec names to the correct names
Const conVEND_ImpSpec = "Vendor_Import_Spec"
Const conVOCHR_ImpSpec = "Voucher_Import_Spec"
Dim d As DAO.Database
Dim r As DAO.Recordset
Dim fd As FileDialog
Dim sSQL As String
Dim strFilter As String
Dim RC As Integer
Dim varFile As Variant
Dim strFileName As String
Dim lngFlags As Long
Dim k As Integer 'counter
Dim vPath As String
k = 0
Set d = CurrentDb
' -------------- import the CSV files --------------
'
'open record set (query)
sSQL = "SELECT tblImportFileNames.txtFileName FROM tblImportFileNames ORDER BY txtFileName;"
Set r = d.OpenRecordset(sSQL)
'ensure that there are file names in the record set
If Not r.BOF And Not r.EOF Then
r.MoveLast
r.MoveFirst
'loop through the filenames in the table
Do While Not r.EOF
k = k + 1 'counter
strFileName = r!txtFileName 'get path/filename from record set
Select Case True
Case InStr(strFileName, conVEND) > 0
'****** comment out the MSGBOX lines and uncomment the DoCmd lines *****
'MsgBox r!txtFileName
DoCmd.TransferText acImportDelim, conVEND_ImpSpec, contblVEND, strFileName, True
Case InStr(strFileName, conVOCHR) > 0
'MsgBox r!txtFileName
DoCmd.TransferText acImportDelim, conVOCHR_ImpSpec, contblVOCHR, strFileName, True
Case Else
MsgBox "Invalid import file"
k = k - 1 'counter
End Select
r.MoveNext
Loop
MsgBox "Done" & " - " & k & " files imported"
DoCmd.SetWarnings False
DoCmd.OpenQuery ("qry_del_tbl_vendor_all_rows")
DoCmd.OpenQuery ("qry_del_tbl_voucher_all_rows")
DoCmd.OpenQuery ("qry_append_tbl_vendor")
DoCmd.OpenQuery ("qry_append_tbl_voucher")
DoCmd.SetWarnings True
'MsgBox "queries run successfully"
vPath = Left(strFileName, InStrRev(strFileName, "\"))
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qry_VenderVoucher_MatchCheck", vPath & "TEST_LoadingQC", True, "Vendor-Voucher Match"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tbl_Vendor", vPath & "TEST_LoadingQC", True, "Vendor File"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tbl_Voucher", vPath & "TEST_LoadingQC", True, "Voucher Level1"
MsgBox "Finished exporting SLQC spreadsheet to " & vPath
Else
MsgBox "No files found to import!!"
End If
ExitHere:
'clean up
On Error Resume Next
r.Close
Set r = Nothing
Set d = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description & " " & Err.Number
Resume ExitHere
End Sub