从 Excel/VBA 中的链接自动下载图片的方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22185853/
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
What is a way to automate downloading a picture from a link in Excel/VBA?
提问by Adam.ACMT
So here's the situation: I'm trying to download a number of pictures from an external server onto my local computer.
所以情况是这样的：我试图从外部服务器下载一些图片到我的本地计算机上。
The Excel file has a link to the picture which will open and download the picture.
Excel 文件有一个指向图片的链接，该链接将打开并下载图片。
What I've tried so far is to convert the hyperlinks into just text (of the picture url) and run the following code.
到目前为止，我所尝试的是将超链接转换为文本（图片网址）并运行以下代码。
I'm only basically familiar with VBA, more so with other languages though. Here is the code I have so far:
我只基本熟悉 VBA，但对其他语言更熟悉。这是我到目前为止的代码：
Option Explicit
Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Dim Ret As Long
'~~> This is where the images will be saved. Change as applicable
Const FolderName As String = "C:\Users\My Name\Downloads\"
Sub DownloadLinks()
Dim ws As Worksheet
Dim LastRow As Long, i As Long
Dim strPath As String
'~~> Name of the sheet which has the list
Set ws = Sheets("Sheet1")
LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow '<~~ 2 because row 1 has headers
strPath = FolderName & ws.Range("BP" & i).Value & ".jpg"
Ret = URLDownloadToFile(0, ws.Range("BP" & i).Value, strPath, 0, 0)
If Ret = 0 Then
ws.Range("CA" & i).Value = "File successfully downloaded"
Else
ws.Range("CA" & i).Value = "Unable to download the file"
End If
Next i
End Sub
The column names are irrelevant, but right now, everything comes out as "Unable to download file" or if it is successful, it isn't in the directory I specified.
列名无关紧要，但现在，一切都显示为“无法下载文件”，或者如果下载成功，则它不在我指定的目录中。
Is there a better way to code this?
有没有更好的方法来编码？
Something about my data maybe?
我的数据可能有什么问题？
I'd also like it to save the file name as text in another column if possible, but that isn't necessary.
如果可能，我还希望将文件名保存为另一列中的文本，但这不是必需的。
Right now I just need them to get downloaded.
现在我只需要它们来下载。
采纳答案by Tim Williams
Try this:
尝试这个：
Sub DownloadLinks()
Dim ws As Worksheet
Dim LastRow As Long, i As Long
Dim strPath As String, strURL As String
Dim c As Range
Set ws = Sheets("Sheet1")
LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
Set c = ws.Range("BP" & i)
If c.Hyperlinks.Count>0 Then
strPath = FolderName & c.Value & ".jpg"
strURL = c.Hyperlinks(1).Address
Ret = URLDownloadToFile(0, strURL, strPath, 0, 0)
ws.Range("CA" & i).Value = IIf(Ret = 0, _
"File successfully downloaded", _
"Unable to download the file")
Else
ws.Range("CA" & i).Value = "No hyperlink!"
End If
Next i
End Sub
