Excel Enum Constants Search

Post your working scripts, libraries and tools
burque505
Posts: 1130
Joined: 22 Jan 2017, 19:37

Excel Enum Constants Search

04 Jan 2019, 15:31

Thanks first to TheDewd for his post here that pointed me in this direction.
If you work with Excel and COM a lot, you might need enumerated constants with some frequency. If you know all or part of a constant's name, you can use this instead of searching far and wide for it.
In the Query field, type in all or part of an enumerated constant's name. Double-click on an entry to put the integer value on the clipboard, double-right-click to put the alpha constant name on the clipboard.
You will need the file "constants.txt" which is in the ZIP archive below, as well as "Sift.ahk", also in the archive. EDIT: Thanks to @FanaticGuru for Sift.ahk and all of his Office COM posts, which I look forward to very much.
Spoiler
Regards,
burque505
XL Enum Srch.gif
XL Enum Srch.gif (196.89 KiB) Viewed 2226 times
Attachments
Excel Enum Search.zip
(17.76 KiB) Downloaded 69 times
Last edited by burque505 on 04 Jan 2019, 16:48, edited 2 times in total.
FanaticGuru
Posts: 1397
Joined: 30 Sep 2013, 22:25

Re: Excel Enum Constants Search

04 Jan 2019, 16:02

This is simple but great!

I am constantly looking up enumerated constants for Excel and Word.

I should have created something like this years ago but was too complacent with just googling all the time.

I am going to see about adding Word's constants and adding a hotkey so I can have it running all the time.

I was also pleasantly surprised to see that it uses my Sift library. Hard to believe it has been almost four years since I wrote that.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers
burque505
Posts: 1130
Joined: 22 Jan 2017, 19:37

Re: Excel Enum Constants Search

04 Jan 2019, 16:14

Thanks, FG, kind words. I have some Word constants and units to share with you, attached. That was going to be my next project - but it would be handier to have both in one GUI, wouldn't it?

The wdUnits are so few in number I should have probably just added them to the larger file.

Edit: Here's a Word version too (minus the wdUnits, all 16 of 'em. Might be worth putting those in just a menu item all together).
Edit: Added wdUnits in as a menu item (in spoiler). Put all the Word stuff in one archive.

Edit: I get this impression my list of Word constants is woefully incomplete. As soon as I find a comprehensive list I'll post it here so it can be plugged in to the script.
Spoiler
Regards,
burque505
Word Enum Srch.gif
Word Enum Srch.gif (214.77 KiB) Viewed 2196 times
Attachments
All word stuff.zip
(92.91 KiB) Downloaded 36 times
FanaticGuru
Posts: 1397
Joined: 30 Sep 2013, 22:25

Re: Excel Enum Constants Search

04 Jan 2019, 20:24

I ended up making quite a bit of changes.

The main thing is I switched over to keeping the data in an array in an attempt to make it a little quicker.

With the large number of constants from both Excel and Word the Initialization is definitely noticeable. It is only the first time you open the dialog though, after that it is quick to display and search.

Apparently setting up all the lines in the Gui ListView is the slow part so the array did not help as much as I had hoped

Code: Select all

; by burque505 
; https://www.autohotkey.com/boards/viewtopic.php?p=256295#unread
; modified by FanaticGuru

SetBatchLines, -1 ; more CPU time for quicker Initialization

#Include [Library] Sift.ahk

#e::
	if !Initialized
	{
		SysGet, VSBW, 2 ; SM_CXVSCROLL
		Width := 240 + 80 + VSBW + 4 ; adjust for vertical scroll bar and make just wide enough not to have horizontal scroll bar
		Gui +AlwaysOnTop
		Menu, HelpMenu, Add, &Help, Help
		Menu, HelpMenu, Add, &wdUnits, wdUnits
		Gui, Menu, HelpMenu
		Gui, Add, Edit, x10 y+10 w300 h25 vQueryText gQuery,
		Gui, Add, Text, x+10 w30, Query
		Gui, Add, ListView, grid x10 y+20 w%Width% r30 hwndHwnd gMyListView vMyListView, Constant | Value
		LV_ModifyCol(1, 240) ; change width of alphanum field
		LV_ModifyCol(2, 80)
		; load Word data
		Fileread, OutputVar, Enumerated Constants - Word.csv
		Data := StrSplit(OutputVar, "`n", "`r")
		; load Excel data
		Fileread, OutputVar, Enumerated Constants - Excel.csv
		Max := Data.MaxIndex() 
		Data.Insert(Max+1, StrSplit(OutputVar, "`n", "`r")*)
		for Index, Element in Data
		{
			Cols := StrSplit(Element, ",")
			LV_Add("", Cols.1, Cols.2)
		}
		Initialized := true
	}
	Gui, Show,, Enumerated Constants Search
