Solved

Save IE browser as .pdf from within Excel

Posted on 2015-02-18
12
111 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

705 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