Find & Replace w/ Name Changes
 

I have a workbook that I use month to month named "Report
MTH YY" This month the report is named "Report Jul 04".
Next month the report will be renamed "Report Aug 04",
then "Report Sep 04" etc.

I have formulas that reference last months report. As
simple as " ='[REPORT Jun 04.xls]Sheet 1'!A1". Next
month in "Report Aug 04" I want the formula to reference
the Jul report i.e. ='[REPORT Jul 04.xls]Sheet 1'!A1" .

I am looking for a way to automate the process rather
than manually using "Find & Replace", but I do not know
how to get the month names to change automatically.

More specifically, I am looking for a way to create a
macro using "Find & Replace" that will find and replace
the month name. So I have;

Cells.Select
Selection.Replace What:="Month(Now()-1",
Replacement:="Month (Now()", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Obviously it does not work. I need to know how to create
the "What:=" and the "Replacement:=" to -
What:= "last month (Jun)"
Replacement:= this month (Jul)

Thanks to Tom Ogilvy for the solution using "Indirect",
however the workbook that the data is generated from is
never open, but it is always in the same directory.


Thanks for any additional help.

Find & Replace w/ Name Changes
 

Changing the formula with replace would be the "goofy" way to do it.

Turn on the macro recorder and then do

Edit=Links=Change Source and change the source to "new" last month's
workbook.

That will give you the code you need. You can then modify it to make it
dynamic.

to format the date to match your file naming convention

Sub Tester2()
Dim dt As Date, oldDt As Date
Dim oldoldDt As Date
Dim sOldMonthandYear As String
dt = Date
oldDt = DateSerial(Year(dt), Month(dt) - 1, 1)
oldoldDt = DateSerial(Year(oldDt), Month(oldDt) - 1, 1)
sCurrentLinkName = "Report " & Format(oldoldDt, "mmm yy") & ".xls"
sOldMonthandYear = "Report " & Format(oldDt, "mmm yy") & ".xls"
Debug.Print dt, oldDt, oldoldDt
Debug.Print sCurrentLinkName, sOldMonthandYear
End Sub

produces:
7/20/2004 6/1/2004 5/1/2004
Report May 04.xls Report Jun 04.xls

So the current link is to the file named for two months ago and you want to
change it to the file name for one month ago.
--
Regards,
Tom Ogilvy




"ronbo" wrote in message
...

I have a workbook that I use month to month named "Report
MTH YY" This month the report is named "Report Jul 04".
Next month the report will be renamed "Report Aug 04",
then "Report Sep 04" etc.

I have formulas that reference last months report. As
simple as " ='[REPORT Jun 04.xls]Sheet 1'!A1". Next
month in "Report Aug 04" I want the formula to reference
the Jul report i.e. ='[REPORT Jul 04.xls]Sheet 1'!A1" .

I am looking for a way to automate the process rather
than manually using "Find & Replace", but I do not know
how to get the month names to change automatically.

More specifically, I am looking for a way to create a
macro using "Find & Replace" that will find and replace
the month name. So I have;

Cells.Select
Selection.Replace What:="Month(Now()-1",
Replacement:="Month (Now()", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Obviously it does not work. I need to know how to create
the "What:=" and the "Replacement:=" to -
What:= "last month (Jun)"
Replacement:= this month (Jul)

Thanks to Tom Ogilvy for the solution using "Indirect",
however the workbook that the data is generated from is
never open, but it is always in the same directory.


Thanks for any additional help.