Problem with this VBA Code and Network Printers (UNC Path)

798 views
Skip to first unread message

Holger Hasenmüller

unread,
Apr 3, 2008, 8:29:23 AM4/3/08
to
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_CURRENT_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
 
 

Dick Kusleika

unread,
Apr 5, 2008, 12:14:17 PM4/5/08
to
On Thu, 3 Apr 2008 14:29:23 +0200, Holger Hasenmüller <hhasen...@fum.de>
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/archives/2008/04/05/getting-the-printer-port/
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Holger Hasenmüller

unread,
Apr 7, 2008, 5:43:14 AM4/7/08
to
"Dick Kusleika" <dkus...@gmail.com> schrieb

> See here
>
> http://www.dailydoseofexcel.com/archives/2008/04/05/getting-the-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


Dick Kusleika

unread,
Apr 7, 2008, 6:55:25 PM4/7/08
to
On Mon, 7 Apr 2008 11:43:14 +0200, "Holger Hasenmüller"
<hhasen...@fum.de> wrote:

>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

Holger Hasenmüller

unread,
Apr 11, 2008, 8:48:18 AM4/11/08
to
Hi Dick,

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

Sub GetPrinterPortName()
Const strPrinter As String = "\\srv01.huk.local\ADOBE-PDF"

strPort = GetPrinterPort(strPrinter)

End Sub

Set objReg = Nothing

End Function

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

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


dan dungan

unread,
Apr 11, 2008, 12:27:20 PM4/11/08
to
Which line is highlighted when the code fails?

Dan

Dick Kusleika

unread,
Apr 13, 2008, 10:28:56 AM4/13/08
to
On Fri, 11 Apr 2008 14:48:18 +0200, "Holger Hasenmüller"
<hhasen...@fum.de> 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

Mats Samson

unread,
Apr 15, 2008, 6:56:02 AM4/15/08
to
I’m 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 you’re able to get the NE-port which seems to change all
the time,
probably when you do some changes to ANY printer setup, it’s re-indexing.
There’s some additional code if you’d like to create a PDF-file from your
printout but I guess you have that already.

Best regards
Mats

Holger Hasenmüller

unread,
Apr 18, 2008, 8:09:34 AM4/18/08
to
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/archives/2008/04/05/getting-the-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


Mats Samson

unread,
Apr 18, 2008, 11:25:02 AM4/18/08
to
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.

Dick Kusleika

unread,
Apr 21, 2008, 12:49:16 PM4/21/08
to
On Fri, 18 Apr 2008 14:09:34 +0200, "Holger Hasenmüller"
<hhasen...@fum.de> wrote:

>
>@Dick:
>I have understand, da i must make a reference, like you write here
>http://www.dailydoseofexcel.com/archives/2008/04/05/getting-the-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.

Holger Hasenmüller

unread,
Apr 21, 2008, 5:36:35 PM4/21/08
to
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


Holger Hasenmüller

unread,
Apr 21, 2008, 5:55:00 PM4/21/08
to
Hi Dick,

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

Now i runs :-)

Kind regards

Holger


Reply all
Reply to author
Forward
0 new messages