ChangeLink

[Joe Bannister]

Hi all,

I know i've asked questions before about ChangeLink but
can anyone tell me why the following code is debugging at
the ChangLink statement?

Sheets("APR(4)").Select

Dim thisyear As String
thisyear = "C:\headoffice\"
thisyear = thisyear & "52_"
thisyear = thisyear & CStr(Range("D5").Text)
thisyear = thisyear & "_prestonweekly.xls"

Range("AA1").Select
Selection.Copy
Range("D5").Select
Selection.PasteSpecial Paste:=xlValue,
Operation:=xlAdd, SkipBlanks:=False, Transpose:=False

Dim nextyear As String
nextyear = "C:\headoffice\"
nextyear = nextyear & "01_"
nextyear = nextyear & CStr(Range("D5").Text)
nextyear = nextyear & "_prestonweekly.xls"

Sheets("MAR(4)").Select
Range("F34:V41").Select
Selection.Copy
Sheets("APR(4)").Select
Range("F10").Select
Selection.PasteSpecial Paste:=xlPasteFormulas,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveWorkbook.ChangeLink thisyear, nextyear,
Type:=xlExcelLinks


Any help greatly appreciated.

Cheers

Joe

 

[Dave Peterson]

my first guess is that the strings don't point to the correct spots.

You could check:

msgbox dir(thisyear)
msgbox dir(nextyear)

to see if you get a filename or "" (nothing back).

If that wasn't it, you may want to post the actual error message.

And where is this code placed? Is it in a general module or behind a worksheet?

Maybe range("d5") is on the sheet you expected.

 

[Joe Bannister]

Cheers Dave,

I knew it would something straight forward.
Point to the correct spot.
Doh!

Joe