goldeempate1
08-19-2013, 12:41 PM
Hello,
I am trying to take the hyperlinks to PDFs stored in a variable number of rows in a certain column, download these PDFs, and place them in a folder on my desktop. I used the following code, but the file doesn't end up being placed in the folder. The intermediate window seems to indicate the file has been picked up, but it is not being written to the folder. Any suggestions would be greatly appreciated! :)
Sub imagestore()
Dim i As Long
Dim lastRow As Long
Dim beginningRow As Long
Dim FileNum As Long
Dim columnNum As Long
Dim FileData() As Byte
Dim filesLocation As String
Dim MyFile As String
Dim message As String
Dim WHTTP As Object
beginningRow = 55 'Row at which the PO data begins
lastRow = WorksheetFunction.CountA(Columns(17)) + beginningRow - 2 'Formula to find last row of PO data
filesLocation = "C:\Users\<username>\Desktop\<foldername>" 'Specifies where to put file
columnNum = 17 'Specifies column number to pull from
On Error Resume Next
Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
If Err.Number <> 0 Then
Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
End If
On Error GoTo 0
If Dir(filesLocation, vbDirectory) = Empty Then MkDir filesLocation
For i = beginningRow To lastRow 'Sets range from row variables
MyFile = Cells(i, columnNum).Text
TempFile = Right(MyFile, InStr(1, StrReverse(MyFile), "/") - 1)
WHTTP.Open "GET", MyFile, False
WHTTP.Send
FileData = WHTTP.ResponseBody
FileNum = FreeFile
Open filesLocation & TempFile For Binary Access Write As #FileNum
Put #FileNum, 1, FileData
Close #FileNum
Next
Set WHTTP = Nothing
message = "Files downloaded to " & filesLocation
MsgBox message 'Alerts user to location of download
End Sub
I am trying to take the hyperlinks to PDFs stored in a variable number of rows in a certain column, download these PDFs, and place them in a folder on my desktop. I used the following code, but the file doesn't end up being placed in the folder. The intermediate window seems to indicate the file has been picked up, but it is not being written to the folder. Any suggestions would be greatly appreciated! :)
Sub imagestore()
Dim i As Long
Dim lastRow As Long
Dim beginningRow As Long
Dim FileNum As Long
Dim columnNum As Long
Dim FileData() As Byte
Dim filesLocation As String
Dim MyFile As String
Dim message As String
Dim WHTTP As Object
beginningRow = 55 'Row at which the PO data begins
lastRow = WorksheetFunction.CountA(Columns(17)) + beginningRow - 2 'Formula to find last row of PO data
filesLocation = "C:\Users\<username>\Desktop\<foldername>" 'Specifies where to put file
columnNum = 17 'Specifies column number to pull from
On Error Resume Next
Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
If Err.Number <> 0 Then
Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
End If
On Error GoTo 0
If Dir(filesLocation, vbDirectory) = Empty Then MkDir filesLocation
For i = beginningRow To lastRow 'Sets range from row variables
MyFile = Cells(i, columnNum).Text
TempFile = Right(MyFile, InStr(1, StrReverse(MyFile), "/") - 1)
WHTTP.Open "GET", MyFile, False
WHTTP.Send
FileData = WHTTP.ResponseBody
FileNum = FreeFile
Open filesLocation & TempFile For Binary Access Write As #FileNum
Put #FileNum, 1, FileData
Close #FileNum
Next
Set WHTTP = Nothing
message = "Files downloaded to " & filesLocation
MsgBox message 'Alerts user to location of download
End Sub