（VBA或公式）从单元格打开超链接，并从新单元格值保存/重命名下载的文件 [英] (VBA or Formula) to open hyperlink from a cell and save/rename downloaded file from new cell value

问题描述

计划： Excel 2010

体验级别：

问题： br>
我有一个链接表，我想打开每个链接（它们是安全服务器上的.csv / .xlsx文件，我必须登录），然后保存一个文件名称由第3个行信息列（C）保持BatchID完整（如下）。

  | A | B | C | 
 |批量日期|链接|新名称| 
 | ------------ | -------------- | ---------- | 
 | 3/03/2014 |查看| 20140303 | 
 | 2/05/2014 |查看| 20140205 | 
 | 1/02/2014 |查看| 20140102 | 
 | 12/01/2013 |查看| 20131201 | 
 | 11/01/2013 |查看| 20131101 | 
 | 10/01/2013 |查看| 20131001 | 
 | 9/01/2013 |查看| 20130901 | 
 | 8/01/2013 |查看| 20130801 | 
  

链接可能弹出一个登录（没关系，我有凭证，但是如果使用VBA，更安全的硬代码，所以如果我想念一个弹出窗口，它不会停顿）。

列表是广泛的，每月更新。

原始的csv / .xlsx文件名类似：

1036548025_detailed_sales_report.xls 数字总是不同（因此是上面的新列），但是 _detailed_sales_report.xls 始终保持静态。我最理想的是要使用通配符保留文件名？所以就像：
savefilename =（A10）& *& _detailed_sales_report.xls

我已经从Google打开超级链接，但大部分是关于一般的超链接或打开链接的工作簿，而我想使用它来下载新的作品。

编辑

还需要延迟每一行，服务器臭名昭着。

编辑3
每个链接是一个新生成的链接，没有链接是一样的（如文件名）： Selection.Hyperlinks（1）.Follow NewWindow：= False，AddHistory：=（$）

  Range（C9 True 
 Workbooks.Open文件名：=http：//www.mylinkgoeshere.---/.php？pID = 12898
'pID =永远不同，它的动态报告系统
 
 ActiveWindow.Visible = False 
 Windows（view_payment_orders.php）。Visible = True 
 ActiveWorkbook.SaveAs文件名：=D：\location\20140205.xlsx _ 
 FileFormat：= xlOpenXMLWorkbook，CreateBackup：= False 
 ActiveWorkbook.Save 
 ActiveWindow.Close 
  

以上是当我运行宏录像机时会发生什么，我不想在超过100行的代码中进行编码:-)循环是否可以在两个不同的列之间工作？

请帮忙，我从哪里去？

解决方案

我无法模拟您打开的链接，但可以尝试以下方式：

  Sub test（）
 
 Dim hlink As Hyperlink 
 Dim wb As Workbook 
 Dim saveloc As String 
 
 saveloc =D：\location\
在ThisWorkbook.Sheets（NameOfYourSheet）中的每个hlink。超链接
设置wb = Workbooks.Open（hlink.Address ）
 wb.SaveAs saveloc&亲爱的.xlsx
 wb.Close True 
设置wb =没有
下一个
 
 End Sub 
  

我假设Excel可以直接使用地址打开链接中的文件。

上面的代码是循环遍历目标中的所有超链接表单。

hlink.Address 获取地址，然后在 Workbooks.Open 方法中使用它。

然后使用 hlink.Parent 作为文件名将其另存为 .xlsx 文件。

hlink.Parent 返回超链接的单词。

要完成另存为路径，我们初始化文件位置 saveloc 。

这是尝试和测试，但只有链接我创建了我的本地驱动器。

Edit1：使用超链接的相邻单元格值保存。

 对于每个hlink在ThisWorkbook.Sheets（NameOfYourSheet）超链接
设置w b = Workbooks.Open（hlink.Address）
 wb.SaveAs saveloc& hlink.Range.Offset（0,1）.Value& .xlsx
 wb.Close True 
设置wb =没有
下一个
  

hlink.Range 返回一个范围对象超链接的位置。

我们使用 Offset 属性获取相邻单元格，然后获取文件名的值。

Program: Excel 2010
Experience Level: Basic

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

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

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 always remains static.

I would ideally like to keep the filename by using a wildcard?? So something like: 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.

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

Edit 3 Each link is a newly generated one, 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:\location\20140205.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?

Please help, where do I go from here?!

解决方案

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.

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