Macro to update link within Current workbook

[howard]

I have the following code to update links within the current workbook

 Sub Update_Links
ActiveWorkbook.UpdateLink Type:=xlExcelLinks
End Sub

when activating the code the folder is opened , I then need to select the workbook C:\my documents Sales report Ver 1.4.xlsm and then select sheets 'Data" to update the workbook


I would like to incorporate this workbook and sheet into my code so I do not have to select this manually


your assistance in this regard is most appreciated[/code]

 

[sijpie]

can this help?

Macro to modify external links - OzGrid Free Excel/VBA Help Forum

Hi, I have the following problem: My main excel file has links to an external excel file and I want to update/modify these links with a macro. The formatting and source cells within the external file always remains the same but the name of the file…

www.ozgrid.com

 

[howard]

Thanks for the help, but when opening the workwook, i first select do not update and the copy the data and paste as valuesinto the previous month. I then save the file & re-open and then select update and the currect data is updated based on several links

I would like a macro to update all the links instead of the manual process

 

[sijpie]

Understood.

By the way, you shouldn't need to close & reopen the file to update the links. If you go to the Data tab in the menu, you will see a button to refresh all links.

To do it in a macro, add the following line to your macro:

    ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks

 

[howard]

Thanks for the advise and your code