return

Query:
	Gui, Submit, NoHide
	if QueryText
		Results := Sift_Regex(Data, QueryText, "uw")
	else
		Results := Data
	Max := Results.MaxIndex()
	for Index, Element in Results
	{
		Cols := StrSplit(Element, ",")
		LV_Modify(Index,, Cols.1, Cols.2)
	}
	Loop % Data.MaxIndex() - Max
		LV_Modify(A_Index+Max,, "","")
	SendMessage, 0x115, 6, 0,,  ahk_id %Hwnd% ; force scroll to top
return

GuiClose:
GuiEscape:
	Gui, Hide
return

MyListView:
	if (A_GuiControlEvent == "R") 
	{
		LV_GetText(Name, A_EventInfo, 1)
		Clipboard := Name
		MsgBox, 4096, Constant Name, Constant name: %Name%`n`n(on clipboard also)
	}
	if (A_GuiControlEvent == "DoubleClick") 
	{
		LV_GetText(Number, A_EventInfo, 2)
		Clipboard := Number
		MsgBox, 4096, Constant Value, Constant integer value: %number%`n`n(on clipboard also)
	}
return

Help:
	v =
	(LTrim
	Type in some or all of an enumerated constant name to find.
	You can delete, edit, and otherwise modify your search,
	and the ListView will refresh accordingly.

	When you see a constant you want to retrieve:

	Double-right-click to get the name on the clipboard;
	Double-click to get the integer value on the clipboard.

	That's it!
	)
	MsgBox, 4096, Help, %v%
return

