Hallo NG,

if work with the Code below (from http://www.xlam.ch/vbacode/index.htm), i have the problem, that it only works with local printers. But if i connect my networkprinters with the microsoft tool "con2prt", the printername is for example: \\printservername\ADOBE-PDF
When i write Const strPrinter As String = "\\printservername\ADOBE-PDF" i get the failure "Printer '\\printservername\ADOBE-PDF' - The Port Name was not found"

What is the problem with networkprinters ??

Kind regards

Hassan

Sub GetPrinterPortName()
Const strPrinter As String = "HP LaserJet 4M Plus"
Dim strSetting As String
Dim strPort As String
Dim intChar As Integer
On Error Resume Next
strSetting = CreateObject("WScript.Shell").RegRead("HKEY_CURREN T_USER\Software\Microsoft\Windows NT\CurrentVersion\Devices\" & strPrinter)
If Err.Number = -2147024894 Then
MsgBox "Es existiert kein Drucker '" & strPrinter & "'.", vbInformation
Exit Sub
End If
For intChar = Len(strSetting) To 1 Step -1
If Mid$(strSetting, intChar, 1) = "," Then
strPort = Mid$(strSetting, intChar + 1)
Exit For
End If
Next intChar
If strPort = "" Then
MsgBox "Drucker '" & strPrinter & "'" & vbCrLf & vbCrLf & _
"Der Port-Name wurde nicht gefunden!", vbExclamation
Else
MsgBox "Drucker '" & strPrinter & "'" & vbCrLf & vbCrLf & "Name des Ports: " & strPort & vbCrLf & _
"Excel-Druckerbezeichnung: " & strPrinter & " auf " & strPort, vbInformation
End If
End Sub

On Thu, 3 Apr 2008 14:29:23 +0200, Holger Hasenmüller
wrote:

Hallo NG,

if work with the Code below (from http://www.xlam.ch/vbacode/index.htm), i have the problem, that it only works with local printers. But if i connect my networkprinters with the microsoft tool "con2prt", the printername is for example: \\printservername\ADOBE-PDF
When i write Const strPrinter As String = "\\printservername\ADOBE-PDF" i get the failure "Printer '\\printservername\ADOBE-PDF' - The Port Name was not found"

What is the problem with networkprinters ??


That's a cool way to get the printer port. The shell object doesn't like
the backslashes in the UNC path - it thinks it's a regsitry path. You can
use RegObj instead. See here

http://www.dailydoseofexcel.com/arch...-printer-port/
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

"Dick Kusleika" schrieb

See here

http://www.dailydoseofexcel.com/arch...-printer-port/

Hi Dick,

thanks for your answer, but the plaudit isn't mine :-(

I have just copied the code, because i'm first learning VBA.

So here my demand (i hope it is the right word ;-)

Can you show me here a sample VBA Code with an UNC Path Networkprinter,
which i can use for adaption ?

Thank your very much

Kind regards

Holger

On Mon, 7 Apr 2008 11:43:14 +0200, "Holger Hasenmüller"
wrote:

"Dick Kusleika" schrieb

See here

http://www.dailydoseofexcel.com/arch...-printer-port/


So here my demand (i hope it is the right word ;-)

"request" is the right word, but you're English is very good (and my German
is very bad).

Can you show me here a sample VBA Code with an UNC Path Networkprinter,
which i can use for adaption ?


Make sure you set a reference like shown in the above link.

Sub GetPrinterPortName()
Const strPrinter As String = "\\LIZ\New_SAVIN C3828 RPCS"
Dim strSetting As String
Dim strPort As String
Dim intChar As Integer

strPort = GetPrinterPort(strPrinter)

If strPort = "" Then
MsgBox "Drucker '" & strPrinter & "'" & vbCrLf & vbCrLf & _
"Der Port-Name wurde nicht gefunden!", vbExclamation
Else
MsgBox "Drucker '" & strPrinter & "'" & vbCrLf & vbCrLf & _
"Name des Ports: " & strPort & vbCrLf & _
"Excel-Druckerbezeichnung: " & strPrinter & _
" auf " & strPort, vbInformation
End If

End Sub

Function GetPrinterPort(sPrinterName As String) As String

Dim objReg As RegObj.Registry
Dim objRootKey As RegObj.RegKey
Dim sKey As String
Dim objVal As RegObj.RegValue
Dim sData As String
Dim vData As Variant

sKey = "\HKEY_CURRENT_USER\Software\Microsoft\Windows
NT\CurrentVersion\Devices\"
Set objReg = New RegObj.Registry
Set objRootKey = objReg.RegKeyFromString(sKey)

For Each objVal In objRootKey.Values
If objVal.Name = sPrinterName Then
sData = objVal.Value
Exit For
End If
Next objVal

If Len(sData) 0 Then
vData = Split(sData, ",")
GetPrinterPort = vData(UBound(vData))
Else
GetPrinterPort = ""
End If

Set objReg = Nothing

End Function
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Hi Dick,

i have copied your source code, and the modified to:

Sub GetPrinterPortName()
Const strPrinter As String = "\\srv01.huk.local\ADOBE-PDF"
Dim strSetting As String
Dim strPort As String
Dim intChar As Integer

strPort = GetPrinterPort(strPrinter)

If strPort = "" Then
MsgBox "Drucker '" & strPrinter & "'" & vbCrLf & vbCrLf & _
"Der Port-Name wurde nicht gefunden!", vbExclamation
Else
MsgBox "Drucker '" & strPrinter & "'" & vbCrLf & vbCrLf & _
"Name des Ports: " & strPort & vbCrLf & _
"Excel-Druckerbezeichnung: " & strPrinter & _
" auf " & strPort, vbInformation
End If

End Sub

Function GetPrinterPort(sPrinterName As String) As String

Dim objReg As RegObj.Registry
Dim objRootKey As RegObj.RegKey
Dim sKey As String
Dim objVal As RegObj.RegValue
Dim sData As String
Dim vData As Variant

sKey = "\HKEY_CURRENT_USER\Software\Microsoft\Windows
NT\CurrentVersion\Devices\"
Set objReg = New RegObj.Registry
Set objRootKey = objReg.RegKeyFromString(sKey)

For Each objVal In objRootKey.Values
If objVal.Name = sPrinterName Then
sData = objVal.Value
Exit For
End If
Next objVal

If Len(sData) 0 Then
vData = Split(sData, ",")
GetPrinterPort = vData(UBound(vData))
Else
GetPrinterPort = ""
End If

Set objReg = Nothing

End Function

If i want start it, i get the "Microsoft Visual Basic" failu

Fehler beim Kompilieren:
Benutzerdefinierter Type nicht definiert

in English:
failure at compilation
user defined type is not defined

What's wrong here ?

Kind regards

Holger

Which line is highlighted when the code fails?

Dan

On Fri, 11 Apr 2008 14:48:18 +0200, "Holger Hasenmüller"
wrote:


Dim objReg As RegObj.Registry
Dim objRootKey As RegObj.RegKey

in English:
failure at compilation
user defined type is not defined

What's wrong here ?


You need to set a reference to Registration Manipulation Classes (Tools -
References). Or use CreateObject("RegObj.Registry") and dim those variables
as Object.
--
Dick

Im using another simple solution, by creating a reference to Word and use
the System.PrivateProfileString to read the registry key:

Sub findPDFport ()
Dim WordApp As Word.Application
Dim strSection As String
Dim strAdobePrt As String
Dim pdfPrinter As String
strSection = "HKEY_CURRENT_USER\Software\Microsoft" _
& "\Windows NT\CurrentVersion\Devices"
strAdobePrt = System.PrivateProfileString(Filename:="", _
Section:=strSection, Key:="Adobe PDF")
pdfPrinter = "Adobe PDF on " & Right(strAdobePrt, 5)
MsgBox "The Adobe Printer is on - " & Right(strAdobePrt, 5)
End Sub

with this solution youre able to get the NE-port which seems to change all
the time,
probably when you do some changes to ANY printer setup, its re-indexing.
Theres some additional code if youd like to create a PDF-file from your
printout but I guess you have that already.

Best regards
Mats


"Dick Kusleika" wrote:

On Fri, 11 Apr 2008 14:48:18 +0200, "Holger Hasenmüller"
wrote:


Dim objReg As RegObj.Registry
Dim objRootKey As RegObj.RegKey

in English:
failure at compilation
user defined type is not defined

What's wrong here ?


You need to set a reference to Registration Manipulation Classes (Tools -
References). Or use CreateObject("RegObj.Registry") and dim those variables
as Object.
--
Dick

Hello Dick, hello Mats,

thank you for your "good ideas", but i'm not a professional scripter, so i
have i few problems.

@Dick:
I have understand, da i must make a reference, like you write here
http://www.dailydoseofexcel.com/arch...-printer-port/
But what i do not know, is how i get the windows under the text "To overcome
that problem, you can use Registration Manipulation Classes".
Is this a window in the IDE from Excel (Alt+F11), or where ca i run it
(please say it me detailed ;-)

@Mats:

If i run your script, the debugger stop at "WordApp As Word.Application" and
the line "Sub findPDFport()" is marked yellow.
What do i wrong ?


Kind regards

Holger

Hello Holger,
sorry my solution was not complete!
First you create the reference to Word with selecting Tools-References in
the VB Editor, scroll down and mark "Microsoft Office 11.0 Object Library".
This is why your execution stops, there's no reference to Word.
Then you use the following code:

Sub findPDFport ()
Dim WordApp As Word.Application
Dim strSection As String
Dim strAdobePrt As String
Dim pdfPrinter As String
Set WordApp = New Word.Application
strSection = "HKEY_CURRENT_USER\Software\Microsoft" _
& "\Windows NT\CurrentVersion\Devices"
strAdobePrt = System.PrivateProfileString(Filename:="", _
Section:=strSection, Key:="Adobe PDF")
pdfPrinter = "Adobe PDF on " & Right(strAdobePrt, 5)
MsgBox "The Adobe Printer is on - " & Right(strAdobePrt, 5) ' ***for viewing
the result only***
€¦your other code goes here

WordApp.Quit
Set WordApp = Nothing
End Sub

Please observe the lines, the first Set WordApp.... and later WordApp.Quit +
Set WordApp..... This is the start and stop of your Word instance that
enables Excel to communicate and use Word as a part of the procedure and
exits Word when it's finished.
"Holger Hasenmüller" wrote:

Hello Dick, hello Mats,

thank you for your "good ideas", but i'm not a professional scripter, so i
have i few problems.

@Dick:
I have understand, da i must make a reference, like you write here
http://www.dailydoseofexcel.com/arch...-printer-port/
But what i do not know, is how i get the windows under the text "To overcome
that problem, you can use Registration Manipulation Classes".
Is this a window in the IDE from Excel (Alt+F11), or where ca i run it
(please say it me detailed ;-)

@Mats:

If i run your script, the debugger stop at "WordApp As Word.Application" and
the line "Sub findPDFport()" is marked yellow.
What do i wrong ?


Kind regards

Holger

On Fri, 18 Apr 2008 14:09:34 +0200, "Holger Hasenmüller"
wrote:


@Dick:
I have understand, da i must make a reference, like you write here
http://www.dailydoseofexcel.com/arch...-printer-port/
But what i do not know, is how i get the windows under the text "To overcome
that problem, you can use Registration Manipulation Classes".
Is this a window in the IDE from Excel (Alt+F11), or where ca i run it
(please say it me detailed ;-)


From the VBA IDE (Alt+F11), choose Tools - References. Scroll down to
Registration Manipulation Classes and put a check mark next to it.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Hi Mats,

OK, i have done it like you write, but in VB Editor the "Microsoft Office
11.0 Object Library" was checked. Also the "Microsoft Excel 11.0 Object
Library" was checked. So i scrolled down and found a "Microsoft Word 11.0
Object Library", which was unchecked.
I checked that also on.

Now i run my first test. I'm waiting, and waiting, and waiting ... and than
i get an messagebox output. I saw that you write "Key:="Adobe PDF")", so i
changed it to "Key:="\\srv01.abc.local\ADOBE-PDF")
But if i run that, i get an Error Message: Runtime Error 462 - The
remote-server-computer does not exist or is not available

After that i run the Script with F8.

After "strAdobePrt = System.PrivateProfileString(Filename:="", _
Section:=strSection, Key:="\\srv01.abc.local\ADOBE-PDF")"
i get the error ;-(

And now ???

The winword.exe process is always running, but that is OK, because the
script does not run to the end.

Kind regards

Holger

Hi Dick,

after a little bit google'n i found the regobj.dll at microsoft.com.

Now i runs :-)

Kind regards

Holger