Excel_Get

Post your working scripts, libraries and tools for AHK v1.1 and older
burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: Excel_Get

Post by burque505 » 05 Dec 2020, 15:22

@Frasier, just FYI: Word_Get.ahk
Regards,
burque505

feiyue
Posts: 349
Joined: 08 Aug 2014, 04:08

Re: Excel_Get

Post by feiyue » 06 Dec 2020, 00:52

This method can automatically get the correct control: :dance: :beer:

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
}


User avatar
Micromegas
Posts: 260
Joined: 28 Apr 2015, 23:02
Location: Germany

Re: Excel_Get

Post by Micromegas » 26 Mar 2021, 07:05

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

Code: Select all

ControlGet, hwnd, hwnd,, Excel7%Excel7#%, %WinTitle%
(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.

User avatar
Micromegas
Posts: 260
Joined: 28 Apr 2015, 23:02
Location: Germany

Re: Excel_Get

Post by Micromegas » 22 May 2021, 06:39

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.

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

swagfag
Posts: 6222
Joined: 11 Jan 2017, 17:59

Re: Excel_Get

Post by swagfag » 17 Jan 2022, 16:05

Micromegas wrote:
26 Mar 2021, 07:05
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

Code: Select all

ControlGet, hwnd, hwnd,, Excel7%Excel7#%, %WinTitle%
(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.
run it with DetectHiddenWindows On

User avatar
Micromegas
Posts: 260
Joined: 28 Apr 2015, 23:02
Location: Germany

Re: Excel_Get

Post by Micromegas » 18 Jan 2022, 18:18

swagfag wrote:
17 Jan 2022, 16:05
run it with DetectHiddenWindows On
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."
}

Meroveus
Posts: 44
Joined: 23 May 2016, 17:38

Re: Excel_Get

Post by Meroveus » 17 Jan 2023, 15:21

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.

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
	}
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?

User avatar
Xeo786
Posts: 759
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Excel_Get

Post by Xeo786 » 17 Jan 2023, 23:34

Meroveus wrote:
17 Jan 2023, 15:21
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?
just add this line, workbooks.add

Code: Select all

coxl.workbooks.Add()
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory

Meroveus
Posts: 44
Joined: 23 May 2016, 17:38

Re: Excel_Get

Post by Meroveus » 19 Jan 2023, 13:20

Xeo786 wrote:
17 Jan 2023, 23:34
just add this line, workbooks.add

Code: Select all

coxl.workbooks.Add()
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.
This did work:

Code: Select all

owp := coxl.Workbooks.Open(coxl.StartupPath . "\Personal.xlam")
This works fine in my particular situation, but for external use, I would need to iterate the startup path and open each one — if any.

burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: Excel_Get

Post by burque505 » 19 Jan 2023, 13:38

@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

george-laurentiu
Posts: 11
Joined: 17 Nov 2022, 09:13

Re: Excel_Get

Post by george-laurentiu » 28 Jun 2023, 11:38

@Frasier it was very useful for me, thank you!

Post Reply

Return to “Scripts and Functions (v1)”