+ Reply to Thread
Results 1 to 3 of 3

macro to download file into unique folder based on cell value

  1. #1
    Registered User
    Join Date
    04-11-2014
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2003
    Posts
    1

    macro to download file into unique folder based on cell value

    I have a file with:

    column A = name
    column B = URL (ending in "pdf" or "jpg")
    column C = extension from column B so either ".pdf" or ".jpg"

    For each URL, the macro downloads the file and renames it with the values of column A + column B. However, the problem is that I want the files to be placed into a folder that has the same name as column A (these folders already exist). I can't figure out how to correct the file location listed below as "F:\test\folderpath" within the URLDownloadToFile function. In other words, it's easy to get the files to all download into a folder, but I want each to go into the custom folder I've built for each value in column A. I realize my use of "folderpath" in the location path is wrong so need help there. Any help is greatly appreciated. Thanks!


    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


    Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Dim strPath As String
    Dim folderpath As String

    Set ws = Sheets("form-data")
    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow '
    strPath = ws.Range("A" & i).Value & ws.Range("C" & i).Value
    folderpath = ws.Range("A" & i).Value

    Ret = URLDownloadToFile(0, ws.Range("B" & i).Value, "F:\test\folderpath" & strPath, 0, 0)

    Next i
    End Sub

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,257

    Re: macro to download file into unique folder based on cell value

    Try changing
    Please Login or Register  to view this content.
    to this, assuming the value in column A has the drive and folder path
    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,257

    Re: macro to download file into unique folder based on cell value

    Quote Originally Posted by Bernie Deitrick View Post
    Try changing
    Please Login or Register  to view this content.
    to this, assuming the value in column A has the drive and folder path
    Please Login or Register  to view this content.
    though you really could just use

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to Download csv file from a dynamic date based url to a specified file location
    By srimani202002 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-23-2014, 07:17 AM
  2. [SOLVED] Macro to Save File to Location Folder Based on Date in Cell
    By John_Day83 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-03-2013, 05:34 PM
  3. Macro to move file to another folder based on the names
    By kriahnadas.oo7 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-17-2013, 09:55 AM
  4. Macro to save duplicate file and create folder name based on cell value
    By munkee in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-19-2012, 12:09 PM
  5. Replies: 3
    Last Post: 09-30-2011, 04:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1