changing links using vba

[excelguru123]

hey folks,

so I have a file that uses 3 different files that need to be updated on a monthly basis. every month, the files get saved into the appropriate month's folder (every month a new folder is created and all new work is saved into that folder). for example

• for may the folder is named: \\path\\majorfolder\subfolder\may2021\xlfile
• for june it will be: \\path\\majorfolde\subfolder\june2021\xlfile

to take it a step further: in a single month, several drafts of the 3 linked files are created so they will be as follows:

• again for may, it will be \\path\\majorfolde\subfolder\may2021\xlfile_draft_1
  • \\path\\majorfolde\subfolder\may2021\xlfile_draft_2
• similarly for june: \\path\\majorfolde\subfolder\june2021\xlfile_draft_1
  • and so on

each time a new draft is added, the links need to be updated to include the most recent draft of the 3 files. Right now, I use Data->Edit Links to update each file everytime a new draft gets added. I want to be able to automate this so that I can update to the latest draft of each of the 3 links with a single button. I tried to run a macro but when I do it shows the old file path as the previous months file. But it can also be a link of the same month but a different draft. What's the best way to do this?

So far, I used the following code to pull all the links out:

aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then 
For i = 1 to Ubound(aLinks) 
Cells(i,1).Value = aLinks(i)
Next i 
End If

This pulls out the current links in the file -- I want to then be able to update these to the most recent month and drafts -- how do I go about doing that?

If you're still with me, thanks for reading!!!