ChangeLink - What's wrong with the following?

[daliano71]

Hello everyone,

I'm new here so please have a little patience. Can anyone tell me what
is wrong with the following? I basically have a series of links that
look like these:

S:\Finance\Periodic Reporting\Monthly Files
\2006\b_February\AData_2006.xls
S:\Finance\Periodic Reporting\Monthly Files
\2006\b_February\BData_2006.xls -> etc until E

These files are the links in a master files. Every month, these links
have to be updated and only the part that represents a month
(b_february etc) needs to change for the new month. The following
attempts to do just that :
- create a list of new links
- replace old links with these links.
The problem is that the macro works only for the first two links, so
the master file will get updated for Plant(1) and Plant(2). After
Plant(2), I get "'ChangeLink' of object '_Workbook' failed".

Can anyone help me?

Thank you so much,

Puzzled and frustrated


Sub MacroLinks()
Dim MyLinks, OldData, NewData, FixedMonthlyPart
Dim Plant(1 To 5) As String
Dim i As Integer, j As Integer
Dim CurrentMonth As String, NewMonth As String

Plant(1) = "A"
Plant(2) = "B"
Plant(3) = "C"
Plant(4) = "D"
Plant(5) = "E"

FixedMonthlyPart = "S:\Finance\Periodic Reporting\Monthly Files\" &
Year(Date) & "\"

NewMonth = Choose(Month(Date) - 1, "a_January", "b_February",
"c_March", _
"d_April", "e_May", "f_June", "g_July", "h_August", "i_Sep", "j_Oct",
"k_Nov", "l_Dec")

CurrentMonth = Choose(Month(Date) - 2, "a_January", "b_February",
"c_March", _
"d_April", "e_May", "f_June", "g_July", "h_August", "i_Sep", "j_Oct",
"k_Nov", "l_Dec")

MyLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

For i = 1 To UBound(Plant)
NewData = FixedMonthlyPart & NewMonth & "\" & Plant(i) & "Data_" &
Year(Date) & ".xls"
OldData = FixedMonthlyPart & CurrentMonth & "\" & Plant(i) & "Data_" &
Year(Date) & ".xls"
For j = 1 To UBound(MyLinks)
ActiveWorkbook.ChangeLink MyLinks(j), Replace(MyLinks(j), OldData,
NewData)
Next j
Next i

Exit Sub