Export from Excel to Access (Office 2002)

pwiren

New Member
Joined
Sep 21, 2006
Messages
13
I am trying to automate a process that starts in Excel, builds a table and then exports it to an existing table in an existing Access file. I am up to the point where I've built the table and opened the Access file, but I don't know where to go from there.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,405
Office Version
  1. 365
Platform
  1. Windows
Is it necessary to import the data into Access? Excel tables can be linked into Access. The nice thing about that, is it is dynamic and does not require any importing.
 

pwiren

New Member
Joined
Sep 21, 2006
Messages
13
Thank you for your reply.

I do not believe that linking will work. The excel tables will be built through monthly data dumps (in excel formats), and will want to add the data to the existing Access table. I need to keep the historical data which I cannot keep in one excel file, because I would soon move beyond the row count limit.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,405
Office Version
  1. 365
Platform
  1. Windows
I have not done too much with writing VBA code in Excel to control Access, or vice versa, but there has been plenty of code like that posted on this forum. You should be able to find by searching this forum.

Here is how I would approach your problem:

1. Write an Excel macro that does what you need it to build and export your Excel table. Place code in the Workbook_Open event so that the macro runs automatically upon opening.

2. Write VBA code in Access that performs the import steps. Place that code in an AutoExec macro so that it runs upon opening Access.

3. Write a simple batch file that opens the Excel file from step 1, and then opens the Access file from step 2. This should accomplish your task.

You can also schedule the batch file to run whenever you want (using something like Windows Scheduler or other scheduling software) if you want the whole process to automatically run at a specified time.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,804
Office Version
  1. 365
Platform
  1. Windows
Why can't you 'build' the table in Access in the first place?
 

pwiren

New Member
Joined
Sep 21, 2006
Messages
13
jm14:
The auto execute is a excellent idea. (I've also been searching the Access and Excel post, and while I've found some great info, the auto execute appears to be cleanest solution.)

Norie,
Also a good idea, however data is forwarded to us in Excel format. I am setting the file up so that others who would like to clean the data (who tend to be a timid when it comes to Access) will not need to leave the comforts of Excel. The end goal is to build an Access table that users can query from Excel.

Thank you both for your input.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,502
Messages
5,523,296
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top