?
Solved

Saving Worksheet to PDF in Excel

Posted on 2014-08-19
5
Medium Priority
?
231 Views
Last Modified: 2014-09-30
Hi Experts,

I'm having an issue with trying to save worksheets as PDF's in Excel. My Script looks like this:

        Sheets("ORF - Process Summary").Visible = True
        Sheets("ORF - Process Summary").Select
        Sheets("ORF - Process Summary").Copy
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "\\svrau570bsm01\" & Range("ORFraiseLAN").Value & "$\My Documents\Classification Tool\" & Range("CURRENTorf").Value & " Process TEST.pdf" _
            , Quality:=xlQualityStandard, IncludeDocProperties:=False, _
            IgnorePrintAreas:=False, OpenAfterPublish:=True
        ActiveWindow.Close

I think that just having selected the sheet should be sufficient before running the export. I'm currently getting a runtime error '1004': Method 'Range' of object '_Worksheet' Failed

(the code is attached to a button on the worksheet I'm trying to save as a pdf).

Ultimately I want to save a number of worksheets as PDF's

Any help appreciated
0
Comment
Question by:martywal
  • 2
  • 2
5 Comments
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 40271906
Hi,

Are your named ranges correct and at this stage of the code available?

Regards
0
 
LVL 2

Expert Comment

by:Julie Thomas
ID: 40274120
You can use this code to verify that your ranges are "available":

Function isNameRngExist(myRng As String) As Boolean
    On Error Resume Next
    isNameRngExist = Len(ThisWorkbook.Names(TheName).Name) <> 0
End Function

If not, you may need to fully qualify them.
0
 

Author Comment

by:martywal
ID: 40282471
Hi guys,

Sorry not been on this for the last few days.
Thanks for your input!

In terms the function, if I just add that to a module that should work when the error pops up, right?

I'm wondering if stopping the script from opening the PDF may fix the issue.
0
 
LVL 2

Accepted Solution

by:
Julie Thomas earned 2000 total points
ID: 40283978
Martywal,

It doesn't sound like the problem is with opening the PDF, but with your code assembling the string variable for the name of the PDF file.

You should use the function to check if your named ranges exist. Put the function in a module and call it with the names of your ranges as arguments and assign the return code to a variable.

Dim NameCheck as Boolean

NameCheck = isNameRngExist("ORFraiseLAN")

If NameCheck = False Then Msgbox("A named range doesn't exist.")

Do the same for "CURRENTorf".

If you get that message box, you'll know that your code can't see your named ranges.

If you fully qualify the named ranges, your code would look like this:

        Sheets("ORF - Process Summary").Visible = True
        Sheets("ORF - Process Summary").Select
        Sheets("ORF - Process Summary").Copy
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "\\svrau570bsm01\" & Sheets("ORF - Process Summary").Range("ORFraiseLAN").Value & "$\My Documents\Classification Tool\" & Sheets("ORF - Process Summary").Range("CURRENTorf").Value & " Process TEST.pdf" _
            , Quality:=xlQualityStandard, IncludeDocProperties:=False, _
            IgnorePrintAreas:=False, OpenAfterPublish:=True
        ActiveWindow.Close

Alternatively, you can build the complete patch and filename into a string variable so you can see what file your code is about to attempt to create.

Dim strPDFname as String

strPDFname = "\\svrau570bsm01\" & Range("ORFraiseLAN").Value & "$\My Documents\Classification Tool\" & Range("CURRENTorf").Value & " Process TEST.pdf"

Look at the variable strPDFname in a message box or in the watch window to make sure your code is building the file name correctly.
0
 

Author Comment

by:martywal
ID: 40289419
Hi experts.
Sorry again for the lack of traction on this.
Indeed it looks like I'd stepped out of where I needed to be to save the pdf.
Appears fixed now.
I'll close in next day or two
Thanks for your help.
Martywal
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

807 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