Need help on DoCmd.TransferSpreadsheet

[jhartford]

I'm working on a Sub to automate importing data from Excel to Access (table) and keeping getting an error msg:

Run-time error 3170. Could not find installables ISAM.

MS Access 2007, MS Office 12.0 Object Library is selected.

Any help will be appreciated

'Import files to the new tables
 
Sub ImportFiles()
    
    'MsgBox "please select the US EOM file to import"
    
    Dim fd As Office.FileDialog, FileName As Variant
        
    'Set up the file dialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    With fd
        'disable multiselect for one file selection
        .AllowMultiSelect = False
        .Title = "Please select US EOM report"
        
        If fd.Show = True Then
            FileName = fd.SelectedItems(1)
        Else
            cmdfiledialog = "EXIT"
        End If
        
    End With
    
    'clear file dialog
    Set fd = Nothing
    
    MsgBox FileName
    
    DoCmd.TransferSpreadsheet _
    acImport, _
    acstpradsheettypeexcel12, _
    "EOM License Test", _
    "FileName", _
    True
    
    'MsgBox x = selectFiles()
    
       
End Sub

 

[KenHigg]

My first guess is that you may need to check references -

 

[spikepl]

You should have Option Explicit at the top of the module. You don't, hence your predicament.

 

[namliam]

Assuming FILENAME actually contains the FULL path and Name of the excel file to import...

    DoCmd.TransferSpreadsheet _
    acImport, _
    acstpradsheettypeexcel12, _
    "EOM License Test", _
    "FileName", _
    True

Should read:

    DoCmd.TransferSpreadsheet _
    acImport, _
    acstpradsheettypeexcel12, _
    "EOM License Test", _
    FileName, _
    True

As it is the code tries to import the file called FILENAME which obviously doesnt exist.

FYI dont use PHP tags when posting code, its near unreadble this way...
Instead use CODE tags...

 

[spikepl]

No it shouldn't. Still errors.

 

[jhartford]

You should have Option Explicit at the top of the module. You don't, hence your predicament.

Thanks spikepl. It works now.

 

[jhartford]

Solved: Need help on DoCmd.TransferSpreadsheet

Solved. Thanks