Regular imports to excel

[gmmillen]

I need some advice on the best way to perform the following:

I need to keep up to date status of assets distributed across multi
sites. At the moment, sheets are emailed to a central location and
data is cut and pasted to (one of two) central data sheets, twice
daily.

Every month has a separate data sheet and the data must be pasted to
match its daily entry record.

Another sheet analyses all the data.

I would really appreciate any ideas on what would be the most
efficient way to get the daily data into the monthly data sheet.

I cannot automate forms like Outlook allows, nor can I use a central
directory on a server, or web access type solution for security
reasons.

The best option seems to be to email the daily sheet, rename it with
the date of the day it represents, then match the data to the correct
day and link it in somehow.

I tried to use the INDIRECT function but it requires the sheet to be
open. I can hard reference the data but it will mess up the automation
of the rest of the workbooks which automatically calculate days, dates
etc..

Any help appreciated.

 

[Don Guillett]

If I understand your problem, I would develop a macro that opens each
workbook in a list and copies the info to the next available row and then
closes the workbook.

 

[Groggy]

Don, thanks for the response. I'd thought of a macro though there are
two main sites feeding data. Each site sends the same sheet of info
with the site only identiable by the sender's name.

I am beginning to think the best I can do is place a macro in the
sender's sheet to autoselect and copy the data, then have the user
navigate to the appropriate day in the data entry sheet and hit paste.

Greg

 

[Don Guillett]

Or have the macro goto the appropriate sheet automatically. If not possible,
an inputbox asking for the sheet name to be entered or selected from a drop
down.