Setting the path in windows 11 1

[As-Lag]

Hello
I have moved some VBA from a Windows 7 machine running Excel 2007 onto a Windows 11 running Excel 2022. The code selects five sheets, one at a time, does some basic formatting and then exports the sheet to a folder called Output as a pdf. On the Win 7 machine, I use CutePDF to print; on the Win 11 machine, I want to use the Microsoft print to PDF printer.

When I go to print the pages, the pages are printed correctly but are placed in "Documents", not "Output". Any clues why the Win 11 machine is not responding as the Win 7 machine please? Is it to do with the ChDir bit of the path? Regards.

Sub DoNagPrint()
'Stop
'Set the printer
    Application.Dialogs(xlDialogPrinterSetup).Show

'Set the printout path

    ChDir "D:\Office\New Age Glass\Excel\Output"
    
'Get Sheet data
    
    Dim projectReference, propertiesReference, propertiesReferenceE, notesReference, shutteringReference As String
    
        projectReference = Sheets("NAG").Range("A1").Value
        propertiesReference = Sheets("NAG").Range("I3").Value
        propertiesReferenceE = Sheets("NAG").Range("I3").Value + "E"
        notesReference = Left(projectReference, 8) & "-4" & Range("G4").Value & Range("C4")
        shutteringReference = Left(projectReference, 8) & "-5" & Range("G4").Value & Range("C4")
        
'Create file names

    Dim aFileName(5) As String
    Dim i As Integer
       
        aFileName(1) = projectReference & " - Calculations.pdf"
        aFileName(2) = propertiesReference & " - Properties.pdf"
        aFileName(3) = propertiesReferenceE & " - Properties.pdf"
        aFileName(4) = notesReference & " - Notes.pdf"
        aFileName(5) = shutteringReference & " - Shuttering.pdf"
               
'Write to files
        For i = 1 To 5
        Sheets(i).Activate
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
            aFileName(i), Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
        Next i

        Sheets("NAG").Activate
        
    MsgBox "Done"

End Sub

 

[IDS]

I very rarely use the print command from VBA, but message no 6 in the thread linked below looks like it should help:

https://www.mrexcel.com/board/threads/printing-vba-code-to-select-printer.79535/

dim myprinter as string
dim printer_name as sttring
printer_name ="name goes here"

myprinter = Application.ActivePrinter
Change_Form.PrintOut Preview:=False, ActivePrinter:=printer_name, PrintToFile:=True, PrToFileName:=PSFileName
Application.ActivePrinter = myprinter

If you include the full path in PSFileName that looks like it should work.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[As-Lag]

Thanks

I will try out and report back.

 

[MintJulep]

There is some ambiguity between "current folder" and "default folder" in the online documentation.

Build aFileName explicit and it should work as expected.

 

[As-Lag]

Dear MintJulep

I was specific with the file name by adding the path to the filename and it works. But why did Excel 2007 - and Excel 2010 - work and not the new Excel?

Regards to you all.