Change source of shortcut in Excel with VBA-CodePudding

I have a huge excel which multiple different data sources. Everyday I need to manually change the data source because the files are always like xxx_2022_08_04.xlsx. Now I was hoping to make a macro of all the data sources and than change them via vba.

I have the new source paths in my table but I can't figure out how to access the shortcut options and how to change the path of each.

So lets say I want to access shortcut 'Profit' which path .../xxx_2022_08_03.xlsx and change it too .../xxx_2022_08_04.xlsx.

How would I do this in vba?

CodePudding user response：

One way would be to call the Edit Links dialog box in order to replace the links in question. You will find this in the Data tab in the group Queries and connection or you use ChangeLink

ActiveWorkbook.ChangeLink Name:= _
    "<Path>\xlfile", NewName:= _
    "<Path>\newxlfile", Type:= _
    xlExcelLinks