Importing an Excel file into a table via VBA code

I have this line of code which imports an Excel file into a table via VBA code.  

DoCmd.RunSavedImportExport "Import-Field Notes"

It works fine as long as the Excel file name is always the same because the saved import is always looking for that file name.  But the Excel file name will always be different even though the structure of the file will always be the same.

The user is selecting the Excel file via:

    Dim fd As FileDialog, SelectFolderOrFile
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .InitialFileName = Environ("userprofile") & "\Documents\"
        If .Show Then
            Me.txtFileName = .SelectedItems(1)
        End If
    End With

Open in new window


My question is this... what can I replace the code...

DoCmd.RunSavedImportExport "Import-Field Notes"

with that will accept the file name, no matter what it is?
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Use the TransferSpreadsheet method.  You can specify a variable file name and you can also specify an Import/Export spec name if you need to.
0
SteveL13Author Commented:
Using the "wizard" (please see screenshot), when I get to [FileName] how do I specify the file name being imported if the file name is different each time (although the structure is the same every time)...

Example
--Steve
0
Matthew OzogCommented:
Use your code from above to get the filename:

 Dim fd As FileDialog, SelectFolderOrFile
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .InitialFileName = Environ("userprofile") & "\Documents\"
        If .Show Then
            Me.txtFileName = .SelectedItems(1)
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tblNotes, .SelectedItems(1)...
        End If
    End With
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
Sorry late getting back to this but the code is not working.  Here is what I have:

       
Dim fd As FileDialog, SelectFolderOrFile
            Set fd = Application.FileDialog(msoFileDialogFilePicker)
        With fd
            .InitialFileName = Environ("userprofile") & "\Documents\"
            If .Show Then
            Me.txtFileName = .SelectedItems(1)
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTempFieldNotesImport", .SelectedItems(1), True, "A1:B63"
            End If
        End With

Open in new window


When I run the code I get an error:

Field 'NotesID' doesn't exist in destination table 'tblTempFieldNotesImport.'

The Excel file has just two columns.  The table tblTempFieldNotesImport has just two fields and they are named Field 1 and Field2.

????
0
SteveL13Author Commented:
I got it.  I just had to change the field names in the table to F1 and F2.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.