Excel_Get

Post your working scripts, libraries and tools for AHK v1.1 and older
kon
Posts: 1756
Joined: 29 Sep 2013, 17:11

Excel_Get

16 May 2017, 17:17

Excel_Get
Returns an Excel application object.

App := Excel_Get([WinTitle, Excel7#])

Parameters:
WinTitle - Optional. Specify a WinTitle to match. Or omit the WinTitle to use the default "ahk_class XLMAIN" which will get the active window.
Excel7# - Optional. Default is 1. Don't change this unless you know what it does.
[ github ] [ download ] (Right-click > SaveAs)

Example Usage:

Code: Select all

F7::  ; Press F7 to display Excel's caption.
    xlApp := Excel_Get()
    if !IsObject(xlApp)  ; If Excel_Get fails it returns an error message instead of an object.
    {
        MsgBox, 16, Excel_Get Error, % xlApp
        return
    }
    MsgBox, % "Caption: " xlApp.Caption
return
Why Excel_Get instead of ComObjActive:
  • It can match a WinTitle.
  • If Excel is in edit-mode, attempting a COM action will produce an error. Excel_Get detects this error and uses ControlSend to send {Escape} to the workbook window.
  • ComObjActive returns the "active" object from the running object table (ROT). "Active" on the ROT does not mean that the window is active. So if more than one Excel application/process is running, ComObjActive does not necessarily return the application object of the active Excel window.
  • If Excel was just opened it may not have registered itself on the ROT yet.
Thanks to jethrow; this version is based on his version. I have rearranged some things but the logic remains largely unchanged. However, unlike other versions, this function is standalone. The ACC lib IS NOT REQUIRED. Also unlike other versions, this function returns a specific error message in the event of failure.
Last edited by kon on 15 Jul 2017, 13:42, edited 4 times in total.
guest3456
Posts: 3454
Joined: 09 Oct 2013, 10:31

Re: Excel_Get

16 May 2017, 23:34

nice work

iamwyf
Posts: 7
Joined: 02 May 2016, 20:38

Re: Excel_Get

17 May 2017, 02:29

great! This helps.
clina1j
Posts: 49
Joined: 22 Apr 2016, 18:39

Re: Excel_Get

14 Feb 2018, 20:04

This script is AMAZING and successfully work on Excel on my company computer, despite various server-permission things that they do.

HOWEVER, I am getting the exact same error messages for

Code: Select all

 ComObjCreate("PowerPoint.Application")
that I used to get for

Code: Select all

ComObjCreate("Excel.Application").
The ones which were solved with Excel_Get().

Is there a "PowerPoint_Get" somewhere out there? if not, could one be created? PLEASE?
burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: Excel_Get

18 Mar 2018, 17:46

Hi, clina1j, maybe this will work for you.
Ppt_Get

Code: Select all

; Ppt_Get.ahk: burque505, modified from
; Excel_Get by jethrow (modified)
; Forum:    https://autohotkey.com/boards/viewtopic.php?f=6&t=31840
; Github:   https://github.com/ahkon/MS-Office-COM-Basics/blob/master/Examples/Excel/Excel_Get.ahk
; With subsequent mods by opiuetasfd - thanks!
Ppt_Get(WinTitle:="ahk_class PPTFrameClass", mdiClass#:=1) {
    static h := DllCall("LoadLibrary", "Str", "oleacc", "Ptr")
    WinGetClass, WinClass, %WinTitle%
    if !(WinClass == "PPTFrameClass")
        return "Window class mismatch. (" WinClass ")"
    ControlGet, hwnd, hwnd,, mdiClass%mdiClass#%, %WinTitle%
    if (ErrorLevel)
        return "Error accessing the control hWnd. (" ErrorLevel ")"
    VarSetCapacity(IID_IDispatch, 16)
    NumPut(0x46000000000000C0, NumPut(0x0000000000020400, IID_IDispatch, "Int64"), "Int64")
    if (hr := DllCall("oleacc\AccessibleObjectFromWindow", "Ptr", hWnd, "UInt", -16, "Ptr", &IID_IDispatch, "Ptr*", pacc)) != 0
        return "Error calling AccessibleObjectFromWindow. (" 
        . (hr = 0x80070057 ? "E_INVALIDARG" : hr = 0x80004002 ? "E_NOINTERFACE" : hr) ")"
    window := ComObject(9, pacc, 1)
    if ComObjType(window) != 9
        return "Error wrapping the window object."
    try return window.Application
    catch e
        return "Error accessing the application object. (" SubStr(e.message, 1, 10)  ")"
}

; References
;   https://autohotkey.com/board/topic/88337-ahk-failure-with-excel-get/?p=560328
;   https://autohotkey.com/board/topic/76162-excel-com-errors/?p=484371
;   https://autohotkey.com/boards/viewtopic.php?p=134048#p134048
Here's an example script.

Code: Select all

#Include Ppt_Get.ahk

F7::  ; Press F7 to display Powerpoint's caption.
    pptApp := Ppt_Get()
    if !IsObject(pptApp)  ; If Ppt_Get fails it returns an error message instead of an object.
    {
        MsgBox, 16, Ppt_Get Error, % pptApp
        return
    }
    MsgBox, % "Caption: " pptApp.Caption
return
Not thoroughly tested, but works on my Win7 64-bit system, AHK 1.1.28.00 64-bit
Regards,
burque505
User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: Excel_Get

18 Mar 2018, 18:21

- Nicely done burque505, I also attempted this here:
Excel_Get is AMAZING, could someone make a PowerPoint_Get? - AutoHotkey Community
https://autohotkey.com/boards/viewtopic.php?f=5&t=44286
- I was waiting for clina1j to confirm that the script worked, before posting a link here, but I'm yet to receive any feedback.
- Interestingly, we have used a different control, to do the latching, mdiClass/paneClass, I wonder which one is better, or if it matters.
- Maybe we need more XXX_Get functions, even an Office_Get function. Cheers.
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: Excel_Get

18 Mar 2018, 18:29

jeeswg, I think you are absolutely right about an Office_Get function, that would make people's lives easier (or not :twisted: ).
Let me check out your link and I'll edit this.
Regards,
burque505
Edit: I think I don't have paneClass, I'm running Office16. Are you running Office12? I thought I saw a reference in there that looked familiar, but I don't have that version.
User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: Excel_Get

18 Mar 2018, 18:44

- I use Excel 2007. Haha separate functions for each program would be more useful, because that way you can omit the window class.
- To list controls:

Code: Select all

WinGet, hWnd, ID, A
WinGet, vCtlList, ControlList, % "ahk_id " hWnd
Loop, Parse, vCtlList, `n
{
	vCtlClassNN := A_LoopField
	;ControlGet, hCtl, Hwnd,, % vCtlClassNN, % "ahk_id " hWnd
	vOutput .= vCtlClassNN "`r`n"
}
Clipboard := vOutput
MsgBox, % "done"
return
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
clina1j
Posts: 49
Joined: 22 Apr 2016, 18:39

Re: Excel_Get

22 Mar 2018, 13:26

Alright, so I gave it a try. It looked very promising, but I got the attached error.
PPTGet Error.png
PPTGet Error.png (8.77 KiB) Viewed 11583 times
I am using Windows 7, and Office 2010 on a 64 bit computer. And my company handles people healthcare information, so the security is PARANOID
Any ideas?
burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: Excel_Get

22 Mar 2018, 13:58

Hi clina1j, I know it wouldn't work as written for Office 2007, but I thought it would work on Office 2010. It works for Office 2013 and Office 2016, at least for me. If you can bear with me for twenty minutes or so, I'll try to get it working in a VM with XP, where I do have Office 2010.

The reason it won't work on 97, 2003, or 2007 is these class names:
PPT97: "PP97FrameClass"
PPT2K: "PP9FrameClass"
XP: "PP10FrameClass"
2003: "PP11FrameClass"
2007: "PP12FrameClass"
BUT 2010: "PPTFrameClass" just like 2013 and 2016.

Back in a few!

EDIT: I get exactly the same error for 2010, although in the VM I'm running 32-bit XP and 32-bit (of course) Office 2010. I think I might need the ACC library for the original jethrow function, which I'll have to modify. I'll give it a try, but it may take a bit. I'll shoot you a PM if I have luck.

Regards,
burque505
User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: Excel_Get

22 Mar 2018, 14:31

It might be good to fill in the info for the control class:
window class:
PPT97: "PP97FrameClass"
PPT2K: "PP9FrameClass"
XP: "PP10FrameClass"
2003: "PP11FrameClass"
2007: "PP12FrameClass"
2010,2013,2016: "PPTFrameClass"

control class:
2007: "mdiClass"
?: "paneClass"

@clina1j: I wrote a version that is virtually identical to burque505's, in case that works for you (and consider changing the window/control class names).
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
awel20
Posts: 211
Joined: 19 Mar 2018, 14:09

Re: Excel_Get

22 Mar 2018, 15:20

-2147467259 = 0x80004005 = E_FAIL = Unspecified failure (src)

Code: Select all

To obtain an IDispatch interface pointer to a class supported by the native object model, specify OBJID_NATIVEOM in dwObjectID. When using this object identifier, the hwnd parameter must match the following window class types.

Office application  Window class    IDispatch pointer to
Word                _WwG            Window
Excel               EXCEL7          Window
PowerPoint          paneClassDC     DocumentWindow
Command Bars        MsoCommandBar   CommandBar
(src)

-changed to use "paneClassDC"
-added some code to translate the hr value into a more readable name
-added Return Type to AccessibleObjectFromWindow dllcall

Code: Select all

; Ppt_Get.ahk: burque505, modified from
; Excel_Get by jethrow (modified)
; Forum:    https://autohotkey.com/boards/viewtopic.php?f=6&t=31840
; Github:   https://github.com/ahkon/MS-Office-COM-Basics/blob/master/Examples/Excel/Excel_Get.ahk
; With subsequent mods by opiuetasfd - thanks!
Ppt_Get(WinTitle:="ahk_class PPTFrameClass", paneClassDC#:=1) {
    static h := DllCall("LoadLibrary", "Str", "oleacc", "Ptr")
    static hrVal := { 0x00000000: "S_OK", 0x80004004: "E_ABORT", 0x80070005: "E_ACCESSDENIED", 0x80004005: "E_FAIL"
                    , 0x80070006: "E_HANDLE", 0x80070057: "E_INVALIDARG", 0x80004002: "E_NOINTERFACE", 0x80004001: "E_NOTIMPL"
                    , 0x8007000E: "E_OUTOFMEMORY", 0x80004003: "E_POINTER", 0x8000FFFF: "E_UNEXPECTED"}
    WinGetClass, WinClass, %WinTitle%
    if !(WinClass == "PPTFrameClass")
        return "Window class mismatch. (" WinClass ")"
    ControlGet, hwnd, hwnd,, paneClassDC%paneClassDC#%, %WinTitle%
    if (ErrorLevel)
        return "Error accessing the control hWnd. (" ErrorLevel ")"
    VarSetCapacity(IID_IDispatch, 16)
    NumPut(0x46000000000000C0, NumPut(0x0000000000020400, IID_IDispatch, "Int64"), "Int64")
    if (hr := DllCall("oleacc\AccessibleObjectFromWindow", "Ptr", hWnd, "UInt", -16, "Ptr", &IID_IDispatch, "Ptr*", pacc, "UInt")) != 0
        return "Error calling AccessibleObjectFromWindow. (" (hrVal[hr] != "" ? hrVal[hr] : Format("0x{:X}", hr)) ")"
    window := ComObject(9, pacc, 1)
    if ComObjType(window) != 9
        return "Error wrapping the window object."
    try return window.Application
    catch e
        return "Error accessing the application object. (" SubStr(e.message, 1, 10)  ")"
}
clina1j
Posts: 49
Joined: 22 Apr 2016, 18:39

Re: Excel_Get

22 Mar 2018, 17:02

So, when I tried that, i got two new errors.

The first, when I tried to run it was:
PPTGet Error 2.png
PPTGet Error 2.png (28.57 KiB) Viewed 11550 times
The second (when i commented out the line above) was:
PPTGet Error 3.png
PPTGet Error 3.png (7.41 KiB) Viewed 11550 times
Possibly a step closer, but still a few bugs

I cannot say how GRATEFUL I am to all of you for working on this.
TAC109
Posts: 1098
Joined: 02 Oct 2013, 19:41
Location: New Zealand

Re: Excel_Get

22 Mar 2018, 17:09

You need to update AHK to a recent version (also undo the change you made).
My scripts:-
XRef - Produces Cross Reference lists for scripts
ReClip - A Text Reformatting and Clip Management utility
ScriptGuard - Protects Compiled Scripts from Decompilation
I also maintain Ahk2Exe
User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: Excel_Get

22 Mar 2018, 17:15

- Generally, when testing, it would be a good idea to add a MsgBox line, to check if a (non-zero) control hWnd was retrieved, otherwise perhaps there was no control with the desired ClassNN.

Code: Select all

    ControlGet, hwnd, hwnd,, paneClassDC%paneClassDC#%, %WinTitle%
MsgBox, % hWnd ;add this line
- Also, when testing, you can try this, to make sure you specify the right window:

Code: Select all

;assumes PowerPoint is the active window
WinGet, hWnd, ID, A
WinGetTitle, vWinTitle, % "ahk_id " hWnd
MsgBox, % vWinTitle
pptApp := Ppt_Get("ahk_id " hWnd)
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
clina1j
Posts: 49
Joined: 22 Apr 2016, 18:39

Re: Excel_Get

22 Mar 2018, 17:40

I think I chose the wrong bit to comment out. When I moved the semicolon to just after the word "return" in the problem line, it worked. Or perhaps my computer was in a mood, I just restated it for other reasons. anyway, it works!
PPTGet Error 4.png
PPTGet Error 4.png (5.18 KiB) Viewed 11537 times
I will try and upgrade my AHK version, but I mentioned that my company is Paranoid, right? I have to get all kinds of permission to use the AHK that I have, upgrading is going to be time consuming.
burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: Excel_Get

22 Mar 2018, 18:11

Re Ppt_Get:
awel20, thanks for updating that code.
clina1j, I just ran awel20's version on XP in a VM and it worked with no mods. I am using a newer version of AHK, though.
Thanks to everybody in the thread for keeping up with this! I appreciate it.

EDIT: I just ran awel20's mod on PowerPoint 2016, no luck. Works great on 2010.

RE-EDIT: The problem is that 2010 uses paneClassDC, and 2013 and 2016 use mdiClass. Might be best to use one function for 2010, one for 2013 and 2016. Either that or implement a check.
Thoughts, anybody?

RE-RE-EDIT This link has a list of the different PP*FrameClass names required, depending on version.
Error.PNG
Error.PNG (20.57 KiB) Viewed 11530 times
Regards,
burque505
Last edited by burque505 on 22 Mar 2018, 18:31, edited 4 times in total.
User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: Excel_Get

22 Mar 2018, 18:19

- It's possible that you could get something to work without using the Format function, the Format function is just there to convert the dec number to hex for presentation purposes in the error message.
- If your version lacks the Format function, it's probably quite old. Format was added in v1.1.17.00 - December 27, 2014. However, I don't think that you would need to update, to get what you want, so I would experiment.
- If you were going to update AutoHotkey, I would use AHK v1.1.27.07 or wait until v1.1.28.01. Why? Because there is an issue with v1.1.28.00 and hotstrings in Excel (and possibly other programs), so you wouldn't want to be stuck with that version.
Changes & New Features
https://autohotkey.com/docs/AHKL_ChangeLog.htm
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
Frasier
Posts: 2
Joined: 03 Dec 2020, 05:04

Re: Excel_Get

03 Dec 2020, 12:39

Hi.
I have exactly the same problem, but with microsoft word.

Can any one do any similar for Word?

Thx
Frasier
Posts: 2
Joined: 03 Dec 2020, 05:04

Re: Excel_Get

03 Dec 2020, 16:17

I think I have managed:

Code: Select all

; Word_Get based in the one for excel by jethrow (modified)
; Forum:    https://autohotkey.com/boards/viewtopic.php?f=6&t=31840
; Github:   https://github.com/ahkon/MS-Office-COM-Basics/blob/master/Examples/Excel/Excel_Get.ahk
Word_Get(WinTitle:="ahk_class OpusApp", _WwG#:=1) {
    static h := DllCall("LoadLibrary", "Str", "oleacc", "Ptr")
    WinGetClass, WinClass, %WinTitle%
    if !(WinClass == "OpusApp")
        return "Window class mismatch."
    ControlGet, hwnd, hwnd,, _WwG%_WwG#%, %WinTitle%
    if (ErrorLevel)
        return "Error accessing the control hWnd."
    VarSetCapacity(IID_IDispatch, 16)
    NumPut(0x46000000000000C0, NumPut(0x0000000000020400, IID_IDispatch, "Int64"), "Int64")
    if DllCall("oleacc\AccessibleObjectFromWindow", "Ptr", hWnd, "UInt", -16, "Ptr", &IID_IDispatch, "Ptr*", pacc) != 0
        return "Error calling AccessibleObjectFromWindow."
    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, _WwG%_WwG#%, {Esc}, %WinTitle%
            else
                return "Error accessing the application object."
        }
It seems that it works... I only have changed th values of Excel7 and XLMAIN from the supposedly equivalents in Word. The first time it didn't work, but with other values it seems it did. Just in case is useful for someone.

Return to “Scripts and Functions (v1)”

Who is online

Users browsing this forum: No registered users and 131 guests