Import multiple workbooks into 1 access table (1 Viewer)

piedpiper8

Registered User.
Local time
Today, 10:25
Joined
Oct 11, 2008
Messages
21
Import multiple Excel workbooks into 1 Access table

Hi everyone,

Just want to say thanks to everyone who helped me on my last project. It is now in “production” and is running great.
Now I have a new project and I would love your help.

1) I would like to use VB to import 3 different Excel workbooks into Access. Here are some caveats

a) I would like Access to search the folders for the Excel files in the same FOLDER/DIR. that the Access file resides in. - For example: if I put the Access file and the 3 Excel files in a folder called “c:\windows\test\myname” or any folder I wish – Then Access would start its search for the Excel files at that location or any folder I put the Access file in.


b) I need the Excel files to import in Access creating a new table with all the columns populated in the Excel files into a newly created table all aggregated together


c) Here is the code I have (which is not working properly)
Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acImport, 8, "CAA Master Table", "C:\Users\Keith\Documents\Test\srid.xlsx", True, ""
DoCmd.TransferSpreadsheet acImport, 8, "CAA Master Table", "C:\Users\Keith\Documents\Test\data.xlsx", True, ""
DoCmd.TransferSpreadsheet acImport, 8, "CAA Master Table", "C:\Users\Keith\Documents\Test\caa.xlsx", True, ""
End Sub
2) I don’t even know if this is possible but... is there a way to build relationships (linking tables together) using VB? – I need this for my query to run/execute.
a) First Excel file is called “data.xlsx” it has these column headers a) “casenum” b) “srid”


b) Second Excel file is called “srid.xlsx” it has these column headers c) “login” d) “password” e) “srid”


c) Third Excel file is called “caa.xlsx” it has these column headers f) “name” g) “login” h) “password”
i) Here’s how I need to link them together automatically by relationship in VB.
(1) I need “data.xlsx” column b) “srid” linked to “srid.xlsx” column e) “srid”


(2) In the same swoop – then I need “srid.xlsx” column c) “login” and column d) “password” linked to “caa.xlsx” column g) “login” and h) “password”


(3) Please view the attachment for clarification.

3) Then I need to run a simple query and output the results into an Excel file.

Your help with this is greatly appreciated.
Thanks so much – piedpiper :)
 

Attachments

  • CAA merge data application.zip
    38.8 KB · Views: 233
Last edited:

Guus2005

AWF VIP
Local time
Today, 15:25
Joined
Jun 26, 2007
Messages
2,645
Very irritating large font.

Import each spreadsheet in its own temporary table. Use an insert query to move records from the temporary table to the final table. Delete the temporary table.

Output what you need.

HTH:D
 

piedpipe9

New member
Local time
Today, 07:25
Joined
Nov 13, 2008
Messages
1
Very irritating large font.

Import each spreadsheet in its own temporary table. Use an insert query to move records from the temporary table to the final table. Delete the temporary table.

Output what you need.

HTH:D

Sorry about the font?

I was looking for help with the code... I want this automated. non computer savy people will be using the program.
So how do I write code for this:

a) I would like Access to search the folders for the Excel files in the same FOLDER/DIR. that the Access file resides in. - For example: if I put the Access file and the 3 Excel files in a folder called “c:\windows\test\myname” or any folder I wish – Then Access would start its search for the Excel files at that location or any folder I put the Access file in.

How do I automate this:

2) I don’t even know if this is possible but... is there a way to build relationships (linking tables together) using VB? – I need this for my query to run/execute.

Thanks for your help!

-piedpiper
 

DCrake

Remembered
Local time
Today, 14:25
Joined
Jun 8, 2005
Messages
8,632
see this demo on searching folders for files
 

Attachments

  • FindFiles.zip
    303.7 KB · Views: 533

Guus2005

AWF VIP
Local time
Today, 15:25
Joined
Jun 26, 2007
Messages
2,645
How do I automate this:

2) I don’t even know if this is possible but... is there a way to build relationships (linking tables together) using VB? – I need this for my query to run/execute.
You don't need to create relationships for queries to work.
You can use relationships to activate cascading updates/deletes. Personally i don't use them, i like to be in charge. which means that i have to delete all relations to other tables myself.

HTH:D
 

Users who are viewing this thread

Top Bottom