Print to PDF from Excel with variable filename interpreted with Sendkeys

[akmdost]

I tried searching throughout but could not find a definite answer. Rather people have only suggested alternatives to similar questions but not a proper answer.
My excel version is 2010.
I need to print multiple pages from multiple excel files to a single PDF file, the code for which I have already prepared. The only issue I am facing is with the below section where I need to send the print to a saved PDF file. The code wont move forward unless I type the file name and click save.

The relevant code is below
<code> Sub printout()

ActiveSheet.printout ActivePrinter:="Adobe PDF on Ne00:", From:=1, To:=1

SendKeys (Variable & ".pdf")

SendKeys "{Enter}"

End Sub</code>what is happening here is the first line of the code opens up the "Save PDF File As" dialogue box. Thereafter I am unable to execute the Sendkeys method to enter the file name and click save/enter. Which is what I want to achieve through the VB macro code and the same is my question here?
I also tried the below
<code> ActiveSheet.printout ActivePrinter:="Adobe PDF Son Ne00:", From:=1, To:=1, PrintToFile:=True, PrToFileName:="Path\PDFs\1.pdf"</code>but this gives me a Post Script error
"When you create a PostScript file you must rely on system fonts and use document fonts. Please go to the printer properties, Adobe PDF settings page and turn OFF the option Rely on system fonts only; do not use document fonts."

if I change the PDF settings as per the error message the file does save but it saves a corrupt file which is blank when I open the saved PDF file.

I also tried the exporttoPDF method but it gives me other different problems one of them being with respect to paper size which is a different question altogether and would not include it here. I tried various techniques to solve the paper size issue but does not solve my problem. just including the reference so that no one suggests the exporttoPDF alternative.

I need to resolve the issue with the .printout method only moreover now it has become an understanding issue wherein I have to understand how to resolve it.

In the save as dialogue box when my above code runs, If I enter the file name manually and click save then everything works perfectly. It is only the sendkeys part I am unable to figure out to automate the entering of filename and clicking save. Need the solution only using the .PRINTOUT

please let me know for any additional details.

 

[Anthony47]

Try adding a wait before starting Sendkeys:

ActiveSheet.printout ActivePrinter:="Adobe PDF on Ne00:", From:=1, To:=1
Application.Wait Now + TimeValue("00:00:03")       '<<< ADD
SendKeys (Variable & ".pdf", True)       '<<< MODIF
SendKeys "{Enter}"

Keep in mind that this code cannot be executed step by step /debug mode

Bye

 

[akmdost]

Try adding a wait before starting Sendkeys:

ActiveSheet.printout ActivePrinter:="Adobe PDF on Ne00:", From:=1, To:=1
Application.Wait Now + TimeValue("00:00:03")       '<<< ADD
SendKeys (Variable & ".pdf", True)       '<<< MODIF
SendKeys "{Enter}"

Keep in mind that this code cannot be executed step by step /debug mode

Bye

already tried that does not work

 

[Anthony47]

In my opinion the SaveAs Window takes a few seconds to show (print process is not immediate), so I suggest you first try increasing TimeValue to 5-10 secs.

In a similar circumstance I identified the readiness of the "Save as" popup window using this code:

    TOut = Now + TimeValue("00:00:10")             'Max waiting
'Wait for a window headed "Salva con nome":
    Do
        myWait (0.5)
          Hwnd = FindWindow(vbNullString, "Salva con nome")      '<<The heading of YOUR Save window
        DoEvents
    Loop Until Hwnd Or Now > TOut
    Debug.Print "   Save As window Handle: "; Hex(Hwnd)

    If Hwnd > 0 Then        'Found!
        myWait (0.5)
            Application.SendKeys (DirDocs & Application.PathSeparator & myPDF & ""), True    '<<< YOUR string
            myWait (0.5)
            Application.SendKeys "{ENTER}", True
    Else
        Msgbox("No Save As After TimeOut")
        'What to do if the window don't show up
        '
    End If

You have to declare this library, on top of the vba module:

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

The code uses a "myWait" function that corresponds to this code:

Sub myWait(ByVal myStab As Single)
Dim myStTim As Single
'
    myStTim = Timer
    Do          'wait myStab
        DoEvents
        If Timer > myStTim + myStab Or Timer < myStTim Then Exit Do
    Loop
End Sub

Is it clear that Sendkeys requires that the target window be the active one, and thus you cannot step trough the code? This is more true with the additional code that I listed.

Bye