Solved

Importing an Excel file into a table via VBA code

Posted on 2014-09-03
5
320 Views
Last Modified: 2014-09-05
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?
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 36

Expert Comment

by:PatHartman
ID: 40301043
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
 

Author Comment

by:SteveL13
ID: 40301079
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
 
LVL 1

Accepted Solution

by:
Matthew Ozog earned 500 total points
ID: 40301109
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
 

Author Comment

by:SteveL13
ID: 40305788
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
 

Author Comment

by:SteveL13
ID: 40305876
I got it.  I just had to change the field names in the table to F1 and F2.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question