Use sendkeys to run a macro code RRS feed

  • Question

  • I want to make use of sendkey function in excel to change the system printer while running another application, i.e.

    While excel is minimized and the other application is ruining, press F9 sets the printer to printer1, and if Alt+S sets to printer2

     can this be achieved?

    Thank you

    Saturday, August 9, 2014 10:44 PM

All replies

  • It probably could be done but SendKeys for this is likely to be unrepliable. But why not change the printer programatically. If you know the full name you can simply use 

    Application.ActivePrinter = "my preferred printer Ne03"
    You can return a list of installed printers from the registry, then use the ActivePrinterFunction, or 
    ' with a reference to Windows Script Host
    Dim oWHS As WshNetwork
        Set oWHS = New WshNetwork 
    ' or without a reference
    Dim oWHS as Object
        Set oWHS = CreateObject("WScript.Network")
        Set col = oWHS.EnumPrinterConnections
        For i = 1 To col.Count
            Debug.Print col(i - 1)
    ' and to set the default printer
    oWHS.SetDefaultPrinter = "my printer"

    Sunday, August 10, 2014 11:29 PM
  • Thank you Peter for your reply, the thing is I need to change the system printer just after the F9 or ALT+S is pressed. So I do not know how to perform this action with the code you posted.

    Monday, August 11, 2014 5:52 AM
  • Is your main question about how to trap when user hits F9 or Alt-S so that you can then run other code, in this case change the printer?

    Did you try my suggestions about how to change the printer?

    Monday, August 11, 2014 9:26 AM
  • Yes Peter, that was the question, I know the code 

    Application.ActivePrinter = "my preferred printer Ne03"

    will change the printer, but I do not know how to write an if statement with sendkeys

    if sendkey = f9 then 

    Application.ActivePrinter = "my preferred printer Ne03"

    end if

    if sendkey = Alt+s then

    Application.ActivePrinter = "my preferred printer2"

    end if

    or is there a code to test if f9 is pressed then do so and so....

    Thank you

    Monday, August 11, 2014 9:45 AM
  • Assign an OnKey procedure, eg

    Sub StartF9()
        Application.OnKey "{F9}", "ProcF9"
    End Sub
    Sub CancelF9()
        Application.OnKey "{F9}", ""
    End Sub
    Sub ProcF9()
        MsgBox "User pressed F9"
        'perform default F9 action ....?
       ' Application.ActivePrinter = "my printer"
    End Sub
    Be sure to cancel the OnKey when not required, eg the workbook is deavtivated or closed. See OnKey in Help

    Monday, August 11, 2014 10:25 AM
  • Thank you Peter, onKey is nice, but unfortunately, it needs excel to be maximized or active window, and as I asked I need to run the code while excel is inactive window, sure impossible.

    Thank you again.

    Wednesday, August 13, 2014 1:59 PM
  • You are asking for Excel to receive a keyboard input when it is not the active application(?)

    Actually it's not impossible, you can subclass windows messages to trap the keyboard no matter which app has focus. However it's a bad idea for at least two reasons - firstly in VBA due to the callback type code, if not handled correctly (eg user interrupts in the VBE) Excel will crash without warning, and secondly how do you know the given keyboard input is not expected by whatever app has focus.

    Wednesday, August 13, 2014 4:25 PM
  • Actually it's not impossible, 

    Interesting! How then? Lets try. Can you post a code please.

    Wednesday, August 13, 2014 7:30 PM
  • Actually it's not impossible, 

    Interesting! How then? Lets try. Can you post a code please.

    Wednesday, August 13, 2014 7:30 PM
  • I don't think anyone will provided such a code for your, as Peter said, though it is possible, but it is not a recommanded idea and it have some issues.

    Friday, August 15, 2014 8:41 AM
  • I've been away a couple of days, did you find some examples and got it working in the meantime.

    Friday, August 15, 2014 12:25 PM
  • Welcome back Peter. I searched about subclassing in excel but I did not find enough examples. I'll try to find more and post it here.

    Thank you.

    Friday, August 15, 2014 8:34 PM
  • In a quick search I couldn't find any examples that will work with VBA. I could put something together to trap those key presses while the app is not active but am hesitant for the reasons I explained and as echoed by churchencool, if not used correctly problems may occur. Explain why you want to do this, maybe there is a different approach.
    Sunday, August 17, 2014 11:17 AM
  • I want to switch between printer because when working with the other application pressing Alt+S (Save) generate a serial number that will print on low quality paper, and when press F9 it should print on other high quality paper to be attached on the final product, I want this process to run silently. Sometimes I need to print the serial number on one printer, and print the product label on another printer in the workstation inside next room.

    We are not using these keys Alt + S or F9 too much outside the application, also if the excel file is closed nothing will happen, also we can set the default printer when excel close.

    I also did not find an example for such things with subclassing or onkey events.

    Sunday, August 17, 2014 1:29 PM
  • Obvious question, what is the other application and can it trap the keyboard (while active) and in turn change your printer (or can it automate Excel and do the same thing)? If not, to keep things simple, if you are educating the user to press F9/Alt-S is there any reason the user can't also first activate Excel?
    Sunday, August 17, 2014 4:01 PM
  • It is C++ based application. There are a lot of keyboard shortcuts Als+S, Alt+A, Alt+C, ..... I can not modify that application. It also can not automate Excel. There is no reason prevents the user to activate Excel, also all user are aware of what F9 do and Alt+S do. It will be user's choice to work between 2 different printers or to use a single printer. So if the fiest choice, it will be great. Will Excel excel in doing this?

    Sunday, August 17, 2014 6:38 PM