AutoHotkey Community

It is currently May 27th, 2012, 12:11 pm

All times are UTC [ DST ]




Post new topic Reply to topic  [ 33 posts ]  Go to page 1, 2, 3  Next
Author Message
PostPosted: April 5th, 2011, 11:11 pm 
Offline

Joined: March 10th, 2008, 12:55 am
Posts: 1907
Location: Minnesota, USA
Easy COM Functions

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 :P).
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:
Code:
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])




Code:
Log:

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.

1. Initial release: Tue April 05, 2011

_________________
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."


Last edited by tidbit on August 26th, 2011, 11:08 pm, edited 14 times in total.

Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: April 5th, 2011, 11:13 pm 
Offline

Joined: March 10th, 2008, 12:55 am
Posts: 1907
Location: Minnesota, USA
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."


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: April 6th, 2011, 2:29 am 
Offline

Joined: March 7th, 2011, 3:45 pm
Posts: 12
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


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: April 6th, 2011, 4:19 am 
Offline
User avatar

Joined: March 19th, 2008, 12:43 am
Posts: 5482
Location: the tunnel(?=light)
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

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


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: April 6th, 2011, 7:22 am 
Offline
User avatar

Joined: May 24th, 2009, 5:35 am
Posts: 2099
Location: Iowa, USA
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):
Code:
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.

_________________
Image
Recommended: AutoHotkey_L
Basic Webpage Controls


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: April 6th, 2011, 10:42 am 
Offline
User avatar

Joined: May 18th, 2010, 3:10 pm
Posts: 1179
Location: Sweden
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?)


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: April 6th, 2011, 5:02 pm 
Offline

Joined: March 10th, 2008, 12:55 am
Posts: 1907
Location: Minnesota, USA
musketball wrote:
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 :)

_________________
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."


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: April 6th, 2011, 5:13 pm 
Offline
User avatar

Joined: March 19th, 2008, 12:43 am
Posts: 5482
Location: the tunnel(?=light)
jethrow wrote:
...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:

Code:
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
}

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


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: April 6th, 2011, 7:05 pm 
@sinkfaze - I usually use this:
Code:
ExitCell() { ; exit cell that you're currently typing in
   Loop, 2
      Loop {
         ControlSend, XLDESK1, {esc}, ahk_class XLMAIN
         ControlGetFocus, focus, ahk_class XLMAIN
      } until focus<>"EXCEL61"
}


Report this post
Top
  
Reply with quote  
 Post subject:
PostPosted: April 8th, 2011, 10:16 pm 
Offline

Joined: March 10th, 2008, 12:55 am
Posts: 1907
Location: Minnesota, USA
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."


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: April 18th, 2011, 4:18 pm 
Offline
User avatar

Joined: March 19th, 2008, 12:43 am
Posts: 5482
Location: the tunnel(?=light)
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:

Code:
 - get selection range (like A1:D7)


This again is a job for the Address property.

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

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

Code:
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
}

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


Last edited by sinkfaze on July 8th, 2011, 9:16 pm, edited 2 times in total.

Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: April 18th, 2011, 8:11 pm 
Offline

Joined: October 11th, 2010, 6:15 pm
Posts: 1211
Location: Right behind you
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:

_________________
COM Tutorial for Webpages
COM Tutorial for Excel


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: April 24th, 2011, 5:48 pm 
Offline

Joined: March 10th, 2008, 12:55 am
Posts: 1907
Location: Minnesota, USA
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."


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: April 24th, 2011, 10:35 pm 
Offline
User avatar

Joined: December 21st, 2007, 3:14 pm
Posts: 3826
Location: Louisville KY USA
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
Image


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: June 19th, 2011, 3:07 pm 
i installed the HotKey L and tried to run one function and that's what i got:

Code:
Error: Call to nonexistent function.
Specifically: Excel_Get([Kimo1.xlsm])
            Line#
--           001:Excel_Get([Kimo1.xlsm])
             002:Exit

The program will exit


Report this post
Top
  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 33 posts ]  Go to page 1, 2, 3  Next

All times are UTC [ DST ]


Who is online

Users browsing this forum: No registered users and 2 guests


You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Powered by phpBB® Forum Software © phpBB Group