Excel_Get
Re: Excel_Get
This method can automatically get the correct control:
Code: Select all
F1:: WordObj:=Word_Get("A")
Word_Get(WinTitle="ahk_class OpusApp")
{
return Office_Get(WinTitle)
}
Ppt_Get(WinTitle="ahk_class PP12FrameClass")
{
return Office_Get(WinTitle)
}
Excel_Get(WinTitle="ahk_class XLMAIN")
{
return Office_Get(WinTitle)
}
Office_Get(WinTitle="")
{
static h:=DllCall("LoadLibrary", "Str","oleacc", "Ptr")
WinGet, list, ControlListHwnd, % WinTitle ? WinTitle : "A"
For i,hWnd in StrSplit(list, "`n")
{
ControlGetPos, x, y, w, h,, ahk_id %hWnd%
if (y<10 or w<100 or h<100)
Continue
n:=Acc:=""
if DllCall("oleacc\AccessibleObjectFromWindow", "Ptr", hWnd
, "UInt", 0xFFFFFFF0, "Ptr", 0*(VarSetCapacity(IID,16)
+NumPut(0x0000000000020400,IID,"Int64")
+NumPut(0x46000000000000C0,IID,8,"Int64"))+&IID, "Ptr*", pacc)=0
Acc:=ComObject(9, pacc, 1)
Try n:=Acc.Application.Version
if (n)
return Acc.Application
}
MsgBox, 4096,, Error: Can't Get Object From ACC !
Exit
}
- Micromegas
- Posts: 260
- Joined: 28 Apr 2015, 23:02
- Location: Germany
Re: Excel_Get
Back to Excel: The version from https://raw.githubusercontent.com/ahkon/MS-Office-COM-Basics/master/Examples/Excel/Excel_Get.ahk gives me an exception at (with Excel 12.0.6787, SP3 MSO 12.0.6785.5000 under Windows 10 Pro 10.0.19041).
At first I thought it might have to do with the hard coded "Excel7", but the control "Excel71" still exists; AU3_Spy shows it at the left and right lower portions of the frame.
Code: Select all
ControlGet, hwnd, hwnd,, Excel7%Excel7#%, %WinTitle%
At first I thought it might have to do with the hard coded "Excel7", but the control "Excel71" still exists; AU3_Spy shows it at the left and right lower portions of the frame.
- Micromegas
- Posts: 260
- Joined: 28 Apr 2015, 23:02
- Location: Germany
Re: Excel_Get
Any news on this? It's a pity this doesn't seem to be working for Excel anymore, of all apps.
As a workaround, I am using the below code , which opens the macro dialog (bosa_sdm_XL9) and fills the Macro name edit box (EDTBX1), but that's only a cludge at best; of course it would be much better to use the same object oriented code, which works so nicely for other apps.
As a workaround, I am using the below code , which opens the macro dialog (bosa_sdm_XL9) and fills the Macro name edit box (EDTBX1), but that's only a cludge at best; of course it would be much better to use the same object oriented code, which works so nicely for other apps.
Code: Select all
Send !l ; to open the Excel Developer ribbon
Sleep 100
Send pm ; to open the Macro dialog
WinWaitActive Macro ahk_class bosa_sdm_XL9, , 3
if ErrorLevel ; if timeout
{
MsgBox, Couldn't activate the window for running Excel macro “%sMacroname%”.
return
}
Control, EditPaste, %sMacroname% , EDTBX1
Send !r ; Run button
Return
Re: Excel_Get
run it with DetectHiddenWindows OnMicromegas wrote: ↑26 Mar 2021, 07:05Back to Excel: The version from https://raw.githubusercontent.com/ahkon/MS-Office-COM-Basics/master/Examples/Excel/Excel_Get.ahk gives me an exception at(with Excel 12.0.6787, SP3 MSO 12.0.6785.5000 under Windows 10 Pro 10.0.19041).Code: Select all
ControlGet, hwnd, hwnd,, Excel7%Excel7#%, %WinTitle%
At first I thought it might have to do with the hard coded "Excel7", but the control "Excel71" still exists; AU3_Spy shows it at the left and right lower portions of the frame.
- Micromegas
- Posts: 260
- Joined: 28 Apr 2015, 23:02
- Location: Germany
Re: Excel_Get
Thank you for your reply. That would have been a great fix, but unfortunately, that doesn't do the trick - I still get an exception.
BTW, by now I am using a different workaround for many cases: Define a hotkey that is unlikely to be used, such as +^!{F13} for the macro (sub) and send that from AHK. The advantage is that that doesn't open a dialog, but it doesn't allow passing parameters to the sub.
Here's the code I tried. It's not much different from the code shown in this thread before, but all in one piece:
Code: Select all
#NoEnv ; Recommended for performance and compatibility with future AutoHotkey releases.
#SingleInstance force
#Warn
DetectHiddenWindows On
Run_macro("!foo")
return
Run_Macro(sMacroname) {
local ; was local oApp
WinActivate Microsoft Excel ahk_class XLMAIN
try {
oApp := Get_ComObj("Excel")
oApp.Run("Personal.xlsb!" . sMacroname)
}
catch e {
MsgBox % "Run_Macro throwing an exception."
. "`nWhat: ––––––––––––––––––––"
. "`n" e.What
. "`nMessage: ––––––––––––––––––––"
. "`n" e.Message
}
Return
}
Get_ComObj(sApp) {
; call to Excel_Get with some error handling
o:= Excel_Get()
if !IsObject(o) ; If Excel_Get fails it returns an error message instead of an object.
{
MsgBox, 16, Get_ComObj can't retrieve app, % o
return
}
Return o
return
}
Excel_Get(WinTitle:="ahk_class XLMAIN", Excel7#:=1) {
DetectHiddenWindows On
static h := DllCall("LoadLibrary", "Str", "oleacc", "Ptr")
WinGetClass, WinClass, %WinTitle%
if !(WinClass == "XLMAIN")
return "Window class mismatch."
ControlGet, hwnd, hwnd, , , Excel7%Excel7#%, %WinTitle%
if (ErrorLevel)
return "Error accessing the control hWnd=" hWnd "."
VarSetCapacity(IID_IDispatch, 16)
NumPut(0x46000000000000C0, NumPut(0x0000000000020400, IID_IDispatch, "Int64"), "Int64")
pacc := -1
if DllCall("oleacc\AccessibleObjectFromWindow", "Ptr", hWnd, "UInt", -16, "Ptr", &IID_IDispatch, "Ptr*", pacc) != 0
return "Error calling AccessibleObjectFromWindow."
if (pacc=-1)
return "Error: Parameter pacc not changed."
window := ComObject(9, pacc, 1)
if ComObjType(window) != 9
return "Error wrapping the window object."
Loop
try return window.Application
catch e
if SubStr(e.message, 1, 10) = "0x80010001"
ControlSend, Excel7%Excel7#%, {Esc}, %WinTitle%
else
return "Error accessing the application object."
}
Re: Excel_Get
I am using Excel_Get to first detect if an active instance of Excel exists.
If it does not, I use ComObjCreate to create an instance of Excel.
This works perfectly — well almost.
An instance of Excel created this way does not automatically open the workbooks in the XLSTART folder.
Is there any way to get ComObjCreate to force those workbooks to open?
If it does not, I use ComObjCreate to create an instance of Excel.
Code: Select all
coxl := Excel_Get() ;get a com object pointing to an open excel instance
If !IsObject(coxl) {
If (sysCfg.Debug >= dbgInfo)
OutputDebug % Format("{}.{} coxl {}",sScript,(A_ThisFunc = "" ? (A_ThisLabel = "" ? "Main" : A_ThisLabel) : A_ThisFunc) . "." . A_LineNumber, "Did not exist prior")
coxl := ComObjCreate("Excel.Application") ;if it isn't there, open excel
coxl.Visible := true ;and make it visible
}
An instance of Excel created this way does not automatically open the workbooks in the XLSTART folder.
Is there any way to get ComObjCreate to force those workbooks to open?
Re: Excel_Get
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory
Re: Excel_Get
I have Office 2021 if that makes any difference. In my case, using workbooks.add() only opens a blank unnamed workbook — but still no startup files.Xeo786 wrote: ↑17 Jan 2023, 23:34just add this line, workbooks.addCode: Select all
coxl.workbooks.Add()
This did work:
Code: Select all
owp := coxl.Workbooks.Open(coxl.StartupPath . "\Personal.xlam")
Re: Excel_Get
@Meroveus, thanks, that's good information (I've got Office 2021 also). It seems to me that if there's a way to do what you want in VBA you can do it with AHK. Do you by chance have a VBA solution for what you're wanting to accomplish?
Regards,
burque505
Regards,
burque505
-
- Posts: 11
- Joined: 17 Nov 2022, 09:13
Re: Excel_Get
@Frasier it was very useful for me, thank you!