Excel: Importing from an Access database to an excel spreadsheet

excelforum.com

 

Sounds like you want to use a concept called a timer:

http://www.ehow.com/how_5052033_insert-timer-ms-excel.html

You would need to combine the timer and a loop.

Not sure on the 2nd question.

 

arkoe is right. I would use a timer and just have it update data every X number of seconds.

You can play between Excel and Outlook (that's how the Melissa virus worked). I do this to send out custom emails with attachments as well as parse text out of emails to update a database.

Opening attachments, reading data, and updating another file would be a lot trickier. You'd probably have to have the attachments in a pretty standard format. You'd also want the emails to automatically go into a specific folder (you can do this via Outlook rules pretty easily, but you'd probably need some kind of unique subject line or body text so the rule knows which emails to filter). Then it would be a matter of parsing the email, saving the file with a specific name, firing a macro on the original file to open and parse that excel file, and then update your database. Keep in mind that after Office 2000, Microsoft made it a much bigger pain in the ass to fire Outlook marcos due to the Melissa virus and other hacks. Usually you have to manually OK every time an external source tries to access Outlook, but you can get around that with digitial certificates and such.

 

Loading data in 5-minute, timed intervals is dangerous, and often unneeded.

From a user's perspective, refresh data on File Open achieves the same thing. That is, execute your import Macro on File Open. You could also create a Button in Excel that executes your current import macro. Call it something like "Refresh Data."

I find few examples where data integration from primary source needs to be full synchronized in an automatic fashion. An example is where other application functions are doing ongoing, automated operations on the destination database (your Excel file), rather than the primary (your Access file).

Explain why 5-minute auto-refreshes are needed? I suggest putting Refresh commands in the control of the user. And have error checking, so that if an error occurs, the User knows it. That's one of the downfalls of timed intervals. You would write errors to a log and maybe fire off email alerts, and avoid cascading failures. And how does the user actually know the data is valid, in a scenario where ETL errors are not reported to them in real time.

 

Good call. You can easily set up a Refresh button, and you can also set it up to Refresh automatically every time the file is opened. That's what I do for all of our reports at work. They're automatically updated when they open, and the users can filter and update as often they want with a button click.