Having Trouble getting this code in VBA to work. Attempting to download a list of PDFs

Rayc266

New Member
Joined
Sep 11, 2024
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Sub DownloadPDFs()
Dim ws As Worksheet
Dim urlRange As Range
Dim cell As Range
Dim url As String
Dim fileName As String
Dim savePath As String
Dim http As Object
Dim fileStream As Object
Dim fileData As Variant
Dim response As Variant

' Set worksheet and range
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name if different
Set urlRange = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)

' Set save path
savePath = "C:\Users\g1cxr07\OneDrive - FR Banks\JamesPDFs\"

' Initialize HTTP and FileStream objects
Set http = CreateObject("MSXML2.XMLHTTP")
Set fileStream = CreateObject("ADODB.Stream")

' Loop through each URL
For Each cell In urlRange
If cell.Value <> "" Then
url = cell.Value
fileName = Mid(url, InStrRev(url, "/") + 1)

On Error GoTo ErrorHandler
' Send HTTP request
http.Open "GET", url, False
http.send
If http.Status = 200 Then
' Write response to file
fileStream.Type = 1 ' adTypeBinary
fileStream.Open
fileStream.Write http.responseBody
fileStream.SaveToFile savePath & fileName, 2 ' adSaveCreateOverWrite
fileStream.Close
cell.Offset(0, 1).Value = "Downloaded"
Else
cell.Offset(0, 1).Value = "Error"
End If
On Error GoTo 0
End If
Next cell

' Clean up
Set http = Nothing
Set fileStream = Nothing

MsgBox "Download complete!"

Exit Sub

ErrorHandler:
cell.Offset(0, 1).Value = "Error"
Resume Next
End Sub
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Forum statistics

Threads
1,224,241
Messages
6,177,370
Members
452,773
Latest member
D P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top