Excel VBA Print or SaveAs Webpage as PDF

Question

Currently, I have a list of URL's in Column C of my Excel Worksheet. I have code that will loop through all the URLs and open them in Internet Explorer. Once it is loaded, it will open the next in the same browser, and so on.

What I'm missing is the Print the page as a PDF, or SaveAs PDF, before moving on to the next URL. The PDF file name would be the contents of the corresponding cell in Column B.

I'm new to printing webpages, so I have been trying a few different approaches. The approach is find most often is using the .ExecWB OLECMDID_PRINT command. The problem is, it never does anything for me. It just moved on to the next URL without saving or printing the webpage.

I've referenced many stackoverflow posts, here are a few to name:

VBA to click within Print Preview window in IE11

Automate IE print dialog box without using SendKeys

Bypass Printdialog in IE9

Here is my code:

Option Explicit

Const OLECMDID_PRINT = 6
Const OLECMDEXECOPT_PROMPTUSER = 1
Const OLECMDEXECOPT_DONTPROMPTUSER = 2
Const PRINT_WAITFORCOMPLETION = 2
Const OLECMDID_SAVEAS = 4

Sub OpenURL_Print()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = True

Dim wb As Workbook
Dim wsSheet As Worksheet, Rows As Long, links As Variant, IE As Object, link As Variant
Set wb = ThisWorkbook
Set wsSheet = wb.Sheets("Sheet1")

Set IE = New InternetExplorerMedium

Rows = wsSheet.Cells(wsSheet.Rows.Count, "C").End(xlUp).Row
links = wsSheet.Range("C1:C" & Rows)

With IE
    .Visible = True
    For Each link In links
        .Navigate (link)
        While .Busy Or .ReadyState <> 4: DoEvents: Wend

  IE.ExecWB OLECMDID_SAVEAS, OLECMDEXECOPT_DONTPROMPTUSER


    Next link
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Could anybody assist me with getting the page to Print as a PDF before moving on to the next URL? I'm not sure what I'm missing or did wrong in this code. I haven't even attempted to specify the save location or save name yet, since I was first trying to get it to print in any way.

Much appreciated!

Answer 1

Your code has a few different issues. The first thing you should do is get it properly looping through the range of cells (yours returns nothing.)

Option Explicit
Sub LoopURL()

    Dim ws As Worksheet, urls As Range, url, lastRow As Long, ie
    Set ws = Sheets("Sheet1")
    Set ie = New InternetExplorer
    With ie
        lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
        Set urls = ws.Range("C1:C" & lastRow)

        For Each url In urls.Cells

            Debug.Print "Loading: ", url

            ie.Navigate url
            Do While .Busy Or .readyState <> 4: DoEvents: Loop

            'print your page here...

            'wait for page to print...

        Next url
        .Quit
        Set ie = Nothing
    End With

End Sub

then you can add in the code needed for the actual printing, which you can find in several answers such as this one: Print Wesite to PDF using VBA.

Also I'm not sure why you think it's necessary to disable events, screenupdating and calculation... but generally all those kind of settings can be as-is - especially when troubleshooting an issue.