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.
Your help with this is greatly appreciated.
Thanks so much – piedpiper
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
3) Then I need to run a simple query and output the results into an Excel file.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)
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.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
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”
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.
(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.
Your help with this is greatly appreciated.
Thanks so much – piedpiper
Attachments
Last edited: