【excel高级技巧】----VBA批量下载excel表内链接中的图片

第一步：准备Excel数据

1. 在A列放置图片URL链接

2. 确保第一行是标题行，数据从第二行开始

3. 确保URL格式正确（以http://或https://开头）

第二步：设置VBA代码

1. 打开Excel，按 Alt + F11 进入VBA编辑器

2. 插入新模块（菜单：插入 → 模块）

3. 将下面的代码粘贴到模块中

   

Option Explicit

Private Declare PtrSafe 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

Sub DownloadImages()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim urlStr As String
    Dim savePath As String
    Dim folderPath As String
    Dim fileName As String
    Dim ret As Long
   
    '设置工作表，可改成 Sheet2 等
    Set ws = ActiveSheet
   
    '获取当前文件路径，创建保存文件夹
    folderPath = ThisWorkbook.Path & "\图片下载\"
    If Dir(folderPath, vbDirectory) = "" Then
        MkDir folderPath
    End If
   
    '获取A列最后有数据的行
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
   
    '遍历A列链接
    For i = 1 To lastRow
        urlStr = Trim(ws.Cells(i, "A").Value)
        If urlStr <> "" And (Left(urlStr, 4) = "http") Then
            '拼接保存文件名
            fileName = folderPath & "图片" & i & ".jpg"
            '下载图片
            ret = URLDownloadToFile(0, urlStr, fileName, 0, 0)
            'ret=0 代表下载成功
            If ret = 0 Then
                ws.Cells(i, "B").Value = "下载成功"
            Else
                ws.Cells(i, "B").Value = "下载失败"
            End If
        End If
    Next i
   
    MsgBox "批量下载完成！" & vbCrLf & "保存路径：" & folderPath, vbInformation
End Sub

第三步：运行代码

1. 在VBA编辑器中，将光标放在 DownloadImages 过程内

2. 按 F5 运行，或从Excel的宏对话框中运行

第四步：自定义参数（可选）

• 修改工作表名称：Set ws = ThisWorkbook.Sheets("Sheet1")

• 修改URL列：urlColumn = "A"

• 修改开始行：startRow = 2

• 修改保存路径：savePath = "C:\DownloadedImages\"