Setting the path in windows 11 1

As-Lag · Jun 27, 2022

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.

Code:

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 · Jun 28, 2022

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 · Jun 28, 2022

Thanks

I will try out and report back.

MintJulep · Jun 28, 2022

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 · Jul 16, 2022

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.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Setting the path in windows 11 1

As-Lag

Structural

IDS

Civil/Environmental

As-Lag

Structural

MintJulep

Mechanical

As-Lag

Structural

Similar threads

Part and Inventory Search

Sponsor