Moving specific data from excel to a table

[gguy]

I need to pull data from a cell in an excel spreadsheet and put it in a specific field of a record in an access table.
The excel spreadsheet is updated several times daily by an outside source.
My user cuts and pastes the value from the excel to his access table whenever he needs to run the program.
I want to automate this process.
Any ideas?

 

[The_Doc_Man]

Well, there is the possibility of this approach...

Assumptions:

1. The spreadsheet is available over the network as a shared file.
2. The spreadsheet is NEVER deleted and regenerated (except if a disk dies).
3. The row in the spreadsheet is always identifiable by context.
4. You are comfortable with Macros.
5. You are comfortable with Windows Task Scheduler.

Steps:

1. Make the spreadsheet the source of a linked (pseudo-)table.
2. Write an update query that would read the linked Excel "table" and write the field to the Access table.
3. Write a Macro that does an ExecuteSQL of your update query.
4. Build a shortcut icon that opens your database on the machine that hosts this application. Modify the icon's command line to include the /X macro-name option. (In Help Files, look up "Command Line" options)
5. Enter that icon in your Windows Task Scheduler to run at the frequency you desire. NOTE: Too often and you "churn" your database. Not often enough and your updates aren't timely. Once every couple of hours ought to be reasonable. Much more often might be a strain. Much less often increases risk of missing a value.

Alternative: If your user is the only person who needs this and whatever that person runs, it is run from a form, put the ExecuteSQL operation in the form's FormLoad event.


Good luck!

 

[gguy]

The access program was created for a single user, so the alternative version ought to work.
Thanks,

GG