[Solved] Trying to send keys to Excel 2016

[ocnuybear]

I have used the "Send Keys To Object Wizard" with the cross which gave many different window names within Excel

Code: Select all

//GetWindowHandle>Data Management Report Oct2017-Mar2018.xlsx - Excel,hWndParent
//FindObject>hWndParent,Edit,,1,hWnd,X1,Y1,X2,Y2,result

//GetWindowHandle>Data Management Report Oct2017-Mar2018.xlsx - Excel,hWndParent
//FindObject>hWndParent,NetUIHWND,,2,hWnd,X1,Y1,X2,Y2,result

//GetWindowHandle>Data Management Report Oct2017-Mar2018.xlsx - Excel,hWndParent
//FindObject>hWndParent,EXCEL7,,1,hWnd,X1,Y1,X2,Y2,result

//GetWindowHandle>Data Management Report Oct2017-Mar2018.xlsx - Excel,hWndParent
//FindObject>hWndParent,EXCEL<,,1,hWnd,X1,Y1,X2,Y2,result

ObjectSendKeys>hWnd,ALT_DN,ALT_UP,o,h,u

But it is not responding.

ALT+O+H+U is to unhide a hidden sheet

[ocnuybear]

This works, but it does not respond to the ENTER when the Unhide window pops up:

Code: Select all

SetFocus>Data Management Report Oct2017-Mar2018.xlsx - Excel
Wait>1
Press ALT
Wait>1
Send>ohu
Wait>1
SetFocus>Unhide
Wait>1
Press ENTER

Setting Focus on the Unhide window is also not working.

[ocnuybear]

Using only VBScript, I can unhide the window:

Code: Select all

Set oShell = CreateObject("WScript.Shell")
oShell.AppActivate "Data Management Report Oct2017-Mar2018.xlsx - Excel"
oShell.SENDKEYS("%")
oShell.SENDKEYS("O")
oShell.SENDKEYS("H")
oShell.SENDKEYS("U")
oShell.SENDKEYS("{ENTER}")

But putting it in Macro Scheduler is not working:

Code: Select all

SetFocus>Data Management Report Oct2017-Mar2018.xlsx - Excel
Wait>1
VBSTART
Function Sendkeys1 ()
Set oShell = CreateObject("WScript.Shell")
oShell.AppActivate "Data Management Report Oct2017-Mar2018.xlsx - Excel"
oShell.SENDKEYS("%")
oShell.SENDKEYS("O")
oShell.SENDKEYS("H")
oShell.SENDKEYS("U")
oShell.SENDKEYS("{ENTER}")
End Function
VBEND
VBRun>Sendkeys1

[ocnuybear]

Back to Macro Scheduler, using:

Code: Select all

WindowAction>1,Data Management Report Oct2017-Mar2018.xlsx - Excel
SetFocus>Data Management Report Oct2017-Mar2018.xlsx - Excel
Wait>1
Press ALT
Wait>1
Send>ohu

Works great and to push the OK button using ENTER on Unhide you need to add which also works on its own:

Code: Select all

SetFocus>Unhide
WaitWindowOpen>Unhide
Wait>1
Press Enter

Which will push the button to unhide the sheet, but using the both the scripts:

Code: Select all

WindowAction>1,Data Management Report Oct2017-Mar2018.xlsx - Excel
SetFocus>Data Management Report Oct2017-Mar2018.xlsx - Excel
Wait>1
Press ALT
Wait>1
Send>ohu
Wait>1


SetFocus>Unhide
WaitWindowOpen>Unhide
Wait>1
Press Enter

Does not press the OK button on Unhide screen, why?

[ocnuybear]

I have used a VBscript code without sending any keys within Macro Scheduler & it is working now:

Code: Select all

VBSTART
  const xlDown = -4121
  Dim xlApp
  Dim xlBook
  Sub GetXL
    Set xlApp = GetObject(,"Excel.Application")
    Set xlBook = xlApp.ActiveWorkbook
  End Sub
  Function DoSomething()
    xlApp.Visible = True
    xlBook.Sheets("(template)").Visible = True
    xlBook.Sheets("(template)").Select
    xlApp.Range("O17").End(xlDown).Offset(1,0).Select

  End Function
VBEND
//Open an XLS file natively
XLOpen>D:\Data Management Report Oct2017-Mar2018.xlsx,1,xlH
//Call GetXL to give VBScript a reference to the XL instance
VBRun>GetXL
//now we can access any XL function via VBScript
VBEval>DoSomething(),res

Sending keys not so reliable here it seems

[Marcus Tettmar]

Excel is very programmable so it should never be necessary to send keys to it. That said, it should be possible to do so. The important thing is you have a solution.