how do I programatically edit formulae in MS Excel?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Don Haworth - 19 Sep 2006 08:46 GMT

In MS EXcel 2003, I have a master workbook with 5 feeder workbooks to enable
a city manager to collect monthy returns from 5 area managers. So in Jan '06
I have CityJan06.xls with many links to NJan06.xls, SJan06.xls, NWJan06.xls,
EJan06.xls and NEJan06.xls. When all the Jan feeders are complete, I want to
programatically copy all 6 workbooks into February's locations with Feb's
dates on the sheets (this I can do) and then edit the links in the Feb master
workbook to point to the new Feb feeders(which I haven't figured out yet).
So, one link in the master wkbk might be"=G:\CSSN06\NJan06\NJan06.xls" and in
the February copy I want to change that programatically to
"-G:\CSSN06\NFeb06\NFeb06.xls". At this stage, I'm not bothered about the
year change in Dec. Everything else in the link stays the same except for the
3-character month name

Signature

Don Haworth

Reply to this Message

Bernie Deitrick - 19 Sep 2006 13:51 GMT

You could do something like this: I assumed all your links are on one sheet:

Sub Macro1()

Dim myRep As String
Dim myFind As String

myFind = InputBox("What month string to replace?")
myRep = InputBox("Replace it with?")

Cells.Replace What:=myFind, Replacement:=myRep, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Answer Jan to the first, and Feb to the second....

HTH,
Bernie
MS Excel MVP

> In MS EXcel 2003, I have a master workbook with 5 feeder workbooks to enable
> a city manager to collect monthy returns from 5 area managers. So in Jan '06
[quoted text clipped - 8 lines]
> year change in Dec. Everything else in the link stays the same except for the
> 3-character month name

Reply to this Message