wdUnits:
	units =
	(LTrim
	wdCell`t`t`t12`tA cell.
	wdCharacter`t`t1	A character.
	wdCharacterFormatting`t13`tCharacter formatting.
	wdColumn`t`t9`tA column.
	wdItem`t`t`t16`tThe selected item.
	wdLine`t`t`t5`tA line.
	wdParagraph`t`t4`tA paragraph.
	wdParagraphFormatting`t14`tParagraph formatting.
	wdRow`t`t`t10`tA row.
	wdScreen`t`t7`tThe screen dimensions.
	wdSection`t`t8`tA section.
	wdSentence`t`t3`t`A sentence.
	wdStory`t`t`t6`tA story.
	wdTable`t`t`t15`tA table.
	wdWindow`t`t11`tA window.
	wdWord`t`t`t2`tA word.
	)
	MsgBox, 4096, wdUnits, %units%
return
You need to #include Sift and have the two files "Enumerated Constants - Word.csv" and "Enumerated Constants - Excel.csv".

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers
burque505
Posts: 1130
Joined: 22 Jan 2017, 19:37

Re: Excel Enum Constants Search

05 Jan 2019, 08:55

Very nice!! I'm putting together a list of Word VBA constants to add, and it's slow going. I could automate the scrape with IE, but the pages keep crashing with IE (not with any other browser). So it's by hand for now. I'll post that list here when it's done.
Regards,
burque505
User avatar
Thoughtfu1Tux
Posts: 93
Joined: 31 May 2018, 23:26

Re: Excel Enum Constants Search

06 Jan 2019, 05:19

This is fantastic! Thank you!

Do you by any chance have any example scripts that you use these with? I use Excel with COM all the time to get/set data, and to create dialog boxes based on their constant codes, but I’m not sure how I would use a lot of these other constants. These would be a fantastic addition to my arsenal of tools to help me automate my repetative work tasks.
hughman
Posts: 14
Joined: 17 Jun 2014, 09:39

Re: Excel Enum Constants Search

06 Jan 2019, 08:06

thx, it's very useful and convenient.
User avatar
Relayer
Posts: 130
Joined: 30 Sep 2013, 13:09
Location: Delaware, USA

Re: Excel Enum Constants Search

06 Jan 2019, 09:17

Hi,
I had a similar problem with long initialization at startup with Capn Odin's trie class (https://www.autohotkey.com/boards/viewtopic.php?t=35837) loading a whole English dictionary. I broke the dictionary into 26 files alphabetically, created an array of file names, and maintained an array of files loaded. I then loaded them on-demand and since the individual files were much smaller, the delay was reduced to imperceptible. I also played around with a background timer task that loaded them over a period of time after showing the gui, again making the loading invisible to the user. It was pretty easy in hindsight.

BTW, you may be able to use his trie class to implement your search.

Relayer
Tre4shunter
Posts: 53
Joined: 26 Jan 2016, 16:05

Re: Excel Enum Constants Search

06 Jan 2019, 13:07

Extremely useful script! Thanks so much for posting!
burque505
Posts: 1130
Joined: 22 Jan 2017, 19:37

Re: Excel Enum Constants Search

06 Jan 2019, 14:13

@ThoughtfulTux, @Tre4shunter, @hughman, @Relayer, thanks for the encouragement and advice. I'll be working on the Word and Excel enumerated constants scripts to try to incorporate suggestions I get, and to add more functionality.

ThoughtfulTux, I don't have any complex examples to share yet, but here's a short script to convert a Word file to a PDF, with code commented out to export it as a web archive. Hopefully it'll be food for thought, if nothing else. (I'm willing to bet you've got far more complex code in your arsenal already.)

(The following description is going to be too simplistic for many. Please just skip over the parts you already know.)

Generally I try to record a VBA macro and convert the code to AHK, or search the web for a VBA script that does more or less what I'm trying to accomplish. One way or another, VBA examples are almost always the starting point for me, unless somebody else has already done it in AHK and posted it.

Here's part of a VBA macro that will export a file as PDF:

Code: Select all

    ActiveDocument.ExportAsFixedFormat _
     OutputFileName:=CurrentFolder & FileName & ".pdf", _
     ExportFormat:=wdExportFormatPDF
Obviously that's going not going to translate one-to-one to AHK! We need to use AHK function syntax, so we need parentheses, and args for "ActiveDoc.ExportAsFixedFormat".
Keeping that in mind, using the enumerated constant for PDF, though, it's actually easier in AHK:

Code: Select all

doc.ExportAsFixedFormat(a_scriptdir . "\finished", 17) ; This will export a PDF
(In the code above, I've left out the creation of the Word object and other essentials. See the spoiler.)
In AHK, we don't need the file extension "pdf" - Word will add it for us. Instead of "ExportFormat:=wdExportFormatPDF", all we need is 17 - the constant.
Spoiler
Note we can use either "SaveAs" or "ExportAsFixedFormat" with very minor code changes.

Regards,
burque505
User avatar
jmeneses
Posts: 487
Joined: 28 Oct 2014, 11:09
Location: Catalan Republic

Re: Excel Enum Constants Search

10 Jan 2019, 02:02

Hey burque505 another time
Your MS word script will not use much, but this script will go very well me
Thanks
Donec Perficiam
burque505
Posts: 1130
Joined: 22 Jan 2017, 19:37

Re: Excel Enum Constants Search

10 Jan 2019, 19:33

I've used several versions of the constants for Excel, so I've attached them in case anyone can use them. Won't take much tweaking the in the script.
excel constants.zip
(35.65 KiB) Downloaded 43 times
User avatar
jeeswg
Posts: 6904
Joined: 19 Dec 2016, 01:58
Location: UK

Re: Excel Enum Constants Search

06 Mar 2019, 06:55

- To enumerate Office constants see this link:
MS Office Excel COM Interface Constants - AutoHotkey Community
https://autohotkey.com/boards/viewtopic.php?f=74&t=33914
- I mention OfficeInfo.ahk by kon, and ImportTypeLib.ahk by maul.esel.
- I suppose the newer your Office version, the fuller your list will be. 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: 1130
Joined: 22 Jan 2017, 19:37

Re: Excel Enum Constants Search

06 Mar 2019, 08:34

Thanks, jeeswg! I'll have a look.
Regards,
burque505

Return to “Scripts and Functions”

Who is online

Users browsing this forum: No registered users and 18 guests