none
EXCEL, VBA, Registry and "\\" in value string RRS feed

  • Question

  • Hello everyone.

    I have a workbook with 7 sheets, in which I do my company accounts.  

    I need to print sections of each sheet each month.  Because of the sheet content, some need printing on A3 paper and others on A4.

    My task is to write a VBA program which will automatically print all reports to the correct printer.

    The printers are all accessible from my PC, but remote on the network.

    Printers use Nexx addressing and the Ne numbers change from system boot to system boot.

    I am attempting to get a list of printers on my network.  I am using the values found in:

     "HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\Devices\

    All was going well until I came to an entry:  \\RUFUS\Brother MFC-620CN Printer

    The \\ at the start just kills my code.

    Run-Time error '-2147024894(80070002)':

    Invalid root in registry key.

    Simplified code extract:

    Sub Test()

    Dim ws as Object

    Dim np as String

    Set wshNetwork = CreateObject("WScript.Network")
        Set ws = CreateObject("WScript.Shell")

            RegKey = "HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\Devices\\\RUFUS\Brother MFC-620C Printer"

    np = ws.RegRead(RegKey)

    End Sub

    Does anyone have any idea how to get around this?  I can't be the only one who has hit this, can I??????

    Many thanks

    John

    Live long, prosper and try to be less confused!

    Monday, November 12, 2012 9:41 AM

All replies

  • See the section "Print to a suitable available printer e.g. a colour printer" at http://www.gmayor.com/fax_from_word.htm  and in particular the note in the yellow panel.

    You may also find http://www.gmayor.com/Associate_Printer.htm useful.


    Graham Mayor - Word MVP
    www.gmayor.com

    Monday, November 12, 2012 10:35 AM
  • Hello Graham,

    Thanks for your speedy response.  I have had a look at the links that you mention, but they don't resolve my problem.

    Whilst I know that the printers that I need to print to are called, say, HP Color LaserJet 2600nBrother MFC-5895CW Printer and \\RUFUS\Brother MFC-620CN Printer, I can't access them without knowing the Ne number.  

    For example, I use the command  Application.ActivePrinter = "Brother MFC-5895CW Printer on Ne07:" to select my A3 printer

    However, each time I boot my PC, the Ne address of Brother MFC-5895CW Printer changes.

    If I run my macro today, I might use Ne06.  tomorrow it could be Ne08.  I have no idea why it changes, all I know is that my macro fails and changing the Ne address gets it running again.

    So,.... I figure that if I can read the Ne address from the registry, then I should be able to access the printer.

    I can access the Ne address from the registry entries for all my printers, except the ones that are prefixed with \\.

    This gives a string:  

    "HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\Devices\\\RUFUS\Brother MFC-620CN Printer"

    As you can see, there are three \ characters and presumably, the registry access function is getting confused.

    Does any of this make sense. or am I just writing gibberish?

    Many thanks

    John

    Live long, prosper and try to be less confused!

    Monday, November 12, 2012 11:30 AM
  • I guess the \\ fails with WScript.Network fails because as an entire string there's no path. I tried various ways to "escape" the backslashes but didn't work, no doubt there's a simple fix.

    However in a quick test no problem with the Registry APIs.

    Peter Thornton

    Monday, November 12, 2012 3:02 PM
    Moderator
  • Using Astrid's function linked from my web page, are you saying that the following does not print your document to the Brother printer?

    Sub PrintBrother()
    Dim vPrinters As Variant
    Dim i As Long
    Dim sPrinter As String
        vPrinters = ListPrinters
        sPrinter = ActivePrinter
        If IsBounded(vPrinters) Then
            For i = LBound(vPrinters) To UBound(vPrinters)
                If InStr(UCase(vPrinters(i)), "Brother MFC-5895CW") Then
                    ActivePrinter = vPrinters(i)
                    GoTo PrintDoc
                End If
            Next i
            MsgBox "Brother printer not available"
        Else
            MsgBox "No printers found"
        End If
        Exit Sub
    PrintDoc:
        ActiveDocument.PrintOut
        ActivePrinter = sPrinter
    End Sub


    Graham Mayor - Word MVP
    www.gmayor.com

    Monday, November 12, 2012 3:14 PM
  • I forgot to ask, what happens if you simply do

    ActiveSheet.PrintOut ActivePrinter:="\\RUFUS\Brother MFC-620CN Printer"

    Peter Thornton

    Monday, November 12, 2012 3:42 PM
    Moderator