Double Sided Printing From Excel
Double Sided Printing From Excel
Double Sided Printing From Excel
Double Sided Printing From Excel
Double Sided Printing From Excel
Double Sided Printing From Excel Double Sided Printing From Excel Double Sided Printing From Excel Double Sided Printing From Excel Double Sided Printing From Excel Double Sided Printing From Excel Double Sided Printing From Excel Double Sided Printing From Excel
Double Sided Printing From Excel Double Sided Printing From Excel
Double Sided Printing From Excel
Go Back  Xtreme Visual Basic Talk > > > Double Sided Printing From Excel


Reply
 
Thread Tools Display Modes
  #1  
Old 02-14-2007, 11:26 AM
WonderMonkey WonderMonkey is offline
Newcomer
 
Join Date: Feb 2007
Posts: 4
Default Double Sided Printing From Excel


Great site. I stumbled across it the other day while doing my first Office Automation project and I've got everything complete except one thing.

My little app will be watching a directory for the arrival of data which it will then shove into Excel and send the results to a specific printer. I need the output to be double-sided. Assume that the printer I will be sending it to has the ability to print double-sided.

I have two sheets in the workbook. Sheet 1 will be the front and sheet 2 will be the back. I haven't tried putting both pages in the same sheet with a page break.

The first thing I tried was to send the workbook to a printer that I had already setup to print double-sided by default. I was hoping it would work but it didn't.
Code:
xlWB.PrintOut(, , , , "PQ-Purchasing-BH600-02")
The second thing I tried was to make a printer object and send it the file. It did not print double-sided either.
Code:
objPrintDoc = New System.Drawing.Printing.PrintDocument
        With objPrintDoc
            .PrinterSettings.PrinterName = "\\pq-nas-01\PQ-Purchasing-BH600-02"
            .DefaultPageSettings.PrinterSettings.Duplex = Printing.Duplex.Vertical
            .DocumentName = "C:\temp\PITemplate2.xls"
        End With

        If objPrintDoc.PrinterSettings.IsValid Then
            objPrintDoc.Print()
        End If
Anybody have any ideas on how to make something double-sided?

Last edited by WonderMonkey; 02-14-2007 at 12:17 PM.
Reply With Quote
  #2  
Old 02-14-2007, 03:36 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey WonderMonkey,

Welcome to the forum.

I don't have a lot of experience with printing, to be honest, and it can be trickier than it looks... But I think that your approach here would seem like the right idea:
Code:
xlWB.PrintOut(, , , , "PQ-Purchasing-BH600-02")
When I use the Macro Recorder in VBA, I get:
Code:
ActiveSheet.PrintOut(Copies:=1, ActivePrinter:= "HP LaserJet 1200 Series PCL 5e on Ne03:", Collate:=True)
And running the macro runs fin in VBA. (So maybe you should specifiy the number of copies and to collate?)

Ok, wait! Now looking at this in .NET, I see that the correct method to call is Worksheet.PrintOutEx(). I've never noticed a name change like this in any other method before when using VB.NET. (Although there are many, many if using C#.) Ok, so for starters, I would try calling .PrintOutEx(), it looks the same as far as I can tell and appears to be the approved method. (The .PrintOut() method is still available, but if you go into the Object Browser you'll see that it's a hidden member.)

I hope this helps! Let us know how it goes...

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 02-16-2007, 03:26 PM
WonderMonkey WonderMonkey is offline
Newcomer
 
Join Date: Feb 2007
Posts: 4
Default

Mike_R -> Thanks for taking the time to reply.

I've gotten around the problem. I think the whole issue was that I was trying to double side two worksheets. I moved them to one worksheet and now I get the double-sided as expected. I would rather have more control and do what I wanted but given the time crunch this is acceptable.

Also, I tried the xlWB.PrintoutEX(... ) and it wasn't supported. Just wanted you to know. I'm probably doing something incorrectly.

Regards,
WonderMonkey
Reply With Quote
  #4  
Old 02-16-2007, 05:18 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey WM,

Very glad you're on your feet.


Quote:
Originally Posted by WonderMonkey View Post
Also, I tried the xlWB.PrintoutEX(... ) and it wasn't supported. Just wanted you to know. I'm probably doing something incorrectly.
No, I'm sure you did it fine. I'm only guessing here, but we are problably referencing different versions of Excel or different interop assembly versions (e.g., the official PIA vs. a local IA). This is just my best guess off the top of my head... It's not worth tracking down now since you are up and running, but I'll keep this in mind for the future.

Good luck, and come back any time...
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #5  
Old 02-26-2007, 09:38 AM
WonderMonkey WonderMonkey is offline
Newcomer
 
Join Date: Feb 2007
Posts: 4
Default

Some additional information.....

When I tried to use

