Updating links to external files using macros
 

Hi all

I have a file which contains links to data in an external file. Every week I
need to update the link to a new file that is created. So for example, at the
moment the link goes to a file called "Sales Firm 2010 2009-11-12", next week
there will be a new file called "Sales Firm 2010 2009-11-19" which I will
want to change this link to. The following week yet another external file
will be created which I will want to change the links to again. All the
external files are the same i.e. format, where the data is located, etc it's
just new data.

At the moment I just go into "edit" then "links" and update from there but I
want to pass this job on to other users who can just update at the push of a
button i.e. I don't want to teach them all how to update links!

Is this possible?


--
James.

Updating links to external files using macros
 

Please paste the following code in the ThisWorkbook module:

'---------------------------------------------------------------------------------------
' Procedure : Workbook_Open
' Author : Dan
' Date : 11/19/2009
' Purpose : Assign Keyboard shortcut Ctrl+Shift+U to the macro
'---------------------------------------------------------------------------------------
'
Private Sub Workbook_Open()

On Error GoTo Workbook_Open_Error

Application.OnKey "^+U", "UpdateLinks"

Workbook_Open_Exit:
On Error GoTo 0
Exit Sub

Workbook_Open_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
Workbook_Open of VBA Document ThisWorkbook"
GoTo Workbook_Open_Exit

End Sub


Please insert a module and paste this code:

Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : UpdateLinks Macro
' Author : Dan
' Date : 11/19/2009
' Purpose : Update links to the new external file Keyboard Shortcut:
Ctrl+Shift+U
'---------------------------------------------------------------------------------------
'
Sub UpdateLinks()
Dim dlgOpen As FileDialog
Dim aLinks As Variant, i As Integer
Dim oldLink As String, newLink As String, FileFolder As String

On Error GoTo UpdateLinks_Error

aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
If InStr(1, aLinks(i), "Sales Firm") 0 Then oldLink = aLinks(i)
Next i
Else
Err.Number = -99
Err.Description = "There are no links in this workbook!"
GoTo UpdateLinks_Error
End If

FileFolder = Left(oldLink, InStr(1, oldLink, "Sales Firm") - 1)

Set dlgOpen = Application.FileDialog(msoFileDialogOpen)

With dlgOpen
.InitialFileName = FileFolder
.AllowMultiSelect = False
.Title = "Select the new file"
If .Show = -1 Then
newLink = .SelectedItems(1)
Else
Err.Number = -99
Err.Description = "You must select a file!"
GoTo UpdateLinks_Error
End If
End With

For i = 1 To UBound(aLinks)
If InStr(1, aLinks(i), "Sales Firm") 0 Then
ActiveWorkbook.ChangeLink aLinks(i), newLink, xlExcelLinks
Next i


UpdateLinks_Exit:
On Error GoTo 0
Err.Clear
Set dlgOpen = Nothing
Set aLinks = Nothing
Exit Sub

UpdateLinks_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
UpdateLinks"
GoTo UpdateLinks_Exit

End Sub

Let me know if it works out.

Dan


"James" wrote:

Hi all

I have a file which contains links to data in an external file. Every week I
need to update the link to a new file that is created. So for example, at the
moment the link goes to a file called "Sales Firm 2010 2009-11-12", next week
there will be a new file called "Sales Firm 2010 2009-11-19" which I will
want to change this link to. The following week yet another external file
will be created which I will want to change the links to again. All the
external files are the same i.e. format, where the data is located, etc it's
just new data.

At the moment I just go into "edit" then "links" and update from there but I
want to pass this job on to other users who can just update at the push of a
button i.e. I don't want to teach them all how to update links!

Is this possible?


--
James.

Updating links to external files using macros
 

Hi Dan

Thank you for all your help. I got an error message which said "Compile
Error: Next without For" under

For i = 1 To UBound(aLinks)
If InStr(1, aLinks(i), "Sales Firm") 0 Then
ActiveWorkbook.ChangeLink aLinks(i), newLink, xlExcelLinks
Next i



--
James.


"Dan" wrote:

Please paste the following code in the ThisWorkbook module:

'---------------------------------------------------------------------------------------
' Procedure : Workbook_Open
' Author : Dan
' Date : 11/19/2009
' Purpose : Assign Keyboard shortcut Ctrl+Shift+U to the macro
'---------------------------------------------------------------------------------------
'
Private Sub Workbook_Open()

On Error GoTo Workbook_Open_Error

Application.OnKey "^+U", "UpdateLinks"

Workbook_Open_Exit:
On Error GoTo 0
Exit Sub

Workbook_Open_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
Workbook_Open of VBA Document ThisWorkbook"
GoTo Workbook_Open_Exit

End Sub


Please insert a module and paste this code:

Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : UpdateLinks Macro
' Author : Dan
' Date : 11/19/2009
' Purpose : Update links to the new external file Keyboard Shortcut:
Ctrl+Shift+U
'---------------------------------------------------------------------------------------
'
Sub UpdateLinks()
Dim dlgOpen As FileDialog
Dim aLinks As Variant, i As Integer
Dim oldLink As String, newLink As String, FileFolder As String

