Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


import excel data to access using command button

Posted on 2014-07-16
Medium Priority
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 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 800 total points
ID: 40198922
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: http://support.microsoft.com/kb/824272

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

by:Helen Feddema
Helen Feddema earned 800 total points
ID: 40204645
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

ID: 40207174
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 85
ID: 40207208
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 28

Expert Comment

ID: 41640930
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

580 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