VBA Print to pdf

[Qqqqq]

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.

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?

 

[Qqqqq]

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]

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]

.
Curious. Just tried your macro code here (without editing anything) and it works.

 

[Joe4]

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]

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 :

 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]

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]

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]

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:

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]

.
If you don't need to proof read the newly created PDF ... change this line :

OpenAfterPublish:=True

To FALSE