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

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

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

see this demo on searching folders for files

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