How to Save PDF opened in an IE page thru VBA

[sugaprasad]

Hi Everyone,

I am creating a VBA macro. The purpose of this macro is to open a webpage and click on hyperlink and save the newly opened webpage in a separate window (it opens in PDF format) to a particular folder. My code is able to open a webpage and click on the hyperlink. But am stuck up with saving the newly opened PDF webpage. Here is my code.

Sub Generate_PDF()
Dim objIE As SHDocVw.InternetExplorer
Dim OrgBox As HTMLInputElement
Dim objButton As HTMLButtonElement
Dim objLink As HTMLLinkElement


Dim PDF As Object
Dim Path As String




 
    Set objIE = New SHDocVw.InternetExplorer
    objIE.navigate "http://XXXXXXXXXXXXXXXXXXXXXXXXXXXX"
    objIE.Visible = True
   


    Do While objIE.readyState < 4: Loop
 
    Set OrgBox = objIE.document.getElementById("dDocName")
    OrgBox.Value = "2021345"
    
    
    Set objButton = objIE.document.getElementById("miniSearch")
    objButton.Click
    
    Application.Wait (Now + TimeValue("0:00:05"))
    
     Set objLink = objIE.document.getElementById("searchResultCompositeIcon")
     objLink.Click
     
  Application.Wait (Now + TimeValue("0:00:30"))
  
    Path = "C:\Documents and Settings\INTEL\Desktop\SP"
    
    PDF.ExecWB OLECMDID_SAVEAS, OLECMDEXECOPT_DODEFAULT, Path  ' geting object variable or with block variable not set error in this line


End Sub

please help me in saving the PDF document to a folder. is there a wa to do this.?

 

[RickXL]

please help me in saving the PDF document to a folder. is there a wa to do this.?

First, I cannot test the searching facilities in your code.

However, there are a couple of errors in the coding. The PDF.ExecWB should be objIE.ExecWB and I think the path needs to include the filename.
Even with that corrected you cannot seem to avoid the appearance of the SaveAs dialog.

Can you adapt your code to something like this:

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
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
Sub test()
    DownloadFile "[URL]http://www.see.ed.ac.uk/~shs/Climate%20change/Climate%20model%20results/over%20estimate.pdf[/URL]", "C:\over estimate.pdf"
    
End Sub

NB I don't think it works as well for html pages because it copies only text not all the pictures etc.

I copied the code from: http://www.mrexcel.com/forum/excel-...c-applications-saving-web-page-locally-2.html

 

[Venkat22]

Hi There,
I am also looking for the same kind of case....did yo get any solution for this?

Thanks
Venkat.

 

[sugaprasad]

Its been more than an year since I worked on this. I did some digging and found this code. It was working for me at that point. Give it a try.

Dim Shell As Object
Dim AcroPDF As SHDocVw.InternetExplorer
Set Shell = CreateObject("Shell.Application")
Application.Wait Now + TimeValue("00:00:02")
For Each AcroPDF In Shell.Windows
If TypeName(AcroPDF.document) = "AcroPDF" Then
AcroPDF.Visible = True




Store = AcroPDF.LocationURL
Call DownloadFile(Store, pdfname)
   


End If
Next






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

Good luck

 

[Venkat22]

Great Sugaprasad!!!
Its worked with the above logic..I was stuck at passing dynamic url, now figured out by above code. Thanks a lot.

Thanks

 

[mnbvcx]

I'm trying to get this to work.

The following code saves a correctly names PDF file in the correct folder. But on open the contents of the file shows the following Windows error message. Do I need to declare a Shell object and use the "For Each AcroPDF In Shell.Windows" code? Do I need to include a Adobe Reference?

Error message

Adobe Reader could not open [my_file.pdf] because it is either not a supported file type or because the file has been damaged (for example, it was sent as an email attachment and wasnt correctly decoded).

Code

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

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

Sub DownloadPDFs()
Dim StartRowNum As Long
Dim EndRowNum As Long
Dim pdfname As String
Dim strPath As String
Dim RecordNum As String
Application.ScreenUpdating = False
URLprefix = "http://[...]/Default.ASP?WCI=wciShowResults&WCE="
strPath = "P:\[...]"
StartRowNum = InputBox("Enter Start Row Number.", "Enter integer", 2)
EndRowNum = InputBox("Enter End Row Number.", "Enter integer")
For RowNum = StartRowNum To EndRowNum
RecordNum = Sheets("Sheet2").Cells(RowNum, 2)
pdfname = strPath & Sheets("Sheet2").Cells(RowNum, 1) & ".pdf"
URL = URLprefix & RecordNum
DownloadFile URL, pdfname
Next RowNum
Application.ScreenUpdating = True
End Sub