VBA Print to pdf

Qqqqq

New Member
Joined
Feb 6, 2014
Messages
48
I have a macro that prints a single worksheet to a pdf file, which I've used successfully many times. I moved this code into a new workbook, and it doesn't work. When I get to the ActiveSheet.ExportAsFixedFormat portion of the code, I get a Run-time error '5': Invalid procedure call or argument error.

VBA Code:
Sub Print_pdf

    Dim xWB As Workbook
    Dim xFSO As Object
    Dim xFileName As String

    Set xWB = ActiveWorkbook
    Set xFSO = CreateObject("Scripting.FileSystemObject")
    xFileName = xWB.Path & "\" & xFSO.GetBaseName(xWB.Name) & ".pdf"

    xWB.Sheets("Sheet1").Select

    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=xFileName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

End Sub

What can I do to make this code work in my new workbook?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Is it possible for the file path to be too long? I renamed a folder within the file path (shorter name), and now the code works perfectly.
 
Upvote 0
Is it possible for the file path to be too long?
Could be. I seem to think it may be capped at 255.
What was the length when it wasn't working?
What is the length now that it is working?
 
Upvote 0
.
Curious. Just tried your macro code here (without editing anything) and it works.
 
Upvote 0
Curious. Just tried your macro code here (without editing anything) and it works.
How do you know what their file path and file name is? They haven't listed that anywhere in this thread.

Quote from post 2:
Is it possible for the file path to be too long? I renamed a folder within the file path (shorter name), and now the code works perfectly.
 
Upvote 0
If the OP has left out a portion of the macro code, it should be posted for review.

I stand by my first statement. The macro code as posted works here.

The OP stated :

VBA Code:
 When I get to the ActiveSheet.ExportAsFixedFormat portion of the code, I get a Run-time error '5': Invalid procedure call or argument error.

He did not indicate anything else.
 
Upvote 0
Could be. I seem to think it may be capped at 255.
What was the length when it wasn't working?
What is the length now that it is working?

Joe, the original path was 237 characters. The new path is 199 characters.
 
Upvote 0
If the OP has left out a portion of the macro code, it should be posted for review.

That is the full code, Logit. Nothing was left out. The code defines the file path to be the same as the active workbook. The expected result is a pdf file with the same name and in the same location as the original Excel workbook.
 
Upvote 0
In my new workbook, I actually need to print more than one worksheet to a single pdf file, so I changed up my code to the following:

VBA Code:
Sub Print_pdf

    Dim xWB As Workbook
    Dim xFSO As Object
    Dim xFileName As String
    Dim xPrint As Variant

    Set xWB = ActiveWorkbook
    Set xFSO = CreateObject("Scripting.FileSystemObject")
    xFileName = xWB.Path & "\" & xFSO.GetBaseName(xWB.Name) & ".pdf"
    xPrint = Array("Sheet1", "Sheet2")

    xWB.Sheets(xPrint).Select

    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=xFileName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

End Sub

When I ran the code, it produced a "Run-time error '1004': Application-defined or object-defined" error when got to the ActiveSheet.ExportAsFixedFormat portion of the code. I realized that was because I had an old copy of the pdf file open. Closing the pdf file allowed the code to run perfectly, over-writing the previous file (which is what I want it to do).

Does anyone know how to make the macro close a pdf file so that it can be over-written?
 
Upvote 0
.
If you don't need to proof read the newly created PDF ... change this line :

VBA Code:
OpenAfterPublish:=True

To FALSE
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top