Edit links to a file in VBA (in Publisher)

Question

I am trying to edit the links to a lot of Excel file using Publisher. Given that Microsoft seems to not allow to use relative links, I am trying to create something similar in VBA. I did not have found a lot of documentation online, just some reference to LinkSources.

Is there anyway possible to change those links with VBA?

score 0 · Accepted Answer · answered Apr 01 '15 at 08:31

I assume you know how to add the reference to the Excel object library. Then to read the links:

dim xl as New Excel.Application
xl.Visible = True
Dim wb As Workbook
Set wb = xl.Workbooks.Open("C:\path to \Myworkbook.xlsx")
Dim v As Variant
v = wb.LinkSources(xlExcelLinks)
Dim first_link as String
first_link = v(1) 
MsgBox "First link is to " & first_link

To change the links:

Dim new_link as String
new_link = "C:\path to\Alternative link.xlsx"
wb.ChangeLink first_link, new_link

Hopefully you get the idea.

Edit links to a file in VBA (in Publisher)

1 Answers1