Solved

Save IE browser as .pdf from within Excel

Posted on 2015-02-18
12
104 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
  • 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now