Import multiple Excel Spreadsheets into one table

[cdawley4]

Hi,

I would like to import multiple spreadsheets into a table. I tried to use the TransferSpreadsheets macro, but it will only allow me to import one spreadsheet at a time. It won't let me use wildcards to import multiple spreadsheets. Is there any type of vba code that I can use to do the import for me and be able to specify a range such as from column A to H?

Thanks,

Chris

 

[cdawley4]

Hi,

I found some sample code that I used by googling for "access vba importing multiple spreadsheets" and found a link to Tek-tips.com. The code looks like so:

Dim ImportDir As String, ImportFile As String
ImportDir = "C:\Program Files\Alcatel\A4400 Call Center Supervisor\Excel\DAILY\Agents\18_09_06\"
ImportFile = Dir(Importdir & "*")
Do While ImportFile <> ""
  DoCmd.TransferSpreadsheet acImport, 8, "tblInitialAgentCcsData", Importdir & ImportFile, False
  ImportFile = Dir
Loop

I also added field names and a range. My code is shown below:

Function Import()
Dim ImportDir As String, ImportFile As String
ImportDir = "e:\test\"
ImportFile = Dir(ImportDir & "*")
Do While ImportFile <> ""
  DoCmd.TransferSpreadsheet acImport, 8, "Tally Data", ImportDir & ImportFile, True, "A:H"
  ImportFile = Dir
  Loop
End Function

Hope this helps other people wanting to accomplish the same thing.

Chris

 

[FLM]

Hope this helps other people wanting to accomplish the same thing.

I'm one of those other people

Thanks

Frank

 

[kukulaka]

Hi there..

will this code work if the excel files are held within multiple different folders with a location?

eg...

\\lancer\east\easycar\(folders that contain excel files that are held in a folder structure \year\month\day\ )

I dont want to have to list every folder path as there are lots of them...

if not is there any code which can list all the file locations into a table and then i can write import code from that table?

 

[SamDeMan]

cdawley4 -> Thanx for the code

when i saw your question i was thinking more along the lines of linking the excel spreasheet. i am not sure what your project looks like, but you may want to consider it. once you like your excel spreasheet, it will behave like an access table.

sam

 

[kukulaka]

there are multiple excel files (100's of the things ) and a new ones are added on a daily basis in a new folder with the month and day date as the folder name. (and these folders are held in a folder for each month) ideally i want to import the data into one table. Either linking each spreadsheet or importing each sheet manually (or using a macro) would take too much time and I'd have to link each new file every day and specify the path of each spreadsheet. (especially when a new month folder is made)

Im not very good at explaining things but the folder structure is
\consolidated\year\month(new month folder generated every month)\day(new folder made every day)

I want a way of being able to list the paths of each spreadsheet (as they are all held in a folder called consolidated but there are many, many sub folders within this)

any help anyone can give on this would be very much apprciated. Thanks!

 

[Blkblts]

Importing Multiple spreadsheet WOW

This post was very beneficial.

Thanks a bunch

 

[Ccourt4]

cdawley4, I think I'm in the same boat you were. I'm trying to import excel files with multiple worksheets into one access table. Is this what you did? If so, could you explain how you called each worksheet individually?

Thanks!

 

[boblarson]

Although, you could set it up to do a loop and set the name of the spreadsheet and range as variables and just loop through a set of them.