Access 2003/Excel 2003 - looping thru the printers object

Hi EE,

I found this related post, and adapted it for my purposes.
Adobe Acrobat PRO is installed differently on each of our pc's.

I have mdb that behaves like a script, by being evoked by Windows Scheduler thru a bat file.

I am going away for a few days, would like to disable the script on my pc, and ask a colleague to run it manually.
My script failed, bec have hardcoded:

 objExcel.ActivePrinter = "Adobe PDF on LPT2:"

and his is:
"Adobe PDF on ne05"

so copied your ideas to do this:

Dim p              As Printer
Dim strPrinterName As String
Dim lngCount       As Long


For Each p In Printers
   Debug.Print p.DeviceName & " "; p.Port
   
   lngPos = InStr(1, p.DeviceName, "Adobe")
   If lngPos > 0 Then
      objExcel.ActivePrinter = p.DeviceName
      Exit For
   End If
   
Next

However, this line is faling
             objExcel.ActivePrinter = p.DeviceName

bec. it does not recognize printer of: "Adobe Acrobat"
it's looking for the full name of:


"Adobe PDF on LPT2:"

i did a debug.print on p.port, but it shows an ip address, and don't know how to use this in VBA.

below is a screen shot:

pls advise,
tx for your ideas and help,
sandra
2010-02-11-printers-msg.GIF
mytfeinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

hitsdoshi1Commented:
Hi Sandra:

Here are the couple of examples I found online.....you can give it a shot....

http://www.dailydoseofexcel.com/archives/2008/04/05/getting-the-printer-port/


Other one is example in excel file...


I haven't looked in details.....but I think this might be able to help you...it gets the printer information from registry

MyOzGridExample-25219.zip
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
mytfeinAuthor Commented:
hi hd,

just looked at first example:
    i don't think i have under Tools/refs
              registration manipulation classes in the list

though calling the function like that is just what i'm looking for....

will look at your next example... tx, s
0
mytfeinAuthor Commented:
hi hd,

a) don't think i need the 2nd example bec. i can use the windows dialogue button for user to choose
printer as you taught me yest, and i want to run w/o user intervention.

b) just wondering out loud, i searched EE today on ".devicename" and it seems popular for people
to load a combo box from printer objects, but how does it work with just printer name, when on my
pc it's looking for the port name as part of the printer name?

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html:

c) so back to 1st example  
      i tried googling    manipulation registry classes access 2003
              cannot find info
                   how do i get  it to show under toos/refs

d)  the 1st example also has a comment by another person about doing it with
          command line and shell, which i do not have too much experience with

maybe will post related questions for c and d, interested in your opinions so far,

tx, s
c) back to 1st examplee
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Helen FeddemaCommented:
Here is a little function I use to get the actual printer names as used in code, from Access.  If you have Access installed, put this function into a standard modules and run it; the printer names will appear in the Immediate Window.
Public Function ListPrinters()
   
   Dim i As Integer
   Dim prt As Access.Printer
   
   i = 0
   
   For Each prt In Application.Printers
      Debug.Print i & " - " & prt.DeviceName
      i = i + 1
   Next prt
   
End Function

Open in new window

0
Helen FeddemaCommented:
Once you get the actual printer name on a computer, you can use it to set the active printer.
0
mytfeinAuthor Commented:
Hi Helen,

I don't know why VBA on our pc's expects the port name in the printer name as well.

Just found and tested this EE solution and it works:

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_20780638.html?sfQueryTermInfo=1+get+microsoft+port+printer+vba 

that uses:
     http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=183

i coded this and logic works:

      Dim p              As Printer
Dim strPrinterName As String
Dim lngCount       As Long


For Each p In Printers
   Debug.Print p.DeviceName & " " & p.Port & " "; p.DriverName
   
   lngPos = InStr(1, p.DeviceName, "Adobe")
   If lngPos > 0 Then
   
' http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_20780638.html?sfQueryTermInfo=1+get+microsoft+port+printer+vba
' http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=183

      Dim pInfo As PrinterInfo
      pInfo = GetPrinterDetails(p.DeviceName)
 
      MsgBox "Port name is " & pInfo.PortName
      MsgBox "Printer status is " & pInfo.Status
     
      objExcel.ActivePrinter = p.DeviceName & " on " & pInfo.PortName
      Exit For
   End If
   
Next

@hd gave good ideas, too -
      a) don't seem to have manipulation registration classes under tools/refs
      b) don't fully understand the script command ideas

     as i am going away, just wanted to get this working for colleague's pc.
     just don't know which approach of all ideas is best....

     though with the solution that is working, you can test status of printer and send it to another
     if not avail.

     Still don't understand why my system requires port name in vba, and as you suggest and have
     seen on EE, people use device name to print w/o port name and do not have a problem

tx, s    
0
hitsdoshi1Commented:
That exactly what I was thinking.......Can you shed some details on what software/OS versions are you using ?

I like the Helen's code but it still doesn't offer port...
0
hitsdoshi1Commented:
So here is the "maybe" workaround solution you might want to take a look at...

Basically, its a function.....define your network in array....and loop around...and get a hit at the right one :)

Function NetworkPrinter(ByVal myprinter As String)
  On Error Resume Next
  Dim NetWork As Variant
  Dim X As Integer
  '/// Define NetWork Array \\\
  NetWork = Array("Ne00:", "Ne01:", "Ne02:", "Ne03:", "Ne04:", _
                   "Ne05:", "Ne06:", "Ne07:", "Ne08:", _
                   "Ne09:", "Ne10:", "Ne11:", "Ne12:", _
                   "Ne13:", "Ne14:", "Ne15:", "Ne16:", _
                   "LPT1:", "LPT2:", "File:", "SMC100:")
  'Setup printer to Print
  X = 0
TryAgain:
  On Error Resume Next
  'Printer
  Application.ActivePrinter = myprinter & Prt_On & NetWork(X)
  If Err.Number <> 0 And X < 16 Then
    X = X + 1
    GoTo TryAgain
  ElseIf Err.Number <> 0 And X > 15 Then
    GoTo PrtError
  End If
  On Error GoTo 0
  NetworkPrinter = myprinter & Prt_On & NetWork(X)
errorExit:
  Exit Function
PrtError:
  'no printer found
  NetworkPrinter = ""
  Resume errorExit
End Function
0
mytfeinAuthor Commented:
hi hd,

tx for all the work in your code,

use Windows XP, OFFICE 2003

Just wondering, how did you know all your ports for your array?

tx, s
0
hitsdoshi1Commented:
This problem really bothers me too......its seems quite simple thing to do....Anyway here is my another try

http://support.microsoft.com/kb/q166008/

I tried this code and it lists all the printer on network.....try EnumeratePrinters4 and may be if it displays name & port or server then you can just fetch those fields and make it as active printer.....something like

if Pname="Adobe PDF" then set activeprinter to Pname & Sname or something like that...

I have the code in excel if you don't want to copy and past....Code in Module 1
Book1.xls
0
mytfeinAuthor Commented:
hi hd,

tx,

stepped thru both macros, does not print the port

hope you noticed the solution that i found above

will close out post and award you points bec. you provided lots of useful info

tx, Helen, yet i had a similar loop posted in the questions....

tx all
0
mytfeinAuthor Commented:
Dear EE public,

the solution is above at comment:   26547328

just want to close post for now... tx
0
Helen FeddemaCommented:
I have never had to use the port when selecting a printer, FWIW.  Maybe some other expert can help with this part of the problem.
0
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
Microsoft Access

From novice to tech pro — start learning today.