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?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Qqqqq

New Member
Joined
Feb 6, 2014
Messages
48
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,923
Office Version
  1. 365
Platform
  1. Windows
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?
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,853
.
Curious. Just tried your macro code here (without editing anything) and it works.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,923
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,853
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.
 

Qqqqq

New Member
Joined
Feb 6, 2014
Messages
48

ADVERTISEMENT

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.
 

Qqqqq

New Member
Joined
Feb 6, 2014
Messages
48
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.
 

Qqqqq

New Member
Joined
Feb 6, 2014
Messages
48
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?
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,853
.
If you don't need to proof read the newly created PDF ... change this line :

VBA Code:
OpenAfterPublish:=True

To FALSE
 

Watch MrExcel Video

Forum statistics

Threads
1,122,645
Messages
5,597,343
Members
414,138
Latest member
Ankitjainkaka

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
Top