DoCmd.TransferSpreadsheet 1

[bigdavidt]

I am working on automating the process of importing data from Excel spreadsheets into temporary Access tables. How can I set up DoCmd.TransferSpreadsheet so I can select an Excel file at runtime?

Thank you in advance.

 

[Remou]

A short search turned up this:

http://www.tek-tips.com/viewthread.cfm?qid=760625

 

[grgimpy]

Here's something I dug up from a project I worked on. You may have to make a couple changes.

Public Function OpenFileDialog(boolMultiselect As Boolean) As Variant
On Error GoTo Err_Handler
    
'The following function utilizes the Open File Dialog
'built-in Microsoft Office feature.  It allows
'the user an interface to select the file they
'would like to import.
'In order for this code to work you must select the
'"Microsoft Office 11.0 Object Library" reference.
    
'Declare Variables
    Dim FileDialog As FileDialog
    Dim vFileSelected As Variant, x As Integer

'Specify that this dialog box will be used for Opening only
'(but really we are only saving the name of the file location)
    Set FileDialog = Application.FileDialog(msoFileDialogOpen)

'Don't allow user to select multiple files
    If boolMultiselect = False Then
        FileDialog.AllowMultiSelect = False
    Else
        FileDialog.AllowMultiSelect = True
    End If

'Only display excel files
    FileDialog.Filters.Clear
    FileDialog.Filters.Add "Excel Files", "*.xls"
    
'Open on specific folder
    FileDialog.InitialFileName = "C:\Program Files\"
    
'Locate file location
        If FileDialog.Show = -1 Then
            For Each vFileSelected In FileDialog.SelectedItems
                OpenFileDialog = OpenFileDialog & ";" & vFileSelected
                x = x + 1
            Next vFileSelected
        End If

'Close FileDialog box
    Set FileDialog = Nothing

'Get rid of ";" in file path name
    OpenFileDialog = Mid(OpenFileDialog, 2)
    
Exit_Handler:
    Exit Function
Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Exit_Handler
End Function

Public Sub ImportFile()

'Quick method for transfering spreadsheet.
'This method does not allow you to append data to an existing
'table.  It just creates a new table full of data each time.
    DoCmd.TransferSpreadsheet acImport, _
        acSpreadsheetTypeExcel8, "ExcelData", _
        OpenFileDialog(False), True

End Sub