Changing links via VBA

[Niels]

Hi

I have a spreadsheet with lots of formulas that lookup numbers in
three other spreadsheets. Each month I have to change the links to
three other spreadsheets.

I am trying to use the following code to do that. It works fine on the
first pane, but all the other panes are not changed. The code seems to
go through the panes correctly, so I am out of ideas. I am using Excel
2000 (danish version).

Could someone help me out.

Regards

Niels


My Code:
Private Sub CommandButton1_Click()

Sheets("Opslag").Select

kalkmd = Range("C2")
kalkår = Range("C3")

If kalkmd < 10 Then
Md_gl_0 = "0" & kalkmd
Else
Md_gl_0 = kalkmd
End If

If kalkmd < 11 Then
Md_gl_1 = "0" & kalkmd - 1
Else
Md_gl_1 = kalkmd - 1
End If

If kalkmd < 12 Then
Md_gl_2 = "0" & kalkmd - 2
Else
Md_gl_2 = kalkmd - 2
End If

Md_gl_3 = "0" & kalkmd - 3
Md_gl_4 = "0" & kalkmd - 4

En_md_gl = kalkår & "\[Lønkalkule (" & kalkår & "_" & Md_gl_2 &
").xls]"
En_md_ny = kalkår & "\[Lønkalkule (" & kalkår & "_" & Md_gl_1 &
").xls]"

Tre_md_gl = kalkår & "\[Lønkalkule (" & kalkår & "_" & Md_gl_4 &
").xls]"
Tre_md_ny = kalkår & "\[Lønkalkule (" & kalkår & "_" & Md_gl_3 &
").xls]"

Sår_md_gl = kalkår - 1 & "\[Lønkalkule (" & kalkår - 1 & "_" & Md_gl_1
& ").xls]"
Sår_md_ny = kalkår - 1 & "\[Lønkalkule (" & kalkår - 1 & "_" & Md_gl_0
& ").xls]"

For Each ark In ActiveWorkbook.Sheets
ark.Select
ark.Activate
ActiveSheet.Range("A1").Select
'Range("A1:IV65536").Select

' Ret henvisninger til sidste måned
Cells.Replace What:=En_md_gl, Replacement:=En_md_ny,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

' Ret henvisninger til tre måneder siden
Cells.Replace What:=Tre_md_gl, Replacement:=Tre_md_ny,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

' Ret henvisninger til samme måned sidste år
Cells.Replace What:=Sår_md_gl, Replacement:=Sår_md_ny,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Next

Sheets("Opslag").Select
Range("a1").Select

End Sub

 

[papou]

Hello Niels

Dim ark As WorkSheet
For Each ark In ActiveWorkbook.Sheets
ark.Cells.Replace What:=En_md_gl, Replacement:=En_md_ny,LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
'...and so on
(Note: there is no need for selecting sheet or range)

HTH
Cordially
Pascal

 

[Tom Ogilvy]

Links are defined at the workbook level. If you want to change a link to
another workbook, you go to Edit=>Links, select a link and click the change
source button. Turn on the macro recorder while you do this manually and
you get the code you need. You can then modify that to accept a string
variable to specify the old and new links. (it will record the ChangeLink
command.)