Printing Excel Problems

[SonicBoomAu]

Every time I try and print an excel worksheet it asks me for the file name.

My Code is as follows:

[COLOR=DarkOrange]
             Capture the printer
             Need to Select the Printer
            Dim ps As New Printing.PrinterSettings
            PrintDialog1.PrinterSettings = ps
             Capture the Original Printer
            strDefaultPrinter = Me.PrintDialog1.PrinterSettings.PrinterName
             Need to Select the Printer
            Me.PrintDialog1.ShowDialog()
            strSelectedPrinter = Me.PrintDialog1.PrinterSettings.PrinterName
             Change the default printer to reflect the selection
            Call ChangeDefaultPrinter(strSelectedPrinter)[/COLOR]


             Start Adding Details to a New Excel Workbook
            Dim objApp As Excel.Application
            Dim objBook As Excel._Workbook
            Dim objBooks As Excel.Workbooks
            Dim objSheets As Excel.Sheets
            Dim objSheet As Excel._Worksheet
            Dim range As Excel.Range

             Create a new instance of Excel and start a new workbook.
            objApp = New Excel.Application
            objBooks = objApp.Workbooks
            objBook = objBooks.Add
            objSheets = objBook.Worksheets
            objSheet = objSheets(1)

             Capture the Details
            etc....


             Print the Excel Document
            objApp.Worksheets.PrintOut()

The worksheet was able to print until I added the ability to select the printer.

Any help is greatly appreciated

 

[Mike_R]

I guess that you are trying to print to a PDF or something? If that is the case, then I believe you can set the Printing preferences in acrobat distiller to handle this without a user-prompt.

The other way to go is to use SendKeys. Its an ugly kluge of an idea, but in a pinch it can get the job done. Ive seen it necessary in print situations like this when the settings could not be pre-set properly.

Let me know if you are using Acrobat or some other printer driver that can be pre-set. If not, then SendKeys("{Enter}") may do the trick.

 

[SonicBoomAu]

Thanks Mike,

But, Im not trying to create a PDF. Im just trying to print the excel document as is.

I thinking about saving the document and then printing it out using the printdailog boxs print document command.

 

[Mike_R]

But, Im not trying to create a PDF. Im just trying to print the excel document as is.

Ok, but I think the inherent issue is the Printer Driver here, not anything that Excel directly controls. If it is within Excels control, then setting xlApp.DisplayAlerts = False should do the trick. I suspect this wont do it however. (But it is worth a shot.)

Whatever printer driver you are using is requiring human input. So you must set that drivers settings properly to print without human input or use SendKeys() to send the correct sequience of keys to make it work. SendKeys("{Enter}") may be sufficient in this case, but Im not exactly sure what MessageBox you are getting.

I thinking about saving the document and then printing it out using the printdailog boxs print document command.

Ok, if I understand this right, you are considering using:

xlApp.Dialogs(xlDialogPrint).Show

instead of the more typical:

xlApp.ActiveSheet.PrintOut

Do I understand this right?

This will force you into a SendKeys() situation for sure. But since it looks like you were headed this way more or less anyway, I guess it couldnt hurt. It wouldnt be my first choice though.

 

[SonicBoomAu]

Thanks for that Mike,

Will have a play and see what I can come up with. You have given me somethings to think about.

 

[SonicBoomAu]

Have tried using the sendkeys.send and objApp.Sendkeys ("{CTRL+P}").

It is coming up with the following error
Exception from HRESULT : 0x800A03EC

Am I using the wrong syntax??

I add "objApp.DisplayAlerts = False" and still received the same error.

I removed my code for changing the default printer and I am able to print the document without any problem but I am unable to select a printer. It automatically selects the default.

Any ideas welcome

 

[Mike_R]

If you look at the help files for the Worksheet.PrintOut() command, youll see that there is an optional ActivePrinter parameter. I had to use the Macro Recorder to figure out what string to pass, but for my machine the correct argument was:

WS.PrintOut(ActivePrinter:="HP LaserJet 1200 Series PCL 5e on Ne01:")

Hope this gets you going!

 

[SonicBoomAu]

Thanks Mike,

Youre a legend.

If you look at the help files for the Worksheet.PrintOut() command, youll see that there is an optional ActivePrinter parameter. I had to use the Macro Recorder to figure out what string to pass, but for my machine the correct argument was:

WS.PrintOut(ActivePrinter:="HP LaserJet 1200 Series PCL 5e on Ne01:")

Hope this gets you going!

Due to pulling the printer details earlier I was able to set the active printer using the captured details. I.E. (strSelectedPrinter)

             Capture the printer
             Need to Select the Printer
            Dim ps As New Printing.PrinterSettings
            PrintDialog1.PrinterSettings = ps
             Capture the Original Printer
            strDefaultPrinter = Me.PrintDialog1.PrinterSettings.PrinterName
             Need to Select the Printer
            Me.PrintDialog1.ShowDialog()
            strSelectedPrinter = Me.PrintDialog1.PrinterSettings.PrinterName
             Change the default printer to reflect the selection
            Call ChangeDefaultPrinter(strSelectedPrinter)


             Print the Excel Document
            objApp.Worksheets.PrintOut(ActivePrinter:=strSelectedPrinter)

Once again thanks for all your Help.

 

[Mike_R]

Ah, great job putting that all together!

Nice work. And glad I could help...