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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,062
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
59,062
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
76,303
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.
 

Forum statistics

Threads
1,141,599
Messages
5,707,311
Members
421,502
Latest member
PULBAG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top