Help finding Active printer "on Ne02"

[Hlatigo]

Morning all!

I have excel printing some sheets to pdf and it works fine on my PC. WHen someone else tries to print it wont work because they arent on "Adobe PDF on Ne02:"...they maybe on Ne03 or Ne08 or ne05...you get my point.

I saw code on here before as to how to find the proper "port" but after a bit of searching, i cant find it again. anyone have an idea?

thanks in advance!

 Application.ActivePrinter = "Adobe PDF on Ne02:"
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
            "Adobe PDF on Ne02:"

 

[nbrcrunch]

does this help

http://www.erlandsendata.no/english/index.php?d=envbaprintchangeprinter

 

[westconn1]

to get all the available printers with ports into a list box or array

      Dim reg As Variant, oreg As Object, mystr As Variant
       Const HKEY_CURRENT_USER = &H80000001
       
      Set oreg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
       
      oreg.enumvalues HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", mystr, Arr
      For Each reg In mystr
          oreg.getstringvalue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", reg, regvalue
          list1.additem reg & " on " & Mid(regvalue, InStr(regvalue, ",") + 1)
      Next

from ths it is easy to match the printer name to the port

 

[Hlatigo]

ok guys...i will give this a try. thanks for your help.

 

[Hlatigo]

Hi nbrcrunch!

I dont think that code will work. the second may work however that is way beyond my skill level that I wouldnt even know how to modify it.





Hi westconn1
I used your code but i get an error on "Arr" stating that variable not defined.

any thoughts?

 

[westconn1]

dim arr as variant

 

[Hlatigo]

Hi there!

Does anyone know if i should replace the bottom strip of code with the top and also, i get a variable not defined on the top code for reg and regvalue. Should i just dim as variant as well?

Which will ultimately find the adobe print and use the right address?

Dim reg As Variant, oreg As Object, mystr As Variant, Arr as variant
       Const HKEY_CURRENT_USER = &H80000001
       
      Set oreg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
       
      oreg.enumvalues HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", mystr, Arr
      For Each reg In mystr
          oreg.getstringvalue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", reg, regvalue
          list1.additem reg & " on " & Mid(regvalue, InStr(regvalue, ",") + 1)
      Next

WriteRegistry HKEY_CURRENT_USER, _
"Software\Adobe\Acrobat PDFWriter\", _
"PDFFileName", _
"C:\Printout.pdf"

Application.ActivePrinter = "Adobe PDF on Ne02:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne02:"

 

[westconn1]

the bottom code writes a line to the registry, which is unlikely to do what you want

what it does is to set the file name for the next file to be printed by the acrobat pdf printer driver, this does not affect the port the pdf driver is on

Should i just dim as variant as well?

yes
you will also need to change list1 to the name of a listbox or combobox you have

 

[Hlatigo]

oooof!

I dont have a list combo.

everyone who uses this workbook has different printers and hence we all have different locations if that is what it is called. is there a way to manually have everyone change their PC to have adobe pdf on "Ne02:"

Im afraid your code is beyond me

 

[westconn1]

no, ne02 may already be in use when they install pdf driver

but you can use a cut down version of the above code like this

       Const HKEY_CURRENT_USER = &H80000001
      Set oreg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
   prn = "CutePDF Writer"        ' put your exact printer name
          oreg.getstringvalue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", prn, regvalue
            activeprinter = prn & " on " & Mid(regvalue, InStr(regvalue, ",") + 1)

the original code was to supply a list of all available printers with port, for the user to select from
declare the variables as strings or variants