AutoHotkey Community

Easy COM Functions

Excel: [Download] [Demo] [Color Chart]

Documentation can be found in the library.

We get a lot of COM question, especially Excel. So I decided to brave the depths of MSDN and make a simple to use Excel library. I haven't used COM before so this was a learning experience too. That also means it is possible that my code may not be the best. I also tried to make the code easy to read (with a few ternaries thrown in ).
I would also appreciate it if anyone would like to help expand or optimize my library. Things such as: Word_, IE_, PowerPoint_, or even common Explorer stuff. But remember, KISS. So easy a doorknob could do it.

Note: I only have MS Office 2002 and have encountered a few limitations while testing. Basically, if a Constants value is negative, you need Excel 2007 (possibly even 2003) or higher to use the option. eg. u4 | xlUnderlineStyleNone | -4142 | No underlining. in Excel_Font().

Function list:

_________________
rawr. be very afraid
*poke*
Note: My name is all lowercase for a reason.
"I think Bigfoot is blurry, that's the problem. It's not the photographer's fault, Bigfoot is blurry. So there's a large, out-of-focus monster roaming the countryside."

Reserved.

_________________
rawr. be very afraid
*poke*
Note: My name is all lowercase for a reason.
"I think Bigfoot is blurry, that's the problem. It's not the photographer's fault, Bigfoot is blurry. So there's a large, out-of-focus monster roaming the countryside."

Very cool! I am excited to try it. As you develop it, I would love to see "larger functionality", such as selecting or moving worksheets, saving them in a new workbook, etc.

Eager to see what else you come up with.

_________________
Proud User of Radial Menu

A few thoughts:

• For your Excel_ActiveCell() and Excel_GetActiveColumn() functions it would be easier to use the Address property, particularly since other functions are dependent upon using Range:
  
  
  Code:
  MsgBox %   ComObjActive("Excel.Application").ActiveCell.Address[0,0]
  MsgBox %   RegExReplace(ComObjActive("Excel.Application").ActiveCell.Address[0,0],"\d")
• Your Excel_GetActiveText() has a syntax error:
  
  
  Code:
  Excel_GetActiveText(_ID)
  {
    Return _ID.ActiveCell._value ; <~ remove the underscore
  }
• I think the GetText and SetText functions should more appropriately be named as GetValue and SetValue, since 1s and 0s that a user desires to treat as literal may not set properly in cells as sent by the Value property, whereas the Formula property will treat them as literal. The naming indicates the function is capable of something it may not be capable of.

Otherwise, a pretty comprehensive set of functions. Some other functions worth consideration to add to your library:

• PasteSpecial
• AutoFill
• ScreenUpdating
• Split/FreezePanes

_________________

Try Quick Search for Autohotkey or see the tutorial for newbies.

I don't personally promote such libraries since I believe it's easier to just learn to use the interface. However, I believe this would be more user friendly if your functions could access the Excel object provided an optional WinTitle Parameter - kinda like ahklearner's old functions. You can use the following code to access an Excel Application (I'd advise against ComObjActive for excel):
Note - Acc Library found here, and the cell must not be in Edit mode to access the Application object from the window object.

_________________

Recommended: AutoHotkey_L
Basic Webpage Controls

Wonderful, great job and hopefully useful library. Functions own, since they make things so much easier for the next time. Not having used excel so much, I wonder: What is "Id", start and end? Can you select a cell based off for example row/column? Can I get the values of every row in column C? Can I get them one at the time, with user interaction (and script interaction) inbetween?

Cheers

_________________
~sumon • Appifyer• AHK Nova halted • Recommended: AHK_L (Why?)

Honestly, I don't really even know what those are. I RARELY use Excel. If you could write down some steps for me on how to do it, I could look into how to do it in COM .

Thank you sinkfaze. Didn't know about address(). I was also trying to avoid using regex. But i guess doing a simple regex on a very short string won't cause any ruckus. I will rename.

jethrow, I was thinking of something like that

