I have a perl script that generates a csv file hourly that keeps track
of conversations used on a router. I created an excel work book with
25 sheets. The first 24 sheets are imported using a macro that grabs
the files from a directory and names the sheets according to their
file name. These are the csv files for the previous 24 hours. The
last sheet is a summary sheet that does some basic calculations and
gives hourly totals of conversations used during the 24 hour period.
What I would like to do is be able to delete the first 24 sheets daily
and rerun the macro to import the next days data. When I do this
though the sheet names change due to the file names being different
and all of the links in the summary page fail with #REF!. What I
would like to do is be able to explicitly define in each formula on
the summary page sheet1-sheet24 based on position instead of the sheet
name hoping that when I reload the next 24 sheets it knows where to
locate the data. When I import the data files, I still want to keep
the names on the tabs that corrolate to the file names for other
functions so I can't just import them in as sheets 1-24. Hope this
makes sense.
Thanks

inclued in your code a search for the old worksheet name
and replace for the new worksheet name before you deleat
the old worksheet.

you will have to make sure that the names of the
worksheets are only in the formulas and not any where
else on the sheet though.


-----Original Message-----
I have a perl script that generates a csv file hourly
that keeps track
of conversations used on a router. I created an excel
work book with
25 sheets. The first 24 sheets are imported using a
macro that grabs
the files from a directory and names the sheets
according to their
file name. These are the csv files for the previous 24
hours. The
last sheet is a summary sheet that does some basic
calculations and
gives hourly totals of conversations used during the 24
hour period.
What I would like to do is be able to delete the first
24 sheets daily
and rerun the macro to import the next days data. When
I do this
though the sheet names change due to the file names
being different
and all of the links in the summary page fail with
#REF!. What I
would like to do is be able to explicitly define in each
formula on
the summary page sheet1-sheet24 based on position
instead of the sheet
name hoping that when I reload the next 24 sheets it
knows where to
locate the data. When I import the data files, I still
want to keep
the names on the tabs that corrolate to the file names
for other
functions so I can't just import them in as sheets 1-
24. Hope this
makes sense.
Thanks
.