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. It's an ugly kluge of an idea, but in a pinch it can get the job done. I've 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, I'm not trying to create a PDF. I'm just trying to print the excel document as is.

 

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

[Mike_R]

But' date=' I'm not trying to create a PDF. I'm just trying to print the excel document as is.[/quote']Ok, but I think the inherent issue is the Printer Driver here, not anything that Excel directly controls. If it is within Excel's control, then setting xlApp.DisplayAlerts = False should do the trick. I suspect this won't do it however. (But it is worth a shot.)

 

Whatever printer driver you are using is requiring human input. So you must set that driver's 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 I'm not exactly sure what MessageBox you are getting.

 

 

I thinking about saving the document and then printing it out using the printdailog box's 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 couldn't hurt. It wouldn't be my first choice though.

Edited January 21, 2005 by Mike_R

[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, you'll 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, :D

 

You're a legend. :D

 

If you look at the help files for the Worksheet.PrintOut() command' date=' you'll 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![/quote']

 

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. :D

[Mike_R]

Ah, great job putting that all together!

 

Nice work. And glad I could help... :)