Solved

Save IE browser as .pdf from within Excel

Posted on 2015-02-18
12
105 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
highlight duplicate entry 16 27
Office 2016 Excel Issue 4 25
how to address a subform in a form, from inside another subform 2 22
Filling Blank Cells 14 15
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

919 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

13 Experts available now in Live!

Get 1:1 Help Now