Problem with this VBA Code and Network Printers (UNC Path)
Holger Hasenmüller
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
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
> 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
<hhasen...@fum.de> wrote:
>"Dick Kusleika" <dkus...@gmail.com> schrieb
>
>> See here
>>
>> http://www.dailydoseofexcel.com/archives/2008/04/05/getting-the-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
Holger Hasenmüller
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
Dan
Dick Kusleika
<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
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
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
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
<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
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
after a little bit google'n i found the regobj.dll at microsoft.com.
Now i runs :-)
Kind regards
Holger