Solved

Save IE browser as .pdf from within Excel

Posted on 2015-02-18
12
110 Views
Last Modified: 2016-02-10
I have code that looks up a runcard number and opens it in an IE browser - what I'm having difficulty doing is to save off the IE browser as a .pdf file format. Using EXCEL2013 in Windows 7.

Sub CreatePDFs()        'This works for saving an actual file
Dim TheFile As String   ' report server file location
Dim TheExpt As String   ' what to name the file
Dim SaveMe As String    ' export file info
Dim EID As String       ' work request number
Dim iCnt As Integer     ' position counter
Dim rL2 As String       ' runcard left2 characters
Dim rR2 As String       ' runcard right 2/3 characters depends on runcard name length
Dim rValue As String    ' combine rL2 & rR2
Dim DocTitle As String  ' output file name
Dim WS As Integer       ' runcard wafer size

Dim Explorer As Object
Dim AIEC As Object

Set Explorer = CreateObject("InternetExplorer.Application")

Sheets("LUP").Select
Range("E2").Select
EID = ActiveCell.Value
Range("F2").Select
WS = ActiveCell.Value
Range("A2").Select
iCnt = 2 ' set cell counter
Stop
Do While ActiveCell <> ""   ' runcard could contain 4 or 5 characters
    rValue = ActiveCell.Value
    If Len(rValue) = 4 Then
        rL2 = Left(rValue, 2)
        rR2 = Right(rValue, Len(rValue) - 2)
    Else
        rL2 = Left(rValue, 2)
        rR2 = Right(rValue, Len(rValue) - 3)
    End If
    DocTitle = "ECRC# " & EID & " " & rL2 & "-" & rR2 & ".pdf"
    TheFile = "http://reports/ReportServer_RPTSRV?/Interactive/FmsReports/RECIPE_RomRecipeIdByRuncard&Wafersize=" & WS & "&Runcard=610166" & rValue
    ActiveWorkbook.FollowHyperlink Address:=TheFile, NewWindow:=True
   
' the vba code works file to here
' Opens the request file from the report server but not able to save the IE browser as a pdf file
' ' ' ' ' ' '
   
    SaveMe = "C:\Temp\" & DocTitle
    iCnt = iCnt + 1
    rValue = "A" & iCnt
    Range(rValue).Select
Loop

End Sub



vba-code-sent-to-Experts-Exchange.txt
0
Comment
Question by:KirbyDerby
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 12

Expert Comment

by:FarWest
ID: 40618975
please note that you did not use the Explorer object to open the link and use internal Excel function instead, so you don't have any control over it
check this to call the link using Explorer Object
http://www.informit.com/articles/article.aspx?p=1170490&seqNum=7
for pdf generating you can use PDF printer and use
Explorer.ExecWB 6, 2
//
there is alternatives that to get html contents and to have is as html file
check this
http://stackoverflow.com/questions/12989687/fetch-website-data-into-excel-using-vba-excel-macro
0
 
LVL 1

Author Comment

by:KirbyDerby
ID: 40621171
Sorry if I worded the request poorly and notices that I didn't even use CreateObject very cleanly but as I look at the reply I'm just not seeing it. The problem still exists as quoted:
what I'm having difficulty doing is to save off the IE browser tab as a .pdf file format. Using EXCEL2013 in Windows 7
.

I can open each "runcard" in a separate tab - I'm wondering if someone can help with saving each tab off as a .pdf file?

First time requesting help - this is urgent and I really could use the help - thank you
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40621304
No problem. Do you want to do this in VBA or manullay
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:KirbyDerby
ID: 40621311
I'm trying to do this in VBA.
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40621313
Does the report page contains an option to save as PDF?
0
 
LVL 1

Author Comment

by:KirbyDerby
ID: 40621331
Yes
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40621345
Please Check if it is possible to call save sa using URL within query  string
0
 
LVL 1

Author Comment

by:KirbyDerby
ID: 40621372
Yes - SaveAs allowed the Excel file currently running the VBA code to be saved using an URL for the location.
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40621397
I meant the web page report that shows the card. Isn't that what you want to save sa PDF?
0
 
LVL 1

Author Comment

by:KirbyDerby
ID: 40621402
Clarification - I've attached an image showing two different places where I can do a SaveAs from - one works the other doesn't.
SaveAs-image.jpg
0
 
LVL 12

Accepted Solution

by:
FarWest earned 500 total points
ID: 40621444
It seams that your using SQL reporting
Check this URL to generate PDF from query string
http://ryanfarley.com/blog/archive/2006/01/27/15689.aspx
0
 
LVL 1

Author Closing Comment

by:KirbyDerby
ID: 40621878
Thank you so much for your help - the rest should come together fairly quickly.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question