I want to import a large number of Excel spreadsheets that are 'supposed' to have the same number of columns representaing the same type of data into tables in an Access database. The UNC path and filename are stored in an Access table ("chemoabstract"). I run the following function, which searches the table for a matching file ID and hopefully imports the data into a file named whatever MYFILEID is. What I would like to do is flag up instances of import errors and rename the error log files so that I can identify them appropriately, i.e MYFILEID_ImportErrors. All ideas gratefully received.
Function ImportChemoSSheets(MYFILEID As String)
'Import Spreadsheets
'MYFILEID = filename (e.g. 100_1)
'[Filename] = path e.g. C:\MyDocuments\
'[Extension] = file type = ".xls"
Dim MyDB As Database 'Current database
Dim MyTbl As Recordset
Dim strUNC As String
Dim strTableName As String
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyTbl = MyDB.OpenRecordset("Chemoabstract")
If MyTbl.BOF And MyTbl.EOF Then
MyTbl.Close
Set MyTbl = Nothing
Exit Function
End If
MyTbl.MoveFirst
Do While Not MyTbl.EOF
If MYFILEID <> MyTbl![FILEID] Then
MyTbl.MoveNext
Else
strTableName = MyTbl![FILEID]
strUNC = MyTbl![FileName] & MyTbl![FILEID] & MyTbl![FileExtension]
DoCmd.TransferSpreadsheet acImport, 8, strTableName, strUNC, True, ""
MyTbl.MoveNext
End If
Loop
MyTbl.Close
Set MyTbl = Nothing
Set MyDB = Nothing
End Function