On Error GoTo UpdateLinks_Error

aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
If InStr(1, aLinks(i), "Sales Firm") 0 Then oldLink = aLinks(i)
Next i
Else
Err.Number = -99
Err.Description = "There are no links in this workbook!"
GoTo UpdateLinks_Error
End If

FileFolder = Left(oldLink, InStr(1, oldLink, "Sales Firm") - 1)

Set dlgOpen = Application.FileDialog(msoFileDialogOpen)

With dlgOpen
.InitialFileName = FileFolder
.AllowMultiSelect = False
.Title = "Select the new file"
If .Show = -1 Then
newLink = .SelectedItems(1)
Else
Err.Number = -99
Err.Description = "You must select a file!"
GoTo UpdateLinks_Error
End If
End With

For i = 1 To UBound(aLinks)
If InStr(1, aLinks(i), "Sales Firm") 0 Then
ActiveWorkbook.ChangeLink aLinks(i), newLink, xlExcelLinks
Next i


UpdateLinks_Exit:
On Error GoTo 0
Err.Clear
Set dlgOpen = Nothing
Set aLinks = Nothing
Exit Sub

UpdateLinks_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
UpdateLinks"
GoTo UpdateLinks_Exit

End Sub

Let me know if it works out.

Dan


"James" wrote:

Hi all

I have a file which contains links to data in an external file. Every week I
need to update the link to a new file that is created. So for example, at the
moment the link goes to a file called "Sales Firm 2010 2009-11-12", next week
there will be a new file called "Sales Firm 2010 2009-11-19" which I will
want to change this link to. The following week yet another external file
will be created which I will want to change the links to again. All the
external files are the same i.e. format, where the data is located, etc it's
just new data.

At the moment I just go into "edit" then "links" and update from there but I
want to pass this job on to other users who can just update at the push of a
button i.e. I don't want to teach them all how to update links!

Is this possible?


--
James.

Updating links to external files using macros
 

Sorry ignore that! It's saying "Complie Error: Invalid outside procedure"
when I run it which is off the "Update Links" module.
--
James.


"Dan" wrote:

Please paste the following code in the ThisWorkbook module:

'---------------------------------------------------------------------------------------
' Procedure : Workbook_Open
' Author : Dan
' Date : 11/19/2009
' Purpose : Assign Keyboard shortcut Ctrl+Shift+U to the macro
'---------------------------------------------------------------------------------------
'
Private Sub Workbook_Open()

On Error GoTo Workbook_Open_Error

Application.OnKey "^+U", "UpdateLinks"

Workbook_Open_Exit:
On Error GoTo 0
Exit Sub

Workbook_Open_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
Workbook_Open of VBA Document ThisWorkbook"
GoTo Workbook_Open_Exit

End Sub


Please insert a module and paste this code:

Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : UpdateLinks Macro
' Author : Dan
' Date : 11/19/2009
' Purpose : Update links to the new external file Keyboard Shortcut:
Ctrl+Shift+U
'---------------------------------------------------------------------------------------
'
Sub UpdateLinks()
Dim dlgOpen As FileDialog
Dim aLinks As Variant, i As Integer
Dim oldLink As String, newLink As String, FileFolder As String

On Error GoTo UpdateLinks_Error

aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
If InStr(1, aLinks(i), "Sales Firm") 0 Then oldLink = aLinks(i)
Next i
Else
Err.Number = -99
Err.Description = "There are no links in this workbook!"
GoTo UpdateLinks_Error
End If

FileFolder = Left(oldLink, InStr(1, oldLink, "Sales Firm") - 1)

Set dlgOpen = Application.FileDialog(msoFileDialogOpen)

With dlgOpen
.InitialFileName = FileFolder
.AllowMultiSelect = False
.Title = "Select the new file"
If .Show = -1 Then
newLink = .SelectedItems(1)
Else
Err.Number = -99
Err.Description = "You must select a file!"
GoTo UpdateLinks_Error
End If
End With

For i = 1 To UBound(aLinks)
If InStr(1, aLinks(i), "Sales Firm") 0 Then
ActiveWorkbook.ChangeLink aLinks(i), newLink, xlExcelLinks
Next i


UpdateLinks_Exit:
On Error GoTo 0
Err.Clear
Set dlgOpen = Nothing
Set aLinks = Nothing
Exit Sub

UpdateLinks_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
UpdateLinks"
GoTo UpdateLinks_Exit

End Sub

Let me know if it works out.

Dan


"James" wrote:

Hi all

I have a file which contains links to data in an external file. Every week I
need to update the link to a new file that is created. So for example, at the
moment the link goes to a file called "Sales Firm 2010 2009-11-12", next week
there will be a new file called "Sales Firm 2010 2009-11-19" which I will
want to change this link to. The following week yet another external file
will be created which I will want to change the links to again. All the
external files are the same i.e. format, where the data is located, etc it's
just new data.

At the moment I just go into "edit" then "links" and update from there but I
want to pass this job on to other users who can just update at the push of a
button i.e. I don't want to teach them all how to update links!

Is this possible?


--
James.