By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,238 Members | 1,320 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,238 IT Pros & Developers. It's quick & easy.

Import mulitiple excel files to access table

P: 6
Hello,

I am struggling to import multiple spreadsheets into an Access table. I have "borrowed" the code from the 'net and tailored it slightly. I am using Access 2013. Code is as follows:

Expand|Select|Wrap|Line Numbers
  1. Function Impo_allExcel()
  2. Dim ImportDir As String, ImportFile As String
  3. ImportDir = "\\-server\WarehouseKPIs\DB_Linked_Forms_Folder\"
  4. ImportFile = Dir(ImportDir & "*")
  5. Do While ImportFile <> ""
  6. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel18, Tbl1_Warehouse_Form_Archive, ImportDir & ImportFile, , Form
  7. ImportFile = Dir
  8. Loop
  9. End Function
  10.  
Any help would be much appreciated.

Thanks.
Apr 22 '14 #1
Share this Question
Share on Google+
7 Replies


zmbd
Expert Mod 5K+
P: 5,397
1) You're making us guess here:
1a) Do you have multiple excel workbooks where-in there is at least one worksheet that you would like to import the data
1b) You have a single excel workbook where-in there are multiple worksheets
1c) a combination of 1a and 1b

2) Not Sure what you are doing in line 6
2a) what is that "form" doing at the end of the statement
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet _
  2.     TransferType:=acImport, _
  3.     spreadsheettype:=acSpreadsheetTypeExcel8, _
  4.     tablename:=Tbl1_Warehouse_Form_Archive, _
  5.     fileName:=ImportDir & ImportFile
2b) are you sure that you are using such an old version of the worksheets? acSpreadsheetTypeExcel8= excel97

3) By, simply stating that your code "doesn't work," posting code that appears to have had very little if any troubleshooting performed, and expecting someone to help doesn't usually result in much of an answer and may result in your thread being deleted. Instead, please tell us what you were expecting to happen and what actually happened.

For each error, if any: the EXACT title, error number, and descriptions that occurred and at what line in your posted code the error occurred.

These are the minimum requirements for posting a question of this nature.
Apr 22 '14 #2

P: 6
Hi thanks for your response.

To clarify:
1) I have multiple (50+) excel files in a folder (containing subfolders), that I would like to import in one hit to Access. The excel files all have the file extension .xls. I would like to import the "Form" range from these spreadsheets into the Tbl1_Warehouse_Form_Archive table.
2. Form refers to the Excel range, I have corrected the Excel file type to 18.
3. Issues - the code is falling down at the following points:
Expand|Select|Wrap|Line Numbers
  1. [ImportDir = "\\server\insert_full_path_here\"]
  2.  
  3. [ImportFile = Dir(ImportDir & "*")]
  4.  
  5. [Do While ImportFile <> ""]
I can see that the ImportFile part may not work due to referring to the ImportDir, which isn't working. The same is true for the "Do While ImportFile" line, which is dependent on the ImportFile segment of the code.

I am relatively new to VBA, and have had success with several bits of code. I have "stepped-into" this code, and couldn't see what was wrong, hence my post here. I will describe my issue in more depth should I post here again.

Thanks again.
Apr 23 '14 #3

Expert 100+
P: 1,240
I can't say anything about version 2013, but in 2007 and earlier I don't believe you can add that "*" do the Dir() folder. Just
Expand|Select|Wrap|Line Numbers
  1. ImportFile=Dir(path)
  2. do while ImportFile <> ""
  3.    (do stuff - including test the file type to be sure it's what you want )
  4.     ImportFile=Dir()  ' to get the next file in the list
  5. Loop
  6.  
Jim
Apr 23 '14 #4

zmbd
Expert Mod 5K+
P: 5,397
Hi thanks for your response.
1) I have multiple (50+) excel files in a folder (containing subfolders), that I would like to import in one hit to Access.
That's a very tall order.
You can use the DIR() method to handle the main directory path fairly easily; however, the subfolders will be much more complicated and your code, as is, will not handle that scenario; however, let's just try to handle the main directory files first.

I would like to import the "Form" range from these spreadsheets into the Tbl1_Warehouse_Form_Archive table.
2. Form refers to the Excel range,
Form is a reserved token/name
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

Second the range name needs to be in quotes i.e:"NameOfImportRange"

Third I advise that very new programmers use the named parameters when first creating their code as I've shown in my last post in section (Post#2 - Sec2A). This way you do not need to worry about the comma placement.

I have corrected the Excel file type to 18. (The excel files all have the file extension .xls.)
Type 18?
If you mean "acSpreadsheetTypeExcel18" that is, TBK, an invalid type.
We need to know what version of Excel was used to create the file. With "xls" either acSpreadsheetTypeExcel8 or acSpreadsheetTypeExcel9 are the most likely types to use.

3. Issues - the code is falling down at the following points:
Yea 20Q... For each error, if any: the EXACT title, error number, and descriptions that occurred and at what line in your posted code the error occurred.

Please follow the advice and basic troubleshooting here:[*]> Before Posting (VBA or SQL) Code you will need to repeat, fixing errors as found, the compile step until no errors occur.
Apr 23 '14 #5

P: 6
Hi, I have solved this using alternative code, I tailored it to search all of the key sub-folders. The reference to the named range "Form" worked fine, but will avoid using reserved words in future.

Thanks.
Apr 25 '14 #6

zmbd
Expert Mod 5K+
P: 5,397
most welcome.
Would you mind sharing your solution?
It can be quite helpfull to others with similuar issue, and sometimes we can help streamline the code or help to avoid a potential bottle-neck.
Apr 25 '14 #7

P: 6
Expand|Select|Wrap|Line Numbers
  1. Public Function Impo_allExcel()
  2. Dim myfile
  3. Dim mypath
  4.  
  5. mypath = "\\server\insert_the_full_path_here\subfolder_1"
  6. ChDir (mypath)
  7. myfile = Dir(mypath)
  8. Do While myfile <> ""
  9.   If myfile Like "*.xls" Then
  10.     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Demo_Table", mypath & myfile, , "Form"
  11.   End If
  12.   myfile = Dir()
  13. Loop
  14.  
  15. mypath = "\\server\insert_full_path_here\Weekend Shift\"
  16. ChDir (mypath)
  17. myfile = Dir(mypath)
  18. Do While myfile <> ""
  19.   If myfile Like "*.xls" Then
  20.     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Demo_Table", mypath & myfile, , "Form"
  21.   End If
  22.   myfile = Dir()
  23. Loop
  24.  
  25. End Function

So I have trimmed this code down to access two folders, I have twelve folders total. It searches/imports the contents of one folder, loops until completed and moves onto the second folder. The code then ends. These folders contain submitted Excel forms for the current month - when the month is complete, I then archive these into a dedicated Month subfolder. Then the folder referred to in the code is repopulated automatically with the newly submitted sheets for the new month. The "Form" part of the DoCmd.TransferSpreadsheet acImport statement is the named range in my excel sheets, if anyone else comes to use this code.

Thanks.
Apr 25 '14 #8

Post your reply

Sign in to post your reply or Sign up for a free account.