Solved

Importing an Excel file into a table via VBA code

Posted on 2014-09-03
5
318 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
  • 3
5 Comments
 
LVL 34

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

929 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now