Issue Printing to PDF using a Command Button and VBA

[PandaPlaza1277]

Hello,

I'm running into an issue printing a specific hidden spreadsheet to PDF using a Command Button. I've managed to make this code work in another workbook of mine and I've even had this code work a number of times in this spreadsheet (maybe 4 times or so earlier on). For some reason, it does not consistently work. I've spent a good amount of time searching for answers and verifying the code that seems to be in question, but as far as I can tell, the input is correct. The error seems to be occurring with the "OpenAfterPublish:=True" line of code. Does anyone have any ideas what could be going wrong?

 

[davesexcel]

My first thought is that you have the pdf open and trying to run the code again will result in error.

 

[PandaPlaza1277]

My first thought is that you have the pdf open and trying to run the code again will result in error.

Unfortunately, no. I don't have the PDF open at all. I've even tried closing out my PDF viewing program (Bluebeam) completely, just in case.

 

[PandaPlaza1277]

Try running the code again. This time, though, when the error occurs, enter the following line in the Immediate Window (Ctrl+G), and press ENTER...

? dir(pdfile, vbNormal)

If the path and filename are correct, it should return the filename. Does it return the filename?

I think I did this correctly. In the Immediate Window, I typed, "? dir(pdfile, vbNormal)". This brought up the error: Run-time '52': Bad file name or number.

 

[Domenic]

Check to make sure that path and filename does not contain more than the maximum characters allowed, which in newer versions is 218 characters, as per Excel specifications and limits.

Also check to make sure that the filename does not contain any illegal characters, such as the following...

\
/
<
>
?
[
]
:
|
*
"

 

[PandaPlaza1277]

Check to make sure that path and filename does not contain more than the maximum characters allowed, which in newer versions is 218 characters, as per Excel specifications and limits.

Also check to make sure that the path and filename does not contain any illegal characters, such as the following...

<
>
?
[
]
:
|
*
"

Well, the file path contains roughly 46 characters. The only special character in the path is "\" and the only special character in the filename is " - ", which from previous experience in a different workbook, didn't have any problems.

 

[Domenic]

When the error occurs, enter the following line in the Immediate Window...

? pdfile

What does it return?

 

[PandaPlaza1277]

When the error occurs, enter the following line in the Immediate Window...

? pdfile

What does it return?

Hm... It almost returns the correct output that I'm looking for. Almost. It is displaying (skipping a little bit): "...\Desktop\ - Summary Analysis - .pdf".
It looks like it's not putting in the "SumAn.Range("C9").Value" or the "SumAn.Range("C10").Value".

 

[Domenic]

It looks like it's not putting in the "SumAn.Range("C9").Value" or the "SumAn.Range("C10").Value".

Do those cells contain any values?

 

[PandaPlaza1277]

Do those cells contain any values?

Yes, they're supposed to contain the values "301600" currently, which is typed into cell C9 on the Summary Analysis page, and the second value is "8/4/22" currently, which is typed into cell C10 on the Summary Analysis page.