Code:
xlWB.PrintOut(, , , , "PQ-Purchasing-BH600-02")
everything would work fine as long as "PQ-Purchasing-BH600-02" is my default printer. Can't have that! For some reason no matter what printer I specify the default is what was used.

So here is what I did .....
1) Find out the current default printer and save the name.
2) Process print jobs, setting default printer for each "depending" and then print.
3) When all print jobs are done then I set the default printer back to what it was in step #1.

The only downside to this (other then there should be a better way) is that if anything on that machine attempts to send something to the default printer then it could be in transition and who knows where it would go. Any printer that I am switching to could be the default at anytime during execution.

Now for some code to find the default printer and to set a default printer:

Get Default Printer:
Code:
Public Function GetDefaultPrinter() As String
        'Declare WMI Variables
        Dim MgmtObject As ManagementObject
        Dim MgmtCollection As ManagementObjectCollection
        Dim MgmtSearcher As ManagementObjectSearcher
        Dim strPrinterName As String = ""

        'Perform the search for printers and return the listing as a collection
        MgmtSearcher = New ManagementObjectSearcher("Select * from Win32_Printer")
        MgmtCollection = MgmtSearcher.Get

        'Enumerate Objects To Find Printer
        For Each MgmtObject In MgmtCollection

            If MgmtObject.Item("Default") Then
                strPrinterName = MgmtObject.Item("name").ToString
            End If

        Next

        Return strPrinterName
    End Function
Set Default Printer:
Code:
     Public Function SetDefaultPrinter(ByVal PrinterName As String) As Boolean
        'Declare WMI Variables
        Dim MgmtObject As ManagementObject
        Dim MgmtCollection As ManagementObjectCollection
        Dim MgmtSearcher As ManagementObjectSearcher
        Dim ReturnBoolean As Boolean = False

        'Perform the search for printers and return the listing as a collection
        MgmtSearcher = New ManagementObjectSearcher("Select * from Win32_Printer")
        MgmtCollection = MgmtSearcher.Get

        'Enumerate Objects To Find Printer
        For Each MgmtObject In MgmtCollection
            'Look for a match
            'Debug.WriteLine(MgmtObject.Item("name").ToString)

            If MgmtObject.Item("name").ToString = PrinterName Then
                'Set Default Printer
                Dim TempObject() As Object 'Temporary Object for InvokeMethod. Holds no purpose.
                MgmtObject.InvokeMethod("SetDefaultPrinter", TempObject)

                'Set Success Value and Exit For..Next Loop
                ReturnBoolean = True
                Exit For
            End If
        Next

        'Return Success Value
        Return ReturnBoolean
    End Function
Reply With Quote
  #6  
Old 02-26-2007, 10:34 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey WM, thanks for sharing the info...

I trust that that the code you show works. One would think that there is a more direct way within the .NET Framework to change the Active Printer. I would think a one or two line command. But I don't know, I don't work directly with printer drivers, etc. really ever. But your code does look good.

My usual go-to move with this sort of thing is to use the Macro Recorder within VBA. Trying it now I guet code that looks like:

Code:
ActiveSheet.PrintOut(Copies:=1, ActivePrinter:="PDF995 on Ne00:", _ Collate:=True)
But other times looks like:

Code:
Application.ActivePrinter = "PDF995 on Ne00:" ActiveSheet.PrintOut(Copies:=1, Collate:=True)
So, ah-ha! There is a one-line command to do this, at least via the Excel object model, anyway.


Running this, this all runs fine for me, regardless of which printer is the active printer and regardless of whether I try this in VBA or VB.NET. And within VB.NET I can call either Worksheet.PrintOut() or Worksheet.PrintOutEx() with the same results...

So it's a bummer that your code is not doing the same. I wonder if you could try it on another PC and see what results you get?
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #7  
Old 02-27-2007, 07:05 AM
WonderMonkey WonderMonkey is offline
Newcomer
 
Join Date: Feb 2007
Posts: 4
Default

Quote:
"Application.ActivePrinter = "PDF995 on Ne00:"
Looks good! I went and looked and sure enough it's there. I am going to try that out. It would certainly condense my code and removes steps.

I'll let you know how it goes. Thanks for taking the time to reply.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Double Sided Printing From Excel
Double Sided Printing From Excel
Double Sided Printing From Excel Double Sided Printing From Excel
Double Sided Printing From Excel
Double Sided Printing From Excel
Double Sided Printing From Excel Double Sided Printing From Excel Double Sided Printing From Excel Double Sided Printing From Excel Double Sided Printing From Excel Double Sided Printing From Excel Double Sided Printing From Excel
Double Sided Printing From Excel
Double Sided Printing From Excel
 
Double Sided Printing From Excel
Double Sided Printing From Excel
 
-->