VBA to follow hyperlinks to different files and save in alt folder

[Randall83]

I have a workbook with a column of 1000+ hyperlinks that link to numerous files of different types in numerous different folder locations on one drive and I want to automate the process of saving those documents to one folder location using the adjacent cell as the file name. There will be a mix of doc types .jpg, .docx, .doc, .pdf, .xls etc - if they can be processed at the same time that would be great however if I have to have a VBA for each doc type I don't mind.


I have searched for solutions to this issue however the closest I had found was for opening workbooks in excel and using saveas to save as a single doc type and having to split each doc type to its own sheet in the workbook. This solution was opening all file types as if it were an .xlsx file and saving it as a .jpg (for example) so obviously corrupting the file.

Sub test()

Dim hlink As Hyperlink
Dim wb As Workbook
Dim saveloc As String
saveloc = "C:\My Documents\"
For Each hlink In ThisWorkbook.Sheets("jpg").Hyperlinks
    Set wb = Workbooks.Open(hlink.Address)
    wb.SaveAs saveloc & hlink.Range.Offset(0, 1).Value & ".jpg"
    wb.Close True
    Set wb = Nothing
Next

End Sub

Any ideas on how I can achieve this?


Many thanks