（VBA 或公式）从单元格打开超链接并从新单元格值保存/重命名下载的文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22662795/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
(VBA or Formula) to open hyperlink from a cell and save/rename downloaded file from new cell value
提问by MrsAdmin
Program:Excel 2010
Experience Level:Basic
程序：Excel 2010
经验水平：基础
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 a 3rd row of information Column (C)to keep the BatchID intact (as below).
问题：
我有一个链接表，我想打开每个链接（它们是我必须登录的安全服务器上的 .csv/.xlsx 文件），然后用第三行信息生成的文件名保存Column (C)到保持 BatchID 不变（如下所示）。
| 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 a 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).
这些链接可能会弹出一个登录（没关系，我有凭据，但如果使用 VBA，将其硬编码会更安全，因此如果我错过了一个弹出窗口，它就不会停止）。
The list is extensive and is updated monthly.
The original csv/.xlsx filename is similar:1036548025_detailed_sales_report.xlsthe number always differs (hence the new column above), however the _detailed_sales_report.xlsalways remains static.
该列表内容广泛，每月更新一次。
原始的 csv/.xlsx 文件名是相似的：1036548025_detailed_sales_report.xls数字总是不同的（因此上面的新列），但_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"
理想情况下，我希望使用通配符保留文件名？所以像：
savefilename = (A10) & "*" & "_detailed_sales_report.xls"
I have googled opening hyper links from Excel but most of it is either about hyperlinks in general or opening a linked workbook, whereas I want to use this to download new work.
我用谷歌搜索过从 Excel 中打开超链接，但其中大部分内容要么是关于一般超链接，要么是打开链接的工作簿，而我想用它来下载新作品。
EDIT
There also needs to be a delay between each row, the server is notoriously slow.
编辑
每行之间也需要延迟，服务器是出了名的慢。
Edit 3Each link is a newly generated one, no link is the same (like the file name):
编辑 3每个链接都是新生成的，没有链接是一样的（就像文件名一样）：
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:\location140205.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 2 different columns?
以上是我运行宏记录器时发生的情况，我不想编写超过 100 行的代码 :-) 循环可以在 2 个不同的列之间工作吗？
Please help, where do I go from here?!
请帮忙，我从这里去哪里？！
回答by L42
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.Addressgets the address and then use it in Workbooks.Openmethod.
You then save it as .xlsxfile using the hlink.Parentas filename.hlink.Parentreturns the hyperlinked word.
To complete the save as path, we initialized the file location saveloc.
This is triedand testedbut only on links I created which are in my local drive.
我假设 Excel 可以直接使用地址打开链接中的文件。
上面的代码所做的是遍历目标工作表中的所有超链接。hlink.Address获取地址，然后在Workbooks.Open方法中使用它。
然后.xlsx使用hlink.Parentas 文件名将其另存为文件。hlink.Parent返回超链接词。
为了完成另存为路径，我们初始化了文件位置saveloc。
这是经过尝试和测试的，但仅限于我在本地驱动器中创建的链接。
Edit1:To save using the hyperlink's adjacent cell value.
Edit1：使用超链接的相邻单元格值进行保存。
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.Rangereturns a Range Objectwhere the hyperlink is.
We use Offsetproperty to get to the adjacent cell and then get it's value for the filename.
hlink.Range返回Range Object超链接所在的位置。
我们使用Offset属性来获取相邻的单元格，然后获取它的文件名值。
