Change Excel Printer in VB.NET

Hi Experts,
  I have an app that sends info to Excel, then creates a .prn file using the Acrobat Distiller.  It works fine when the printer name is hardcoded.  But now, I'm to a point where I need to adjust it to work on different PCs and that is a problem.  For a reason I don't understand, I've had to assign the name of the Distiller printer with a strange extension to get it to work.

Basically, this is what I need to get working (Windows XP machine):
objExcel.ActivePrinter = "Acrobat Distiller"    '<-- Causes an Error
objExcel.ActivePrinter = "Acrobat Distiller on Ne08:"   '<--OK

  What's the deal with that little "on Ne08:" extension?  Is that the port?  The code below returns "Acrobat Distiller"

             Dim strPrintersList As String
            ' Find all printers installed, assign Acrobat Distiller
            For Each strPrintersList In System.Drawing.Printing.PrinterSettings.InstalledPrinters
                If InStr(strPrintersList, "Acrobat Distiller") Then
                    objExcel.activeprinter = strPrintersList   ' <- ERROR HERE
                End If
            Next strPrintersList

            objExcel.ActivePrinter = strPrintersList   'Assign Distiller as printer

I need someone to come through on this one!  Sorry, but I can't use a PrintDialog.
Thanks experts!
-Ren
RenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

armoghanCommented:
Ne08 semes to be the machine name
0
RenAuthor Commented:
So, how do I incorporate Ne08 into the printer name so that I will be able to assign my Acrobat Distiller as excel's active printer?

-Ren
0
dsaboCommented:
You should look at the shared printer properties, and change the rename the printer. that would work.

look at your Printers, and change the name of the printer
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

RenAuthor Commented:
Thanks for the advice, dsabo.  I looked at the properties and it says the name of the printer is "Acrobat Distiller".  Does that make sense??  That's what is so weird, this line:

For Each strPrintersList In System.Drawing.Printing.PrinterSettings.InstalledPrinters

... gets the name of the printer I want and sets strPrintersList = "Acrobat Distiller".  That is exactly what I want.  But the assignment of objExcel.ActivePrinter = strPrintersList is where the problem occurs.

Before, I was just using this statement to hardcode the printer:
objExcel.ActivePrinter = "Acrobat Distiller on Ne08:"

...and that worked fine.

This causes an error because strPrintersList  = "Acrobat Distiller" without the "on Ne08:" statement:
objExcel.activeprinter = strPrintersList   ' <- ERROR HERE

So, if the name of the printer is "Acrobat Distiller" then what is the "on Ne08" and why can't I assign the printer.  What am I not seeing?

Just a side note, I do a similar thing with Word and strPrintersList = "Acrobat Distiller" DOES work with word.  This seems to be specific to Excel.

Thanks!
-Ren
0
dsaboCommented:
Try to rename the printer on the control panel /printers.....

rename the printer name, but not in the properties.
0
armoghanCommented:
I think "on Ne08" will work always when you are printing an Excel file.
So just hardcode it when you are going to print an Excel file
0
RenAuthor Commented:
 I wish it were that easy, armoghan, I wondered if it was "Ne08" on all computers too.  But I checked on a co-workers' computer and theirs is "Ne14".  So I can't take a chance like that with an app that the entire office will be using.  I suppose, at worst, I could just cycle through and concatenate "Acrobat Distiller" with every combination of "Ne01" through "Ne99" until I made a successful printer reassignment.  I may end up doing that if nobody can figure this one out.
  dsabo, thanks again for the advice, but the name in the control panel / printer is still just "Acrobat Distiller" without that extension.  This is very confusing.  Really, the only way I've been able to find the name with the extension is to open excel, then the vba screen and type "? Application.activeprinter" in the immediate window.  It's also with any other printer, like my default networked printer also has an extension like that.  I am wondering if it's some kind of Windows XP thing.

-Ren
0
RenAuthor Commented:
 I decided to go ahead and just loop through all the possible numbers Ne could have, this solution seems to do the trick.  All I do is loop through 1 to 99 and concatenate that with a "Acrobat Distiller on Ne" string.  If I can set Excel's activeprinter to the concatenated string, I know it worked.

        Do While (1)
            For NeoCTR = 1 To 99
                Try
                    If NeoCTR < 10 Then
                        objExcel.ActivePrinter = "Acrobat Distiller on Ne0" & NeoCTR & ":"
                    Else
                        objExcel.ActivePrinter = "Acrobat Distiller on Ne" & NeoCTR & ":"
                    End If
                    'will only get to this point with a successful assign, otherwise Err
                    Exit Do
                Catch x As Exception
                    If NeoCTR = 99 Then
                        MsgBox("Cannot locate Acrobat Distiller printer. Unable to continue.")
                        g_blnAbortAll = true
                        Exit Sub
                    End If
                End Try
            Next NeoCTR
        Loop

I'll leave this open for awhile to see if anyone can figure out what the "on Ne08" means.
Thanks all!

-Ren
0
moduloCommented:
Closed, 500 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.