Import multiple Excel Spreadsheets into one table (1 Viewer)

cdawley4

New member
Local time
Today, 10:33
Joined
Sep 13, 2004
Messages
5
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
 
Last edited:

cdawley4

New member
Local time
Today, 10:33
Joined
Sep 13, 2004
Messages
5
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:

Code:
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:

Code:
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

Registered User.
Local time
Today, 18:33
Joined
Aug 9, 2006
Messages
21
cdawley4 said:
Hope this helps other people wanting to accomplish the same thing.

I'm one of those other people:)

Thanks

Frank
 

kukulaka

New member
Local time
Today, 17:33
Joined
Nov 16, 2006
Messages
4
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

Registered User.
Local time
Today, 13:33
Joined
Aug 22, 2005
Messages
182
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

New member
Local time
Today, 17:33
Joined
Nov 16, 2006
Messages
4
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

Registered User.
Local time
Today, 12:33
Joined
Jan 21, 2000
Messages
61
Importing Multiple spreadsheet WOW

This post was very beneficial.

Thanks a bunch
 

Ccourt4

New member
Local time
Today, 13:33
Joined
Dec 14, 2006
Messages
1
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

Smeghead
Local time
Today, 10:33
Joined
Jan 12, 2001
Messages
32,059
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.
 

Users who are viewing this thread

Top Bottom