I have been working with a lot of excel spreadsheets lately. In most cases, I have found that the most simple approach has been to load spreadsheets into Access tables and slice/dice data with SQL queries.
I have one spreadsheet in particular that is standardized and updated daily. By quickly loading and running saved queries, I save a ton of time, my results are accurate, and I no longer have to reinvent the wheel.
To start this process, I set up a form in Access that allows me to select the spreadsheet with a File Dialog. Here are some recommended steps for importing excel files with the File Dialog control.
UPDATES 11/20/2015:
- One of our visitors noticed a syntax error under step 5, a misplaced ‘end if’ which has been fixed.
- I also leaned up the selectFile function. It is more efficient with less code.
Step 1: Prepare your table
The most important part of this step is attention to detail with field data types. If your field data types are set up improperly in Access, you are sure to run into import errors.
The best way that I have found is to first import the spreadsheet using built in “Import from Excel” wizard. Be sure you remove spaces from your column names. Lowercase column names are great for hand coding SQL queries as they are easy to rememeber. Capitalized column names work if you stay consisitent(I.E. FirstName, LastName, PhoneNumber). Using the wizard click on each column and ensure that you have selected the correct data type.
As you finish the wizard, your data should be imported. If you run into errors, you will see an extra table that lists out your import errors. If you have import errors, start over. For this example, I am labeling the newly created table as “names”.
Step 2: Run a DELETE Query on the data in your table
You DO NOT want to delete your table, just the data inside. Open a query, select SQL view and run:
DELETE * FROM names
--Where names is the name of
--the table for this example
--use the name of your table
Step 3: Add a button to your form
Assuming you have a form that you are going to use for your application. If not, create a blank one and add a button. For this demo, I am setting the caption on the button to “Load Excel” and naming it cmdLoad.
Step 4: Add the file dialog function to your application
Add the following file dialog function to a module. Don’t forget to add the Microsoft Office Object Library reference as noted in the code below.
'--------------------------------------------------
' File Browse Code
'--------------------------------------------------
'NOTE: To use this code, you must reference
'The Microsoft Office 14.0 (or current version)
'Object Library by clicking menu Tools>References
'Check the box for:
'Microsoft Office 14.0 Object Library in Access 2010
'Microsoft Office 15.0 Object Library in Access 2013
'Click OK
'--------------------------------------------------
Function selectFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
If .Show Then
selectFile = .SelectedItems(1)
Else
'stop execution if nothing selected
End
End If
End With
Set fd = Nothing
End Function
Step 5: Connect the File Dialog box to your button
Connect the button to the file dialog, clear your table (optional), and import your spreadsheet.
Private Sub cmdLoad_Click()
On Error GoTo ErrorHandler
'disable ms access warnings
DoCmd.SetWarnings False
'Delete existing data in table
'This is optional. If you are combining spreadsheets
'in your table, don't run this query
DoCmd.RunSQL "DELETE * FROM names"
'load spreadsheet
DoCmd.TransferSpreadsheet acImport, 8, "names", selectFile(), True
're-enable ms access warnings
DoCmd.SetWarnings True
Exit Sub
ErrorHandler:
MsgBox "There was an Error: " & Err & ": " & Error(Err)
End Sub
UPDATE: Here is an example Access db with a sample excel spreadsheet to import:
data-import-example.zip
If you were able to put everything together correctly, you should now be able to import an excel spreadsheet into an access table. If you are having troubles, please post a comment. As always if you have improvements or suggestions, I would love to hear from you as well!
Thanks so much for this. I was able to import 1 file at a time using your code. Can you show me how to import multiple files at a time?
I just posted a comment on another one of your strings. This code worked out perfectly for me. I still need to figure out how to select one tab name from the excel document. Do you have any ideas?
I have not tested this, but it looks like you can specify the specific worksheet in the range of “transferspreadsheet”
I’ve tried using a variable that includes the sheet name and the range, “Zip Detail- All!a11:ac5000” in the transferspreadsheet function, but i’m getting an error that ‘record 22460’ does not exist in my target table. I’m not sure what could be causing this as the excel data set does not extend that far. Any thoughts?
It sounds like you might be running into some kind of data type error. Perhaps an autonumber field in access that has the same field name as your spreadsheet in excel. Two suggestions that might work – 1. re-create your table in access by importing the spreadsheet using the wizard, run a delete query to clear everything out (you are just keeping the structure), and then attempt loading it using VBA. Option #2. Your spreadsheet might have blank space data all the way down to the 22460th cell, left-click on the row header just under your data next to column A (it selects the whole row) and scroll all the way down past the 22460th point by dragging the slider on the right (if you can slide down that far, this is the problem) Hold shift and click on the row header at the 22461th point, right click the selected row header group and select delete (move cells left when prompted. You will delete all blank space with this method. Be careful to not delete any data.
Thanx a million for your great website.
Your codes are very efficient and responsive
Thanx again
Ryan, The code works perfect! One question, asked earlier, but not yet answered. What can I add to this code so it will allow the import of multiple files, 1 appending after the other?
I just posted this: http://www.minnesotaithub.com/2015/04/import-multiple-spreadsheets-into-access-file-dialog-vba/ Included is a link at the bottom with some sample files.
Woo hoo! i’m upgraded to 2013 and i’m just lost trying to add these two things. i’m sorry, just not skilled in VBA, but i’m obviously at a point where I need to figure it out for this great feature! I hit Control G and brought up the VBA window. i’m just guessing I “add a module”. not sure. so I have a Module 1 with FileDialog code in it minus the delete table things for I don’t need that at the moment. do I rename the Module 1 to FileDialog? doesn’t seem to let me. anyway, I then added the second part to my Button named Load Excel, but with a command button name of cmdLoad and under Build I added the second part of the above code… I got errors Compile Error, user-defined type not defined and in a VBA window it highlighted the FileDialog in the code. made me think I needed the module named FileDialog or something.. i’m just making a mess, but i’m sure i’m close! I do have all the Object Library 15 checked.. lol.. I just have other misunderstood problems.
I have the same issue on MS Access 2010
I am getting a “Compile error: User-defined type not defined” any suggestions?
It sounds like you have a couple of things going on. check the spelling of your data type declarations (example: fileName as string) string is the data type piece. If you are still running into issues, try either removing the “as string” or change to “as variant”. Also, it sounds like this error is popping up at compile time as a result of “Option Explicit” at the top. If this is included, you can try commenting out with a single quote in front of it to test.
Hi,
Just tried your code and when clicking on the button i’ve this error after choosing my file : “There was an Error: 3274: External table is not in the expected format”
Hello Quentin, this issue sounds like the column headers on your spreadsheet are not matching the format you set up in your access db. I recommend importing a sample with the built in tools to create the table that you will be importing to. Set your data types, etc.. and then nuke with a ‘DELETE * FROM [table]’ query before trying your automated approach. Did you ever get this sorted out?
Name AutoCorrect Save Failures
After doing a bit of research I think I may have discovered the reason for my problem when I kept getting a new table created with the title of Name AutoCorrect Save Failures. According to Allen Browne it is a bug in Access:
http://allenbrowne.com/bug-03.html
When I followed his directions the new table was no longer created.
Sorry, I think these are posted in reverse order. Here was my original post:
—–
First of all thanks!!! This is very useful!
Two Questions:
1. I’m not sure how the two bits of code talk to each other. In your sample file when I click on one of the buttons to import the data everything works. My question is what is happening in the click event that triggers the code in “functions” module to give me the dialog browser? The only thing they have in common is selectFile in the import code that is the same as the function name. Is this what connects the two?
2. Using your exact code in a file I created that is supposed to replicate your sample file I’m getting an “error” that I’ve never seen. I recreated everything you have except the table name. My table name is MainTable and that is the only thing that is different. When I click on the button I created the data is imported, but…I get a new table called Name AutoCorrect Save Failures. The table has four fields (Object Name, Object Type, Failure Reason, Time). The Object name is MainTable, the object type is Table failure reason is Could not save the object, the time is the time when I ran it. Basically, do you know what “Could not save the object” is referring to?
Appreciate any help you can offer, and again, thanks for a very useful example of how to do something lots of people likely need to do.
Hello- I am happy that this works for you! I noticed your other comment regarding #2, it sounds like everything has been taken care of, is this still the case?
For question number 1 – I am going to really abstract the meaning of a function here, so people please don’t beat me up! Think of a function as a variable, variable (whew). A variable stores a piece of data, but a function is ‘like’ a variable that you can pass data in to, have it do something and then return a value. In this case, you arent actually passing something in, but the code accesses the windows native file picker (msoFileDialogFilePicker) – this is the thing that pops up that allows you to look through your files, directories, etc… and then after you select the file, it returns the string of the path of the file that you selected to the function which then returns the value to the
line. The function calls the file select is the selectFile() line.
You may have a better understanding of functions than my abstraction above, but I hope this helps. Please post if you run into more questions.
Everything’s working now in my practice database. This is such a useful bit of code!
During my playing with it I initially forgot to reference the Microsoft Office Object Library you listed in the comments section of the function. Once I added in the correct reference for Office 2013 the last error disappeared.
That leads me to the following questions:
1. As the “developer” am I the only one who needs to add in the Microsoft Office Object Library reference or will the actual users need to do this also?
2. If the user is on Office 2010, but I referenced the object library for Office 2013 will that cause a problem?
Hello Petr-
To answer both of your questions, I am going to start with question number 2. I am working on getting a test environment set up for access 2010 to open a database built in 2013 to see if the references either convert or somehow stay. In answering question 1 – as long as your users are using the same version of Access as you, they will not have to add references. Your users should be able to open your Access database and it will just work.
JUST IN CASE: I recently discovered the late binding version of the file dialog code (No need for reference). This version will work the same way with out a reference. If you want to give it a try: http://www.minnesotaithub.com/2015/11/solved-late-binding-file-dialog-vba-example/
I will post my test results as soon as I am able to run some test cases. Thank you for bringing up the question of backward version, conversion of references.
Hello,
I wonder what would be the simplest solution to import files every day to the same table?
When I run your code it imports fine on the first go to an empty table, but does nothing if I want to do an additional import. In other words it does not continue after the last import, I guess it expects an empty table.
If you publish a bitcoin address I would tip you for your help.
Many thanks Ryan fo the code!
(does the statement closed part “End If” on the line 15 (Step 5) make any sense?)
Oh my gosh, ‘end if’ has no business in there – thank you for catching that! I updated the code.
Thank you so much!
Hi Ryan,
What would the code look like if I already have the file path and it is listed in the code like this:
Private Sub Command5_Click()
‘Unset warnings
DoCmd.SetWarnings False
‘SQL delete statement
DoCmd.RunSQL “DELETE * FROM CosasOneTable”
filepath = “C:Userspricem4DesktopCosasOne.xlsx”
DoCmd.TransferSpreadsheet acImport, , “CosasOneTable”, filepath, True
DoCmd.SetWarnings True
End Sub
Thanks in advance
Hello Michelle,
You can probably accomplish this task with:
Private Sub Command5_Click() Dim filepath filepath = "C:Userspricem4DesktopCosasOne.xlsx" ' Unset warnings DoCmd.SetWarnings False ' SQL delete statement DoCmd.RunSQL "DELETE * FROM CosasOneTable" ' Transfer Spreadsheet DoCmd.TransferSpreadsheet acImport, , "CosasOneTable", filepath, True ' Reset warnings DoCmd.SetWarnings True End SubHi Ryan, thank you so much! Yes that did work, totally awesome!
This is the best explanation I have come across. Great work!
Hi Ryan,
First of all thank you, the code helps. But I am getting error after
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, “Ratings”, fileName, True
The above code works for other 2 excel file imports but not for Ratings table. Can you please help me.
Thanks in advance.
This works perfectly. THANK YOU!!!
Very helpful many thanks
Hi Ryan,
Thank you so much for your above tutorial. Could you please help me how I can do the same with TXT file? As I want to import .txt file and save into table.
Thanks again.
Thanks so muuuuuuuuuuuuuuuuuuuuuch.