如何从单元格打开超链接并从新单元格值保存/重命名下载的文件? [英] How to open hyperlink from a cell and save/rename downloaded file from new cell value?

问题描述

我有一个链接表.我想打开每个链接(它们是我必须登录的安全服务器上的 .csv/.xlsx 文件)，然后使用由列 (C) 中的信息生成的文件名进行保存，以保持 BatchID 不变.

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 |    

链接可能会在登录时弹出(没关系，我有凭据，但如果使用 VBA，将其硬编码会更安全，因此如果我错过了弹出窗口，它不会停止).

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).

列表内容广泛，每月更新一次.
原来的 csv/.xlsx 文件名类似:
1036548025_detailed_sales_report.xls 数字始终不同(因此出现了上面的新列)，但 _detailed_sales_report.xls 保持不变.

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.

我希望通过使用通配符来保留文件名.所以像:
savefilename = (A10) &*"&_detailed_sales_report.xls"

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

我用谷歌搜索从 Excel 中打开超链接，但其中大部分内容要么是关于一般超链接，要么是打开链接的工作簿，而我想下载新作品.

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

以上是我运行宏记录器时发生的情况，我不想编写超过 100 行的代码.两列之间可以循环工作吗?

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?

推荐答案

我无法模拟你打开的链接，但你可以试试这个:

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

我假设 Excel 可以直接使用地址打开链接中的文件.
上面代码的作用是遍历目标工作表中的所有超链接.
hlink.Address 获取地址，然后在 Workbooks.Open 方法中使用它.
然后使用 hlink.Parent 作为文件名将其保存为 .xlsx 文件.
hlink.Parent 返回超链接词.
为了完成另存为路径，我们初始化了文件位置saveloc.
这是尝试和测试，但仅限于我在本地驱动器中创建的链接.

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 返回超链接所在的 Range Object.
我们使用 Offset 属性来获取相邻单元格，然后获取它的文件名值.

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.

这篇关于如何从单元格打开超链接并从新单元格值保存/重命名下载的文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！