Automate Import Process

[jguscs]

I found some partial solutions to automate the import process of an XLS spreadsheet to the database as a table.

They involve the retrieval of the path to the XLS file via "file control" and using the retrieved path of the file using some code:
DoCmd.TransferSpreadsheet acImport, , tblName,

What I'm wondering is if there is if anyone has a more concise way to import an XLS file by allowing the user to browse for the file.

This is the file i found somewhere (I forgot where; somewhere from this forum) that demonstrates the browsing part of the import process.

 

[Pat Hartman]

Just use the value obtained from the browse as the file name in the transferSpreadsheet Method.

 

[jguscs]

I've been looking into this and was wondering if anyone could confirm the following method of importing an XLS table.
Actually, the Sub doesn't work as-is. There's an error:
"User-defined type not defined" and it points to: Dim CD As CommonDialog
Also, I don't know if I'm using the TransferSpreadsheet properly:
DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel9, "New Table", file, True, ,)

Private Sub BtnImport_Click()
Dim Filename As String
Dim CD As CommonDialog
Dim file As String
Dim filenum As Integer
On Error GoTo ExitImport
CD.Filter = "All Files (*.*)|*.*|Excel files|*.xls"
CD.FilterIndex = 2
CD.DefaultExt = "xls"
CD.Flags = cdlOFNHideReadOnly Or cdlOFNFileMustExist Or cdlOFNNoReadOnlyReturn
CD.DialogTitle = "Select the source file "
CD.Filename = Filename
CD.CancelError = True
CD.ShowOpen
Filename = CD.Filename
filenum = FreeFile()
Open Filename For Input As #filenum
file = Input$(LOF(filenum), filenum)
Close #filenum
LoadTextControl = True
DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel9, "New Table", file, True, ,)
Me!LstTables.Requery
ExitImport:
End Sub