从url下载图片并保存在单元格命名的文件夹中 [英] Download pictures from url and save in a folder named by a cell

问题描述

我有一个包含文件夹名称，图像名称和网址的工作表。我想将每个图像下载到特定文件夹，以便结果如下所示：

 文件夹名称图像名称URL 
 ----------- ------------ ------------------------- ---------- 
 folder1 image1 http://www.example.com/example1.jpg 
 folder2 image2 http://www.example.com/example2.jpg 
 folder3 image3 http://www.example.com/example3.jpg 
 folder4 image4 http://www.example.com/example4.jpg 
 folder5 image5 http：//www.example。 com / example5.jpg 
 
 C：\images\folder1\image1.jpg 
 C：\images\folder2\image2.jpg 
 C：\\ \\images\folder3\image3.jpg 
 C：\images\folder4\image4.jpg 
 C：\images\folder5\image5.jpg 
  

我发现了这个VBA代码，它的作用就像一个魅力，但是我不知道如何添加一个方法来创建文件夹如果不存在：

  Option Explicit 
 
私有声明函数URLDownloadToFile Liburlmon_ 
别名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 
 
 ~~>这是保存图像的地方。根据需要更改
 Const FolderName As String =C：\Temp\
 
子样本（）
 Dim ws As Worksheet 
 Dim LastRow As Long，我As Long 
 Dim strPath As String 
 
'~~>列表的名称
设置ws = Sheets（Sheet1）
 
 LastRow = ws.Range（A& Rows.Count）.End（xlUp）。行
 
对于i = 2 To LastRow'<〜〜2，因为行1有标题
 strPath = FolderName& ws.Range（A& i）.Value& .jpg
 
 Ret = URLDownloadToFile（0，ws.Range（B& i）.Value，strPath，0，0）
 
如果Ret = 0然后
 ws.Range（C& i）.Value =文件已成功下载
 Else 
 ws.Range（C& i）.Value =无法下载文件
 End If 
 Next i 
 End Sub 
  

解决方案

以下将确定文件夹是否存在，如果不存在，则创建它：

 如果Len（Dir（FolderName，vbDirectory））= 0然后
 MkDir FolderName 
如果
  

从此处修改

对于任何更高级的，我建议使用FileSystemObject类。

I have a worksheet with folder names, image names and urls. I want to download each image to the specific folder so that the outcome would look like this:

Folder Name   Image Name     URL
-----------   ------------   -----------------------------------
folder1      image1         http://www.example.com/example1.jpg
folder2      image2         http://www.example.com/example2.jpg
folder3      image3         http://www.example.com/example3.jpg
folder4      image4         http://www.example.com/example4.jpg
folder5      image5         http://www.example.com/example5.jpg

C:\images\folder1\image1.jpg
C:\images\folder2\image2.jpg
C:\images\folder3\image3.jpg
C:\images\folder4\image4.jpg
C:\images\folder5\image5.jpg

I've found this VBA code and it works like a charm, but I don't know how to add a method to creat folders if they don't exist:

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:\Temp\"

Sub Sample()
    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("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow '<~~ 2 because row 1 has headers
        strPath = FolderName & ws.Range("A" & i).Value & ".jpg"

        Ret = URLDownloadToFile(0, ws.Range("B" & i).Value, strPath, 0, 0)

        If Ret = 0 Then
            ws.Range("C" & i).Value = "File successfully downloaded"
        Else
            ws.Range("C" & i).Value = "Unable to download the file"
        End If
    Next i
End Sub

解决方案

The following will determine if a folder exists, and if not, create it:

If Len(Dir(FolderName, vbDirectory)) = 0 Then
   MkDir FolderName
End If

Modified from here

For anything more advanced, I recommend using the FileSystemObject classes.

这篇关于从url下载图片并保存在单元格命名的文件夹中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！