excel - How to open hyperlink from a cell and save/rename downloaded file from new cell value?

Question

I have a table of links. I want to open each link (they are .csv/.xlsx files on a secure server which I have to login to), then saveas with a file name generated by information in Column (C), to keep the BatchID intact.

|     A      |      B       |    C     | 
| Batch Date | links        | New name | 
|------------|--------------|----------|
| 3/03/2014  | View         | 20140303 |
| 2/05/2014  | View         | 20140205 |
| 1/02/2014  | View         | 20140102 |
| 12/01/2013 | View         | 20131201 |
| 11/01/2013 | View         | 20131101 |
| 10/01/2013 | View         | 20131001 |
| 9/01/2013  | View         | 20130901 |
| 8/01/2013  | View         | 20130801 |    

The links may popup at login (that's ok, I have the credentials, though if using VBA it would be safer to hard code that in so if I miss a popup it won't stall).

The list is extensive and is updated monthly.
The original csv/.xlsx filename is similar:
1036548025_detailed_sales_report.xls the number always differs (hence the new column above), however the _detailed_sales_report.xls remains static.

I would ideally like to keep the filename by using a wildcard. So something like:
savefilename = (A10) & "*" & "_detailed_sales_report.xls"

I googled opening hyperlinks from Excel but most of it is either about hyperlinks in general or opening a linked workbook, whereas I want to download new work.

There needs to be a delay between each row, the server is slow.

Each link is newly generated. No link is the same (like the file name):

    Range("C9").Select
      Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
        Workbooks.Open Filename:="http://www.mylinkgoeshere.---/.php?pID=12898"
            'the pID= is always different, its a dynamic report system

        ActiveWindow.Visible = False
        Windows("view_payment_orders.php").Visible = True
        ActiveWorkbook.SaveAs Filename:="D:location20140205.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWorkbook.Save
    ActiveWindow.Close

The above is what happens when I run the Macro recorder, I don't want to have to code in over 100+ lines. Can a loop work between two columns?

Answer 1

I can't possibly simulate the links you're opening but you can try this:

Sub test()

Dim hlink As Hyperlink
Dim wb As Workbook
Dim saveloc As String

saveloc = "D:location"
For Each hlink In ThisWorkbook.Sheets("NameOfYourSheet").Hyperlinks
    Set wb = Workbooks.Open(hlink.Address)
    wb.SaveAs saveloc & hlink.Parent & ".xlsx"
    wb.Close True
    Set wb = Nothing
Next

End Sub

I assumed that Excel can open the files in your link directly just using the address.
What above code does is loop through all hyperlinks in your target sheet.
hlink.Address gets the address and then use it in Workbooks.Open method.
You then save it as .xlsx file using the hlink.Parent as filename.
hlink.Parent returns the hyperlinked word.
To complete the save as path, we initialized the file location saveloc.
This is tried and tested but only on links I created which are in my local drive.

Edit1: To save using the hyperlink's adjacent cell value.

For Each hlink In ThisWorkbook.Sheets("NameOfYourSheet").Hyperlinks
    Set wb = Workbooks.Open(hlink.Address)
    wb.SaveAs saveloc & hlink.Range.Offset(0,1).Value & ".xlsx"
    wb.Close True
    Set wb = Nothing
Next

hlink.Range returns a Range Object where the hyperlink is.
We use Offset property to get to the adjacent cell and then get it's value for the filename.