VBA Save URL that Redirects to PDF

aquickremark

New Member
Joined
Jun 20, 2012
Messages
2
Hello,

We have a spreadsheet of URLs. These URLs redirect from one page to a specific PDF.

Does anybody have an idea of how to open the URL, let it redirect and then save the PDF?

The URLDownloadToFileA will save the original link only, and when I do SendKeys after waiting for the file to open, it still requires the user to click the 'Save' button.

We want to avoid having the user have any interaction.

Thanks!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

poolhall

Active Member
Joined
Jan 9, 2009
Messages
350
Generally speaking, you need to submit a GET request to your URL, get the server response, parse out a redirect link from the response, then submit a new request to follow this redirect link.
 

aquickremark

New Member
Joined
Jun 20, 2012
Messages
2
Generally speaking, you need to submit a GET request to your URL, get the server response, parse out a redirect link from the response, then submit a new request to follow this redirect link.

Do you have some sample code that I might be able to try out?
 

poolhall

Active Member
Joined
Jan 9, 2009
Messages
350
It could be the first step. You'll have to add a reference to Microsoft XML, v6.0 (VBE - Tools - References):
Code:
Sub GetRedirectLink()Dim xmlReq As ServerXMLHTTP60
Dim response As String


Set xmlReq = New ServerXMLHTTP60
xmlReq.Open "GET", "http://yourURL"
xmlReq.send


If xmlReq.Status <> 200 Then MsgBox xmlReq.StatusDescription: Exit Sub
response = xmlReq.responseText

debug.print response


End Sub

Then from the string 'response' you need to extract the redirect link.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,861
Members
414,408
Latest member
macroSmith

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
Top