Solved

Importing an Excel file into a table via VBA code

Posted on 2014-09-03
5
316 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Bulk load data error 5 34
Resize text 4 16
Multiple queries for a form 14 22
Minus first query 1 0
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

747 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

10 Experts available now in Live!

Get 1:1 Help Now