Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Selecting another network printer vba - Office 365
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Selecting another network printer vba - Office 365

    Hello, everything that I can find on this is saying I have the correct code, but is still not working. I recorded the marco to select the printer, so I am assuming that is correct. Any help would be great. This is what I have;

    Sub CHANGE_PRINTER_ONE()
    Dim sCurrentPrinter As String
    sCurrentPrinter = Application.ActivePrinter

    Application.ActivePrinter = "\\IAN1516\Brother HL-2240 series LV Cell on Ne06:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "\\IAN1516\Brother HL-2240 series LV Cell on Ne06:", Collate:=True

    Application.ActivePrinter = sCurrentPrinter
    End Sub

  2. #2
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selecting another network printer vba - Office 365

    I'm no expert at all on this, but what's "...still not working...?"
    Is the code bugging out, just not doing anything, not printing to the desired printer, or what?


    Two observations:
    1. When I run
    Code:
    debug.print Application.ActivePrinter
    ... I only get the printer name, and port, not the server name as per your code. It should be noted that I'm not in a network environment, and I guess that you are, so this is perhaps, why.

    2. Also, I don't think there's any need for the separate line:
    Code:
    Application.ActivePrinter = "\\IAN1516\Brother HL-2240 series LV Cell on Ne06:"
    ... as the printer's being set later in your code, anyway.

    To be honest, I don't think it's going to make any difference, but it may be worth trying:
    Code:
    Sub CHANGE_PRINTER_ONE()
    Dim sCurrentPrinter As String
    sCurrentPrinter = Application.ActivePrinter
    
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "\\IAN1516\Brother HL-2240 series LV Cell on Ne06:", Collate:=True
    
    Application.ActivePrinter = sCurrentPrinter
    End Sub
    ... and as a last resort, perhaps ditch the server name, too:
    Code:
    Sub CHANGE_PRINTER_ONE()
    Dim sCurrentPrinter As String
    sCurrentPrinter = Application.ActivePrinter
    
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "Brother HL-2240 series LV Cell on Ne06:", Collate:=True
    
    Application.ActivePrinter = sCurrentPrinter
    End Sub
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    New Member
    Join Date
    Nov 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selecting another network printer vba - Office 365

    Sorry about that, I am getting a Run-time error 1004, Method 'ActivePrinter' of object'_Application' failed.

    I tired both of your suggestions and it is printing to my printer, not the Brother 2240.

  4. #4
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selecting another network printer vba - Office 365

    Mmm
    I'm all out of ideas, I'm afraid. Hopefully, this will have pushed your post back to the top, so that someone else may be able to help.
    All the best
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  5. #5
    New Member
    Join Date
    Nov 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selecting another network printer vba - Office 365

    Thanks Sykes

  6. #6
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selecting another network printer vba - Office 365

    This has been bugging me (pardon the pun!).

    I've done some research, and the only other thing I can come up with, is that Windows appears to regularly change the numbered suffix for a printer. I'm not sure why, or when, but it's not constant - that's for sure.
    I don't know whether this is only when on a network, or at each user login, but the port number's definitely not static.

    When you did your original post, you obtained the full printer name by using a macro, and (understandably) hard-coded the name into your procedure.
    There seem to be several methods of obtaining the current port numbers - including going into the registry - but I'm suggesting the following method, as the most simple one I've found, which might (?) achieve your goal. This particular one comes from Juan Pablo Gonzalez - from this website, but there are quite a few variations on a theme, out there.

    The idea seems to be to iterate through the likely printer ports (appending them to the name of the printer you're trying to access), until the code doesn't error, and Bob's your uncle - you've found the CURRENT port for that printer!

    Code:
    Sub CHANGE_PRINTER_ONE()
    Dim sCurrentPrinter As String
    sCurrentPrinter = Application.ActivePrinter
    
    Dim intgr As Integer
    
        For intgr = 0 To 16
           On Error Resume Next
            Application.ActivePrinter = "\\IAN1516\Brother HL-2240 series LV Cell on Ne" & Format$(intgr, "00") & ":"
           'Debug.Print "Active printer is " & Application.ActivePrinter & " Integer is now: " & Format$(intgr, "00") & " Error number is now: " & Err.Number
        
           If Err.Number = "0" Then Exit For
        Next intgr
    
    If Not Application.ActivePrinter Like "\\IAN1516\Brother HL-2240 series LV Cell on Ne*" Then
        MsgBox "Unable to set the printer", vbCritical
        Exit Sub
    End If
    
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Application.ActivePrinter = sCurrentPrinter
    End Sub
    I've made several changes to both his original code, and yours - to make it pertinent to your scenario.

    Hopefully, this might just work...
    Do tell!
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  7. #7
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selecting another network printer vba - Office 365

    Did this work for you?
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  8. #8
    New Member
    Join Date
    Nov 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selecting another network printer vba - Office 365

    Sykes, I am getting "Unable to set the printer". I am going to see how they have the printed networked. Something is weird.

  9. #9
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selecting another network printer vba - Office 365

    Probably a silly question, but is that particular printer actually on line, at the moment?
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  10. #10
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,641
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Selecting another network printer vba - Office 365

    Don't iterate printer ports, you can read it from the registry very easily:

    Code:
    Public Function GetPrinterPort(strPrinterName As String) As String
       Dim objReg As Object, strRegVal As String, strValue As String
       Const HKEY_CURRENT_USER = &H80000001
       Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
       strRegVal = "Software\Microsoft\Windows NT\CurrentVersion\PrinterPorts\"
       objReg.getstringvalue HKEY_CURRENT_USER, strRegVal, strPrinterName, strValue
       GetPrinterPort = Split(strValue, ",")(1)
    End Function

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •