Changing embedded file name
 

I've been left a workbook containing many sheets, most of which contain
several reference to particular cells in a previous month's copy of the
workbook.

So for instance if the current workbook is called say "Cash September
2005.xls", after this is 'rolled over' to the next month via a macro
which clears out several ranges, it saves as a new name "Cash October
2005.xls"

There are a hundreds of cells which are opening cash balances and which
are dependent on the previous month's workbook closing balance. So for
instance B10 on Sheet1 of the September Workbook contains a formula

='G:\Cash\[Cash August 2005.xls]Sheet1'!$B$11

When the September workbook is rolled over and becomes the October
Workbook, the formula in B10 still refers to August. I need to add some
code to the rollover macro so that the formula changes to:

='G:\Cash\[Cash September 2005.xls]Sheet1'!$B$11

i.e. I just need to change the reference to the month in the formula
since the layout of the workbook doesn't change. I've created variable
names which contain the text "Cash August 2005.xls" (lastfname), and
its equivalent "Cash September 2005.xls" (obalfname), and have tried to
use these in some Find and Replace code. However when the macro runs:
i.e.

Cells.Replace what:=Range("lastfname"), replacement:=Range("obalfname")

I get an Update pop up Window which is requiring me to select the
filename for each replace it finds. There are a hundred or so of these
balances to replace and I need the code to run untouched by human hands
as it were. (Incidentally does anyone know how to close these update
windows without having to select a file name? It was driving me mad
just now since the Cancel or X close didn't seem to have any effect,
and I had to step through all the Replaces on a sheet before the macro
would finish).

a) is this the best approach to adopt or can someone suggest a better
way? I'm thinking for instance that it might be better to move all the
closing balances into the opening balance cells as values, immediately
prior to the rollover so that I avoid links to other workbooks. In
which case I'd need to be able to identify all the opening and closing
balance cells, which might be more complicated than finding specific
text since it seems inelegant to have to create hundreds of individual
range names

b) if the Find/Replace is the best way to proceed, what code should I
use in order to replace these strings across all the relevant sheets,
so that the user doesn't have to continually click on file names. If it
makes any difference, not all the sheets have balances on them, but if
it's just as easy to loop through all the sheets I could live with
that.

If responding to the ng, please email to me as well.

Many thanks in advance.

Richard Buttrey

Changing embedded file name
 

Edit=Links select the link and do Change source.

--
Regards,
Tom Ogilvy


"Richard" wrote in message
oups.com...
I've been left a workbook containing many sheets, most of which contain
several reference to particular cells in a previous month's copy of the
workbook.

So for instance if the current workbook is called say "Cash September
2005.xls", after this is 'rolled over' to the next month via a macro
which clears out several ranges, it saves as a new name "Cash October
2005.xls"

There are a hundreds of cells which are opening cash balances and which
are dependent on the previous month's workbook closing balance. So for
instance B10 on Sheet1 of the September Workbook contains a formula

='G:\Cash\[Cash August 2005.xls]Sheet1'!$B$11

When the September workbook is rolled over and becomes the October
Workbook, the formula in B10 still refers to August. I need to add some
code to the rollover macro so that the formula changes to:

='G:\Cash\[Cash September 2005.xls]Sheet1'!$B$11

i.e. I just need to change the reference to the month in the formula
since the layout of the workbook doesn't change. I've created variable
names which contain the text "Cash August 2005.xls" (lastfname), and
its equivalent "Cash September 2005.xls" (obalfname), and have tried to
use these in some Find and Replace code. However when the macro runs:
i.e.

Cells.Replace what:=Range("lastfname"), replacement:=Range("obalfname")

I get an Update pop up Window which is requiring me to select the
filename for each replace it finds. There are a hundred or so of these
balances to replace and I need the code to run untouched by human hands
as it were. (Incidentally does anyone know how to close these update
windows without having to select a file name? It was driving me mad
just now since the Cancel or X close didn't seem to have any effect,
and I had to step through all the Replaces on a sheet before the macro
would finish).

a) is this the best approach to adopt or can someone suggest a better
way? I'm thinking for instance that it might be better to move all the
closing balances into the opening balance cells as values, immediately
prior to the rollover so that I avoid links to other workbooks. In
which case I'd need to be able to identify all the opening and closing
balance cells, which might be more complicated than finding specific
text since it seems inelegant to have to create hundreds of individual
range names

b) if the Find/Replace is the best way to proceed, what code should I
use in order to replace these strings across all the relevant sheets,
so that the user doesn't have to continually click on file names. If it
makes any difference, not all the sheets have balances on them, but if
it's just as easy to loop through all the sheets I could live with
that.

If responding to the ng, please email to me as well.

Many thanks in advance.

Richard Buttrey

Changing embedded file name
 

Changing embedded file name
From: Tom Ogilvy
Date Posted: 9/27/2005 12:29:00 PM

Edit=Links select the link and do Change source.

--
Regards,
Tom Ogilvy


Tom, many thanks.

The obvious was once more staring me in the face

Rgds,


Richard Buttrey
Grappenhall, Cheshire, UK

*** Sent via Developersdex http://www.developersdex.com ***