ChangeLink - What's wrong with 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 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. 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,

Clueless 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

 

[protonLeah]

I'm Confused??:

- My understanding of the "CHOOSE" function is that it takes
positive index from 1 to 29; but you subtract a 1 and a 2 from th
current month which would seem to cause problems for January an
February.
Is NewMonth previous to, or following, the current month?
- You did not dim MyLinks as an array as required by the "j" loop