I need to run a macro that searches a range (column) that contains references
to another spread sheet in the function line e.g.,

='I:\REPORTS\Fairfield\[c_090204.xls]Route1'!$G14
The 090204 is yesterday's date.

I need to replace 090204 with 090304 (being today's date) in the reference
in all cells within the search range.

Can this be done automatically without prompting the user to enter the
values?
I have not been able to figure it out.

Hi,
Menu Edit Links, do a Chnage Source.

Regards,
Sébastien

"GenerallyConfused" wrote:

I need to run a macro that searches a range (column) that contains references
to another spread sheet in the function line e.g.,

='I:\REPORTS\Fairfield\[c_090204.xls]Route1'!$G14
The 090204 is yesterday's date.

I need to replace 090204 with 090304 (being today's date) in the reference
in all cells within the search range.

Can this be done automatically without prompting the user to enter the
values?
I have not been able to figure it out.

also, programmatically, if really needed:
ActiveWorkbook.ChangeLink Name:="I:\REPORTS\Fairfield\c_090204.xls", _
NewName:= "I:\REPORTS\Fairfield\c_090304.xls", _
Type:= xlExcelLinks

Regards,
Sebastien

Thanks again, I'll let you know after I test it tomorrow.

Jimmy

"sebastienm" wrote:

Hi,
Your dates are in the 'mmddyy' format, therefore something like:
Name:="I:\REPORTS\Fairfield\c_" & Format(date()-1, "mmddyy") & ".xls"
and
NewName:= "I:\REPORTS\Fairfield\c_" & Format(date(), "mmddyy") & ".xls"

(not tested though)
Regards,
Sebastien

"GenerallyConfused" wrote:

sebastienm, thank you very much for your post. It is quite helpful. Do you
know of any way to make the first date automatically run as yesterday's date,
and the second date automatically run as today's date without using a prompt
to have the current user enter the two dates manually. I have tried using
the "today" &
"today-1" functions, but they don't seem to work in the macro.

Thanks again for your help

jimmy

"sebastienm" wrote:

also, programmatically, if really needed:
ActiveWorkbook.ChangeLink Name:="I:\REPORTS\Fairfield\c_090204.xls", _
NewName:= "I:\REPORTS\Fairfield\c_090304.xls", _
Type:= xlExcelLinks

Regards,
Sebastien