Excel to Access (hidden worksheet | linked cells)

[JK]

I have each salesperson in our salesforce submit an excel form to me each
week. It's an Activity Summary Report. The Workbook contains 10 worksheets.
They are, 1. cover page containing salesperson name/number, 2. SUN, 3. MON,
4. TUE, etc... 9. Summary Page & lastly, there is a hidden worksheet that is
linked to all of the weekday worksheets designed to pull all of the data into
one worksheet for purposes of importing into access.

The hidden summary worksheet is a named range 'WeekSum' & has 7 columns;
Salesperson Number, WeekDate, DayDate, DayTime, Account (or customer name),
Activity & Comment.

There are roughly 15 salespeople; each of them sends me a report every
Monday. I'm trying to figure out the best way to import this data into an
access database every week without having to manually link to each workbook.

If you have any ideas, I'd appreciate it. Maybe you could point me in the
right direction.

Thanks!
Jason K.

 

[Klatuu]

You can use the TransferSpreadsheet's acLink option to cause it to link to
the worksheet. You will need to include the worksheet's name in the Range
argument so it will know which worksheet to use.

If you can have the sales people (I know, this is the hard part, they are
sales people) put their reports in one directory, you can use the Dir
function to loop through the xls files in the directory and link to each in
turn and transfer the data into your database.