Excel_Get

Post your working scripts, libraries and tools
kon
Posts: 1760
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] [Download] GeSHi © Codebox Plus

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: 2332
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] [Download] GeSHi © Codebox Plus

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

Code: [Select all] [Download] GeSHi © Codebox Plus

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: 500
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] [Expand] [Download] GeSHi © Codebox Plus


Here's an example script.

Code: [Select all] [Download] GeSHi © Codebox Plus

#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: 4600
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.
burque505
Posts: 500
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: 4600
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] [Expand] [Download] GeSHi © Codebox Plus

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 812 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: 500
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: 4600
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).
awel20
Posts: 17
Joined: 19 Mar 2018, 14:09

Re: Excel_Get

22 Mar 2018, 15:20

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

Code: [Select all] [Download] (Untitled.txt)GeSHi © Codebox Plus

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] [Expand] [Download] GeSHi © Codebox Plus

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 779 times


The second (when i commented out the line above) was:
PPTGet Error 3.png
PPTGet Error 3.png (7.41 KiB) Viewed 779 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: 214
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 programs:-
ReClip - a Text Reformatting and Clip Management utility
XRef - Produces Cross Reference lists for scripts

User avatar
jeeswg
Posts: 4600
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] [Download] GeSHi © Codebox Plus

    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] [Download] GeSHi © Codebox Plus

;assumes PowerPoint is the active window
WinGet, hWnd, ID, A
WinGetTitle, vWinTitle, % "ahk_id " hWnd
MsgBox, % vWinTitle
pptApp := Ppt_Get("ahk_id " hWnd)
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 766 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: 500
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 759 times


Regards,
burque505
Last edited by burque505 on 22 Mar 2018, 18:31, edited 4 times in total.
User avatar
jeeswg
Posts: 4600
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

Return to “Scripts and Functions”

Who is online

Users browsing this forum: Dravenizer and 19 guests