How can i print multiple workbooks with same printer preference?

[paperfolder]

I have the following code which opens all xls file in a folder. Originally I was using printout, however I noticed that if I changed something in printer properties using CTRL+P, the settings did not apply to any of the sheets after page 1. I'm specifically trying to use the three hole punch on left. I tried to send the excel CTRL+P and enter, however that doesn't seem to actually send CTRL+P to the excel application.. it seems to be sending it to my VBA editor? If I can send CTRL+P and enter like a human would in the excel somehow via VBA, I think it would work as I want. I also tried using Application.Dialogs(xlDialogPrint).Show and it also fails to after Page 1.

I cannot change the default printer properties since it can only be changed by the admin.

TLDR - how do I actually send CTRL+P and Enter to excel?

Any help is much appreciated!

Public Sub Print_All()

    Dim fldr As FileDialog
    Dim myFolder As String
    Dim myFile As String
    Dim myFiles() As String
    Dim imax As Integer
    
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show <> -1 Then GoTo NextCode
        myFolder = .SelectedItems(1)
    End With
    
NextCode:
    PrintAll = myFolder
    Set fldr = Nothing
    
    Debug.Print myFolder
    
    myFile = Dir(myFolder & "\*.xlsx")
    
    Dim i As Integer
    
    'aquire name
    i = 0
    Do While myFile <> ""
        If i = 0 Then
            ReDim myFiles(i)
        Else
            ReDim Preserve myFiles(i)
        End If
        
        myFiles(i) = myFile
        
        Debug.Print myFiles(i)
        i = i + 1
        myFile = Dir
    Loop
    
    Debug.Print "sorted"
    
    'sorts array
    QuickSort myFiles(), LBound(myFiles), UBound(myFiles)
    
    imax = i
    
    ' adds additional information
    For i = LBound(myFiles) To UBound(myFiles)
        j = j + 1
        Debug.Print myFiles(i)
        Workbooks.Open Filename:=myFolder & "\" & myFiles(i)
        Worksheets("Print").Activate
        ActiveSheet.PageSetup.LeftHeader = "Page " & CStr(j) & " of " & CStr(imax)    'edit header of sheet
        'Application.Dialogs(xlDialogPrint).Show
        Application.SendKeys ("^p")
        Application.SendKeys ("~")
        ActiveWorkbook.Save 'save sheet
        ActiveWorkbook.Close    'close sheet
    Next i

End Sub


Private Sub QuickSort(strArray() As String, intBottom As Integer, intTop As Integer)
    Dim strPivot As String, strTemp As String
    Dim intBottomTemp As Integer, intTopTemp As Integer
    
    intBottomTemp = intBottom
    intTopTemp = intTop
    
    strPivot = strArray((intBottom + intTop) \ 2)
    
    While (intBottomTemp <= intTopTemp)
    
        '  comparison of the values is a descending sort
        While (strArray(intBottomTemp) < strPivot And intBottomTemp < intTop)
            intBottomTemp = intBottomTemp + 1
        Wend
        
        While (strPivot < strArray(intTopTemp) And intTopTemp > intBottom)
            intTopTemp = intTopTemp - 1
        Wend
        If intBottomTemp < intTopTemp Then
            strTemp = strArray(intBottomTemp)
            strArray(intBottomTemp) = strArray(intTopTemp)
            strArray(intTopTemp) = strTemp
        End If
        
        If intBottomTemp <= intTopTemp Then
            intBottomTemp = intBottomTemp + 1
            intTopTemp = intTopTemp - 1
        End If
    
    Wend
    
    'the function calls itself until everything is in good order
    If (intBottom < intTopTemp) Then QuickSort strArray, intBottom, intTopTemp
    If (intBottomTemp < intTop) Then QuickSort strArray, intBottomTemp, intTop

End Sub

 

[bugmonsta]

If you want to send Ctrl-P then use the caret and P:

Application.SendKeys ("^p")

to send enter use the tilde:

Application.SendKeys ("~")

these will work together so your code will simply be:

Sub printy2()
Application.SendKeys ("^p")
Application.SendKeys ("~")
End Sub

for more sendkey commands you can view them at them at MS MSDN page: https://msdn.microsoft.com/en-us/library/office/ff821075.aspx

And whilst I think about it - you can show the print dialog box (not what you wanted but I'm adding it here in case anyone is searching for it in the future ) with -

Application.Dialogs(xlDialogPrint).Show

 

[paperfolder]

If you read carefully, and look at my code, I said that I've tried all of the commands above.

 

[paperfolder]

ping

 

[bugmonsta]

hello,

As you put the

TLDR - how do I actually send CTRL+P and Enter to excel?

I didn't read the rest of your comment, sorry about that.

I just couldn't get my head around why you are printing the module and not the sheets... so I have replicated it with a basic test - mine below will only process one file and then send the ctrl-p.

IF I run this code from within VBA I get the module printed! If I run it from the macro window it works! Whilst this is not a fix for your problem I thought I would put my findings on the post, so that if someone else has a solution then this may help. plus it also bumps your post up to let more people view it!

Sub Printfiles()
Dim fPATH As String, wb As Workbook

fPATH = "C:\Temp\test\testfile.xlsm"
shtNAME = "Master"                 'put the name of the sheet that is to be printed
'Application.Dialogs(xlDialogPrinterSetup).Show          'allow user to select a different printer
    Set wb = Workbooks.Open(fPATH)      'open the file
    wb.Application.SendKeys ("^p")
    'wb.PrintOut                 'testing printout
End Sub

 

[paperfolder]

Yeah, I find it really odd that it is printing the module page, because as one of my debugging test, I checked which window was active right before the print command and it it shows that the workbook is active.

MsgBox "The name of the active window is " & ActiveWindow.Caption
        Application.SendKeys ("^p")