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.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Export from Excel to Access (Office 2002)
- Thread starter pwiren
- Start date
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.
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.
Upvote
0
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,171
- Office Version
- 365
- Platform
- 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.
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.
Upvote
0
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.
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.
Upvote
0
Similar threads
- Question