sumon, that is on my todo list. being able to return all the values in a selection. But first I need to know how to determine the selection range (e.g. C1:C96).
ID is the Excel document/window. Now defined by Excel_Get()
Start is the starting cell. Like A1 or K76
End is optional. if blank, it defaults to Start. otherwise it's the ending cell. like B6 or Z836. that way you can modify many cells at once.
So: Excel_Select(ID, "C1", "C100") would select C1 through C100 (C1:C100)


Update.

Ps, i'm not to savvy with version numbers, so each update will just go up by 1

_________________
rawr. be very afraid
*poke*
Note: My name is all lowercase for a reason.
"I think Bigfoot is blurry, that's the problem. It's not the photographer's fault, Bigfoot is blurry. So there's a large, out-of-focus monster roaming the countryside."

This is about the most reliable method I've found to check this, since previous methods of checking the status of the active control (or the control with focus) will not work if the Excel window is not active:

_________________

Try Quick Search for Autohotkey or see the tutorial for newbies.

@sinkfaze - I usually use this:

Update:

What's new in 3:
- Excel_AutoFill()
- Excel_SetStyle()
- Vertical align
- More Documentation
- Basic samples in the Docs
- Fixed some mistakes

_________________
rawr. be very afraid
*poke*
Note: My name is all lowercase for a reason.
"I think Bigfoot is blurry, that's the problem. It's not the photographer's fault, Bigfoot is blurry. So there's a large, out-of-focus monster roaming the countryside."

Just tried out the AutoFill function (one of the few things I hadn't already made myself), very nice. You might consider making the _Table object static, not there will be some dramatic improvement in performance but an improvement nonetheless.

I noticed this on your todo list:



This again is a job for the Address property.



Another really nice feature of this implementation of the Address property is if you have something you need to paste into multiple ranges of different sizes which you have selected (and which Excel would normally reject), the address property will create a comma-delimited list of the selected ranges which can then be parsed.

As far as a ScreenUpdating function, this is probably the most simple implementation:



SplitPanes is a little more tricky because of the amount of options available, but the typical user will probably never need anything more than to split at a specific row/column, so the SplitRow and SplitColumn properties would be sufficient. You would also have to account in the documentation for the fact that Row and Column expect a number because those properties want to know how many rows/columns will be above/left of (respectively) the split. So something like this might suffice:

_________________

Try Quick Search for Autohotkey or see the tutorial for newbies.

I use the "Macro" button in Excel. It records any actions you take and convert's them into VBA. It's an easy translation from there as long as you understand basic COM.

_________________
COM Tutorial for Webpages
COM Tutorial for Excel

Thank you sinkfaze! Added your functions. although ScreenUpdate seems to do something funny to me. Probably some 2002 issue. Or maybe because I don't use the Explorer shell. or both.

I was wondering, should the name be simplified to XL_ ?

What's new in 4:
- Excel_SetSplit
- Excel_SplitPanes
- Excel_ScreenUpdate
- Excel_GetSelection
- Preparing the Docs for Natural Docs
- Made the _Tables static.

ps: I'm having issues with natural docs. the custom installer. I installed it, ran CMD and changed the dir to where my file is, typed "mkdoc" aswell as "mkdoc s" and I get an error "'mkdoc' is not recognized as an internal or external command, operable program or batch file." so I then tried copying mkdoc.bat to the NaturalDoc.exe folder and put my script there aswell. it worked, however it only generates a Browser Test HTML file. nothing related to my script can be found. I'll play around a bit more.

_________________
rawr. be very afraid
*poke*
Note: My name is all lowercase for a reason.
"I think Bigfoot is blurry, that's the problem. It's not the photographer's fault, Bigfoot is blurry. So there's a large, out-of-focus monster roaming the countryside."

as far as selecting workbooks my own library relies on a file path or defaults to the top most active excel workbook

_________________
No matter what your oppinion Please join this discussion
Formal request to Polyethene

i installed the HotKey L and tried to run one function and that's what i got: