import excel data to access using command button

Posted on 2014-07-16
Last Modified: 2016-06-07
I need to import excel data to access using a command button on the forma and append to an existing table.

The file path and file names are not consistent so the user should be able to browse to the file and select it.


Question by:oldrin
    LVL 84

    Accepted Solution

    You can use various methods to allow the user to pick a file. If all your users are running the full version of Access, you can add a Reference to the Microsoft Office library, and use the FileDialog method. Assuming you have a Textbox named txFilePath on your form:

    Private Sub cmdFileDialog_Click()
    ' This requires a reference to the Microsoft Office 11.0 Object Library.
       Dim fDialog As Office.FileDialog
       Dim varFile As Variant
       ' Set up the File dialog box.
       Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
       With fDialog
          ' Allow the user to make multiple selections in the dialog box.
          .AllowMultiSelect = True
          ' Set the title of the dialog box.
          .Title = "Select Your Excel File"
          ' Clear out the current filters, and then add your own.
          .Filters.Add "Excel Files", "*.xls;*.xlsx"
          .Filters.Add "All Files", "*.*"
          ' Show the dialog box. If the .Show method returns True, the
          ' user picked at least one file. If the .Show method returns
          ' False, the user clicked Cancel.
          If .Show = True Then
             ' Loop through each file that is selected and then add it to the list box.
             For Each varFile In .SelectedItems
                Me.txtFilePath = varFile
             MsgBox "You clicked Cancel in the file dialog box."
          End If
       End With
    End Sub

    Open in new window

    Base code hacked from here:

    Once you do have that, you can use the builtin TransferSpreadsheet to move your data in:

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "YourSTAGINGTable", Me.txtFilePath, True

    Note that I've imported to YourSTAGINGTable, which should NOT be your live data table. Instead, you import data to a temporary or "staging" table, then use standard VBA/SQL to move it over after verifying that data. So for example, after import:

    Currentdb.Execute "INSERT INTO MyLiveTable(Col1, Col2, Col3) (SELECT Col1, Col5, Col7 FROM YourSTAGINGTABLE Where Date1>#" & DateAdd("dd", -7, Now) & "#)"

    That would only insert records added in the past week. Of course, you'd have to substitute for all Field, Table and Control names in order to match everything up.
    LVL 31

    Assisted Solution

    There are several ways to import data from an Excel workbook into an Access table (either a new table, or appending to an existing table).  As Scott mentioned, it is best to import into a temp table, since there are often data type errors when importing from Excel into Access.  After browsing for the workbook using the FileDialog code, TransferSpreadsheet works fine if you want to import a whole workbook of data, and you can also use the Range argument to just get data from a certain range:

                DoCmd.TransferSpreadsheet transfertype:=acImport, _
                   spreadsheettype:=acSpreadsheetTypeExcel12, _
                   tablename:=strTable, _
                   FileName:=strXLFile, _
                   hasfieldnames:=True, _

    Open in new window

    If you need to do the same import on a regular basis (say, a workbook that is updated with new data every week), you can use a saved import specification, and call it like this:


    Open in new window

    This only works if everything (workbook name and path) are exactly the same, though, which limits its usefulness.

    Author Comment

    Scott and Helen,

    Thank you both for your expert advise.

    Facing an issue regarding filedialog function.  Getting an error from VB / Tools / References, "error accessing the system registry".  It could be because we users in our company do not have admin rights or something else.  There is also the issue that some of the users of this access database do not have the full version of access. They are using the runtime viewer.

    Is there a workaround the filedialog and where we can still have the option to choose the location and the file to be imported ?

    Thanks again
    LVL 84
    Here's code that implements the Windows File Dialog via the API:

    I've used that code for years and it works fine for me. Just copy it into a new Standard Module, save the module as something like "basFileDialog",. The link above also shows sample usage.
    LVL 26

    Expert Comment

    No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

    I have recommended this question be closed as follows:

    -- Scott McDaniel (Microsoft Access MVP - EE MVE ) (http:#a40198922)
    -- Helen_Feddema (http:#a40204645)

    If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

    Experts-Exchange Cleanup Volunteer

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    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…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    761 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

    13 Experts available now in Live!

    Get 1:1 Help Now