34 replies to this topic

[tidbit]

Easy COM Functions
Excel:
Documentation can be found in the library.

 

I NO LONGER HAVE EXCEL AND CANNOT UPDATE OR MAINTAIN THE LIBRARY. I WAS ALSO WORKING ON SOME UPDATES BEFORE I LOST ACCESS TO EXCEL BUT NEVER GOT TO FINISH. So here is the potentially broke, sloppy stuff that was in the middle of an update (could try an old backup version, though) : http://www.autohotke...idbit/Excel.zip

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:

Excel_Get([_WinTitle])

Excel_ActiveCell(_ID)
Excel_GetActiveRow(_ID)
Excel_GetActiveColumn(_ID)
Excel_GetActiveText(_ID)
Excel_GetSelection(_ID)

Excel_GetValue(_ID [, _start])
Excel_GetRowHeight(_ID [, _start, _end])
Excel_GetColWidth(_ID [, _start, _end])

Excel_AutoFill(_ID [, _start, _end, _Sources, _type])
Excel_SetRowHeight(_ID [, _start, _end, _value])
Excel_SetColWidth(_ID [, _start, _end, _value])
Excel_SetValue(_ID [, _start, _end, _value])
Excel_SetStyle(_ID [, _start, _end, _style])
Excel_Select(_ID [, _start, _end])
Excel_SetActive(_ID [, _start])
Excel_SetFormula(_ID [, _start, _end, _value])

Excel_ScreenUpdate(_ID)
Excel_SplitPanes(_ID)
Excel_SetSplit(_ID [, _which, _where)

Excel_DelCells(_ID [, _start, _end, _direction])
Excel_ClearText(_ID [, _start, _end])
Excel_ClearAll(_ID [, _start, _end])

Excel_ClearFormatting(_ID [, _start, _end])

Excel_BgColor(_ID [, _start, _end, _color])
Excel_Font(_ID [,_start, _end, _options, _Font])
Excel_Borders(_ID [, _start, _end, _options])

Log:
5. Update: --- Dead ---

4. Update: Sun April 24, 2011
- Excel_SetSplit
- Excel_SplitPanes
- Excel_ScreenUpdate
- Excel_GetSelection
- Preparing the Docs for Natural Docs

3. Update: Fri April 08, 2011
- Excel_AutoFill()
- Excel_SetStyle()
- vertical align
- More Documentation
- Basic samples in the Docs
- fixed some mistakes

2. Update: Wed April 06, 2011
- added jethrows Excel_Get()
- Changed Excel_ActiveCell and Excel_GetActiveCol
- Fixed a couple errors.
- GetText and SetText renamed.
- Todo List updated.

[color=darkred]1. Initial release: Tue April 05, 2011

[tidbit]

Reserved.

[musketball]

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.

[sinkfaze]

A few thoughts:

[*:1as88gdk]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:

MsgBox %   ComObjActive("Excel.Application").ActiveCell.Address[0,0]
MsgBox %   RegExReplace(ComObjActive("Excel.Application").ActiveCell.Address[0,0],"\d")

[*:1as88gdk]Your Excel_GetActiveText() has a syntax error:

Excel_GetActiveText(_ID)
{
  Return _ID.ActiveCell.[color=red]_[/color]value ; <~ remove the underscore
}

[*:1as88gdk]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:

[*:1as88gdk]PasteSpecial
[*:1as88gdk]AutoFill
[*:1as88gdk]ScreenUpdating
[*:1as88gdk]Split/FreezePanes

[jethrow]

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):

Excel_Get(WinTitle="ahk_class XLMAIN") {
	ControlGet, hwnd, hwnd, , Excel71, %WinTitle%
	return, Acc_ObjectFromWindow(hwnd, -16).Application
}

Note - Acc Library found here, and the cell must not be in Edit mode to access the Application object from the window object.

[sumon]

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

[tidbit]

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.

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

[sinkfaze]

...and the cell must not be in Edit mode to access the Application object from the window object.

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:

Excel_Check() {

	WinGet, xl, ControlList, ahk_class XLMAIN
	if	RegExMatch(xl,"EXCEL61\nEXCEL71") {
		MsgBox, 48, Warning, Excel is currently in 'Edit cell mode,' please exit any cell being edited and try again.
		return	False
	}
	return	True
}

[tidbit]

Update:

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

[sinkfaze]

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:

- get selection range (like A1:D7)

This again is a job for the Address property.

MsgBox %	ComObjActive("Excel.Application").Selection.Address[0,0]

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:

Excel_ScreenUpdate(_ID) {
	return	_ID.ScreenUpdating :=	!_ID.ScreenUpdating ? True : False

}

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:

Excel_SplitPanes(_ID)
{
	_ID.ActiveSheet.Activate()
	return	_ID.ActiveWindow.FreezePanes :=	!_ID.ActiveWindow.FreezePanes ? True : False
}

Excel_SetSplit(_ID,_Which="Row",_Where="")	; specify 'Row' or 'Col' for Which
{
	_ID.ActiveSheet.Activate()
	if	(_Which="Row")
		_ID.ActiveWindow.SplitRow :=	_Where
	else	_ID.ActiveWindow.SplitColumn :=	_Where
	return	_ID.ActiveWindow.FreezePanes :=	True
}

[Mickers]

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. :wink:

[tidbit]

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.

[tank]

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