Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-12-2008, 05:25 PM   #1
piedpiper8
Registered User
 
Join Date: Oct 2008
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
piedpiper8 is on a distinguished road
Thumbs up 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
Attached Files
File Type: zip CAA merge data application.zip (38.8 KB, 177 views)


Last edited by piedpiper8; 11-12-2008 at 05:41 PM.
piedpiper8 is offline   Reply With Quote
Old 11-13-2008, 04:35 AM   #2
Guus2005
AWF VIP
 
Guus2005's Avatar
 
Join Date: Jun 2007
Location: The Netherlands
Posts: 2,529
Thanks: 54
Thanked 87 Times in 80 Posts
Guus2005 is a jewel in the rough Guus2005 is a jewel in the rough Guus2005 is a jewel in the rough
Re: Import multiple workbooks into 1 access table

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
Guus2005 is offline   Reply With Quote
Old 11-13-2008, 06:03 AM   #3
piedpipe9
Registered User
 
Join Date: Nov 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
piedpipe9 is on a distinguished road
Re: Import multiple workbooks into 1 access table

Quote:
Originally Posted by Guus2005 View Post
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
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

piedpipe9 is offline   Reply With Quote
Old 11-13-2008, 07:01 AM   #4
DCrake
Remembered
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 325 Times in 208 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
Re: Import multiple workbooks into 1 access table

see this demo on searching folders for files
Attached Files
File Type: zip FindFiles.zip (303.7 KB, 479 views)
__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Old 11-13-2008, 07:48 AM   #5
Guus2005
AWF VIP
 
Guus2005's Avatar
 
Join Date: Jun 2007
Location: The Netherlands
Posts: 2,529
Thanks: 54
Thanked 87 Times in 80 Posts
Guus2005 is a jewel in the rough Guus2005 is a jewel in the rough Guus2005 is a jewel in the rough
Re: Import multiple workbooks into 1 access table

Quote:
Originally Posted by piedpipe9 View Post
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

Guus2005 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatic Import Access records from table to MySQL table Chromium Modules & VBA 0 06-04-2008 01:44 PM
create a progress bar in VB JohnLee Modules & VBA 3 01-08-2008 07:29 AM
[SOLVED] Writing records from an Access table to multiple spreadheets GEawf01 Modules & VBA 1 06-13-2006 02:00 PM
[SOLVED] Import Excel Table to access with validation process ?? ngarasan Modules & VBA 0 05-17-2006 12:14 AM




All times are GMT -8. The time now is 01:42 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World