Updating Access from Excel data

[WLMPilot]

I am trying to figure out how to update inventory that is retained in Access
(yet to be created) with supply requests that are created in Excel. I have
some macro experience within Excel.

I have developed, in Excel, a supply requisition using item number and qty.
Using the item number, the macro will find the item description (from another
worksheet) and place it on the order worksheet.

The worksheet is emailed, then downloaded onto supply officer's computer.
This is where I need some guidance. I need to update inventory quantities
that are in Access, based on quantity of items ordered with a spreadsheet.
Not sure what the formula would look like and if I need to have a separate
database or use the master inventory database.

Thanks for your input,

Les

 

[Steve Schapel]

Are you sure about that, Bonnie? I was under the impression that the
facility to link to Excel had been removed from Access, due to patent
dispute.

 

[Steve Schapel]

Bonnie,

Well, to be precise, you can link to Excel worksheets, but you can no
longer add, edit, or delete any data in the worksheet, which renders it
a lot less useful than it used to be!

 

[Steve Schapel]

Just looked it up, Bonnie, and this change was introduced with SR2 of
Access 2003.

 

[WLMPilot]

Thanks for your input.

Now let me ask this. This inventory/supply requisition is for an EMS
service. We currently have seven outlying stations. Every Thursday, the
crew will reorder supplies for their station and email it to the supply
officer. I have set up the worksheet for the supply request to save the
worksheet using the date as part of the filename (08-0626 Station 4.xls).
What I was looking for are the easiest ways to have that Access master
inventory database updated.

My thinking was to have a command button that is clicked on. Each time it
is clicked, a macro will look in the directory for the spreadsheets and
update the inventory levels. After a worksheet has been "looked at" to
update inventory, the worksheet will be moved to another directory, out of
the way.

If this is not the easiest approach, having seven worksheets to look at,
then what would be better?

Thanks,
Les

 

[Rafi]

Place your files in a temp directory (Temp1) and then going through the list
of files save file 1 as "linked", run an update query in Access, move file 1
to a second directory (Processed), select the next file and save it as
"Linked" and repeat the process untill you ran through all seven files.

Okay, this is a different question. I didn't know there were 7 spreadsheets.


What I was suggesting was when you recieve a spreadsheet (they would all need
to be the same format), save it as the same file name that has been linked in
your database and run the append query. You do not have to relink each time
if you use the same file name and the spreadsheet format is the same.

Bonnie

http://www.dataplus-svc.com

Thanks for your input.

Now let me ask this. This inventory/supply requisition is for an EMS
service. We currently have seven outlying stations. Every Thursday, the
crew will reorder supplies for their station and email it to the supply
officer. I have set up the worksheet for the supply request to save the
worksheet using the date as part of the filename (08-0626 Station 4.xls).
What I was looking for are the easiest ways to have that Access master
inventory database updated.

My thinking was to have a command button that is clicked on. Each time it
is clicked, a macro will look in the directory for the spreadsheets and
update the inventory levels. After a worksheet has been "looked at" to
update inventory, the worksheet will be moved to another directory, out of
the way.

If this is not the easiest approach, having seven worksheets to look at,
then what would be better?

Thanks,
Les

Thanks Steve,

[quoted text clipped - 14 lines]

longer add, edit, or delete any data in the worksheet, which renders it
a lot less useful than it used to be!