import excel data to access using command button

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.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Helen FeddemaCommented:
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.
oldrinAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.