How to download PDF file from browser using Excel VBA
I am using the below code snippet to download a PDF file from a website.
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
Sub Test()
Dim strPDFLink As String
Dim strPDFFile As String
Dim Result As Boolean
strPDFLink = "myurl?SessionKey=rCpZeX9UP300002D50BA& docid=*8G0leLEfTTX3oX8QpVUmKqRoTj6zS6bzTWf9%29Dt1hij3ym9hKqucLhtOnWVeCgM0wyGJyjI9RNj3Kv&PageNo=1"
strPDFFile = "D:\Users\d828737\Desktop\Doc Comparison\Temp\abcd.pdf"
Result = DownloadFile(strPDFLink, strPDFFile)
End Sub
Function DownloadFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then DownloadFile = True
End Function
Below is the response i am getting from browser using code
<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Interview Enterprise Web Client</title>
</head>
<frameset name="ImageFrame" border="1" framespacing="0" topmargin="0" leftmargin="0" marginheight="0" marginwidth="0" rows="*,80">
<frame name="document" src="iv_web_client.iv_document?SessionKey=1aYT4sGK1200002D50C6&docid=*8G0SU4Fcf)xcWWX6e96)FGlOL4rOYYt0i3m)HlGth2F(W4RnxurPClkHvNBurOAsaeNfGlwBKzzTm5&outputname=&FirstPage=1&options=" scrolling="auto" border="0" frameborder="no" topmargin="0" leftmargin="0" marginheight="0" marginwidth="0" style="border-bottom:solid#000000 1px;" noresize="">
<frame name="control" src="iv_web_client.iv_doc_sel?SessionKey=1aYT4sGK1200002D50C6&docid=*8G0SU4Fcf)xcWWX6e96)FGlOL4rOYYt0i3m)HlGth2F(W4RnxurPClkHvNBurOAsaeNfGlwBKzzTm5&outputname=&pageno=1&options=" scrolling="auto" border="0" frameborder="no" topmargin="0" leftmargin="0" marginheight="0" marginwidth="0" style="border-bottom:solid#000000 1px;" noresize="">
</frameset>
<noframes>You need a frames capable browser to use this site.</noframes>
</html>
I also tried below method
Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
WHTTP.Open "GET", fileUrl, False
WHTTP.Send
FileData = WHTTP.ResponseBody
when I open the url given in the above code in the browser I see the pdf file open automatically. How do I download the same pdf file opened in my browser using code?
Can someone help me solve the problem.
+3
source to share
1 answer
I can think of a couple of ways to do this. If you want to loop over a bunch of links and download all the files, you can customize your inventory list in Excel as you see in the image below.
Then run the following macro.
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
Sub DownloadFilefromWeb()
Dim strSavePath As String
Dim URL As String, ext As String
Dim buf, ret As Long
URL = Worksheets("Sheet1").Range("A2").Value
buf = Split(URL, ".")
ext = buf(UBound(buf))
strSavePath = "C:\Users\rshuell\Desktop\Downloads\" & "DownloadedFile." & ext
ret = URLDownloadToFile(0, URL, strSavePath, 0, 0)
If ret = 0 Then
MsgBox "Download has been succeed!"
Else
MsgBox "Error"
End If
End Sub
Now if you just want to download one file, run the script below.
Sub DownloadFileWithVBA()
Dim myURL As String
'Right-click on the link named 'Sample Address File'
'Click 'Copy Link Location'
'Paste the link below
myURL = "http://databases.about.com/library/samples/address.xls"
Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.Send
myURL = WinHttpReq.ResponseBody
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write WinHttpReq.ResponseBody
oStream.SaveToFile ("C:\Users\Excel\Desktop\address.xls")
oStream.Close
End Sub
0
source to share