Saving Worksheet to PDF in Excel

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
martywalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

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

Regards
0
Julie ThomasCommented:
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
martywalAuthor Commented:
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
Julie ThomasCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
martywalAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.