Setting Paper source for printer in macro. 1

[Maltor]

I have a macro that starts in Extra! and opens excel. Different people have different default printers. The problem is that if a user has a different default printer than the previous user, the manual feed setting is not carried over.

I know I could create multiple copies of the excel file specifically for each printer and create paths to each file individually in individual copies of the macro. I would prefer to have one macro as then only one macro needs to be changed when maintaining the macro.

Thanks,
Chris

 

[PHV]

You can try this:
YourExcelAppObject.Dialogs(xlDialogPrinterSetup).Show

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Nelviticus]

Hmm, it seems that you can't play with the printer settings in Excel VBA but you can in Word. One solution might be to create a Word object solely for printer manipulation, as any changes you make to the printer settings will be global (you'll have to remember to store the user's settings first and restore them when you're done).

In Word you can change

Options.DefaultTrayID

to the WdPaperTray constant

wdPrinterManualFeed

. One problem you might run into is that not all printer drivers conform to Microsoft's standard for the ID numbers of their paper trays, so

wdPrinterManualFeed

might not set the tray to the manual feed one!

I ran into this problem and spent ages banging my head against it and finally came up with this:

Public Sub PrintManual()
    
    Dim lngTrayID As Long       ' Current tray ID
    Dim lngOrigTrayID As Long   ' Original tray ID
    Dim lngIterations As Long   ' Number of possible tray IDs to loop through
    
    lngTrayID = 0
    lngIterations = 5000
    
    With Options
        
        ' Store the original tray option
        lngOrigTrayID = .DefaultTrayID
        
        ' Loop through the first lngIterations possible tray options until
        ' we find one containing the word "manual"
        Do While lngTrayID <= lngIterations And InStr(1, UCase(.DefaultTray), &quot;MANUAL&quot;, vbTextCompare) = 0
            
            lngTrayID = lngTrayID + 1
            .DefaultTrayID = lngTrayID
            
        Loop
        
        ' Print the document
        Application.PrintOut Filename:=&quot;&quot;, Range:=wdPrintAllDocument, Item:= _
            wdPrintDocumentContent, Copies:=1, Pages:=&quot;&quot;, PageType:=wdPrintAllPages, _
            Collate:=True, Background:=True, PrintToFile:=False
        
        ' Restore the original tray option
        .DefaultTrayID = lngOrigTrayID
        
    End With
    
End Sub

It loops through loads of possible settings for the printer tray until it finds one that has the word "manual" in its name. Hopefully you can modify it to suit your needs if you decide to pursue this route.

Nelviticus