0 Comments

A client of mine was having a problem trying to automate Excel to print to a specific (non default) printer.  They know which printer they want to use, but are having trouble trying to get Excel to understand it.

Excel’s Application.ActivePrinter property is quite picky about what it will accept.  The right pattern to feed to the property is something like “PrinterName on PortName:”.

For example, if you want to print to Microsoft XPS Document Writer, you need to do something like this:

var excel = new Excel.Application();
var workbook = excel.Workbooks.Open("....");
var worksheet = workbook.ActiveSheet;

excel.ActivePrinter = “Microsoft XPS Document Writer on ne01:”;
worksheet.PrintOut();

excel.Quit();

And this of course assuming that the selected printer (Microsoft XPS Document Writer) is on port ne01.

Getting the right name for the printer is somewhat trivial.  You can poke around the Devices and Printers control panel item on Windows and see their name.

The problem is… the port name information is not so obvious to find.

So, how would you know where to get the port name?

Trying to enumerate the attached printers as described by this StackOverflow question does not gives you the right port name… See the sample code and output below…

The Code:

using System;
using System.Management;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            var oquery = new ObjectQuery("SELECT * FROM Win32_Printer");
            var mosearcher = new ManagementObjectSearcher(oquery);
            var moc = mosearcher.Get();            

            foreach (var o in moc)
            {
                var pdc = o.Properties;

                foreach (var p in pdc)
                {
                    if (p.Name == "Name" || p.Name == "PortName")
                    {

                        Console.WriteLine("{0}: {1}", p.Name, o[p.Name]);
                    }
                }
            }
        }
    }
}

The result:

Name: Xerox WorkCentre 3119 Series
PortName: USB001
Name: Send To OneNote 2010
PortName: nul:
Name: Microsoft XPS Document Writer
PortName: XPSPort:
Name: CAMPUS-R101-DC236
PortName: 192.168.100.21
Name: CAMPUS-R217-DC236
PortName: 192.168.100.22
Name: Fax
PortName: SHRFAX:
Name: \\TIGER\Canon Inkjet MP140 series
PortName: USB001

You just have to trust me the port name here is not the port name that Excel wants to use.

So, where else can you get this particular information?

After looking around the web, I found this post that gives some clues on how to do this from VBA that can easily be adapted for .NET.

It turns out that the key to this is to poke around in the registry. in particular HKCU\Software\Microsoft\Windows NT\CurrentVersion\Devices.

So I came up with the code below to test it and lo and behold.  It works perfectly.  Basically I enumerate through all the devices in that particular registry location, did some string manipulation on the port (remove the winspool, prefix from the port name) and concatenate the stripped version of the port name with the printer / device name to form the PrinterName on PortName: combination that is required by Excel.

using System;
using Microsoft.Win32;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            var excelApp = new Excel.Application();
            excelApp.Visible = true;

            var workbook = excelApp.Workbooks.Add();
            var worksheet = workbook.ActiveSheet;

            var key = Registry.CurrentUser;
            var subkey = key.OpenSubKey(@"Software\Microsoft\Windows NT\CurrentVersion\Devices");

            var printerNames = subkey.GetValueNames();

            //Should be able to complete this loop without throwing an exception
            //if all the names matches to what Excel is expecting.
            foreach (var printerName in printerNames)
            {
                var excelPrinterName = ConvertToExcelPrinterFriendlyName(printerName);
                Console.WriteLine(excelPrinterName);

                excelApp.ActivePrinter = excelPrinterName;
            }

            GC.Collect();
            GC.WaitForPendingFinalizers();

            Marshal.FinalReleaseComObject(worksheet);

            workbook.Close(false);
            Marshal.FinalReleaseComObject(workbook);

            excelApp.Quit();
            Marshal.FinalReleaseComObject(excelApp);
        }

        public static string ConvertToExcelPrinterFriendlyName(string printerName)
        {
            var key = Registry.CurrentUser;
            var subkey = key.OpenSubKey(@"Software\Microsoft\Windows NT\CurrentVersion\Devices");

            var value = subkey.GetValue(printerName);
            if (value == null) throw new Exception(string.Format("Device not found: {0}", printerName));

            var portName = value.ToString().Substring(9);  //strip away the winspool, 
 
            return string.Format("{0} on {1}", printerName, portName);;
        }
    }
}

Hope this is useful for those who need it… Enjoy.