Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Basic Ahk_L COM Tutorial for Excel


  • Please log in to reply
227 replies to this topic
Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
If you have some working VBScript you can use ahk to run it:
oSC := ComObjCreate("ScriptControl")

oSC.Language := "VBScript"

oSC.Timeout := -1 ;removes the timeout



ahkvariable := 2



code =

(

'your vbscript

variable = %ahkvariable%

)

oSC.ExecuteStatement(code) ;execute your code

variable := oSC.Eval("variable") ;you have to pull out any variables from the vbscript to be able to use them
Sometimes it's just easier to execute code rather than translating it. If it ain't broke don't fix it. :wink:

plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011
Hey, I know this might sound redundant, but I have tried to search for it:

Is there an updated tutorial on COM with AHK? I know there COM tutorials for excel and webpages, but anything on COM itself? I also know tank has one currently but that one said that it is outdated and no longer maintained. Is there anything that you guys can recommend?

Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
There's this one, which is very basic, then a link to jethrows which is more in depth.
You can't really learn general COM because it is different for every system. If you code for IE you will have to start from the ground up with completely different approaches for Excel. Basicly what I'm saying is you will learn about COM by learning how different programs use it.
I will say that the other coders here can tell you more than I can. I'm still a n00b. :wink:

The best way to learn is to post here on the forums. Have a specific task and you'll get plenty of help on how to accomplish it.

Keep in mind COM is not ahk specific although you have to change a few things to use it from one language to the next the commands are roughly the same.

jethrow
  • Moderators
  • 2854 posts
  • Last active:
  • Joined: 24 May 2009

@Mickers - I would recommend stating somehow that since Excel has a built-in Macro Recorder for VBScript*, the user could utilize this for getting the VBScript code for what they want to do - and then just convert that to AHK. I do this quite often ...

... also, thanks for the double props :wink:

 

* - the built-in Macro Recorder generates VBA, not VBScript



plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011

@Mickers - I would recommend stating somehow that since Excel has a built-in Macro Recorder for VBScript, the user could utilize this for getting the VBScript code for what they want to do - and then just convert that to AHK. I do this quite often ...

... also, thanks for the double props :wink:


Yeah, I had a relatively difficult time deciphering the VBA code from the Macro Recorder just to do a color fill-in for a cell.

I had to take a look at tidbit's Excel library to extract the proper COM syntax for that Excel function.

Is there a certain methodology of translating from VBA to AHK COM?

Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
I have had no end of issues specifically translating colors for cell highlighting. I have had to do a lot of searching via google to find a excel color pallete that included the numbers.
I've spent a lot of largely wasted time on the MSDN trying to figure out how to do simple things. Microsoft really needs to fix that jack up site.
As for a methodology I can't really tell you. Google search can be your best friend. In this day in age most questions have been asked and anwered already it's just about finding where it is.

tidbit
  • Administrators
  • 2709 posts
  • Hates playing Janitor
  • Last active: Jan 15 2016 11:37 PM
  • Joined: 09 Mar 2008
Mickers, use AHK and COM to make a pallet ;) Like I did. I also have that pallet posted in my libraries topic. though it's EXCEL 2002 or something. not sure if newer versions have changed.

rawr. be very afraid
*poke*
. Populate the AutoHotkey city. Pointless but somewhat fun. .


plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011

I have had no end of issues specifically translating colors for cell highlighting. I have had to do a lot of searching via google to find a excel color pallete that included the numbers.
I've spent a lot of largely wasted time on the MSDN trying to figure out how to do simple things. Microsoft really needs to fix that jack up site.
As for a methodology I can't really tell you. Google search can be your best friend. In this day in age most questions have been asked and anwered already it's just about finding where it is.


Well, Google and AHK search didn't really help me on that end. I had to look at someone else's library. I did see one forum with such a question but no real solution in regards to COM syntax. But I just wondered if someone found out how to do it, they might know the way to do it or the place they got it from. But I think tidbit's library has a pretty formidable collection of COM syntax, so I got lucky there.

jethrow
  • Moderators
  • 2854 posts
  • Last active:
  • Joined: 24 May 2009

Yeah, I had a relatively difficult time deciphering the VBA code from the Macro Recorder just to do a color fill-in for a cell.

' VBScript code
Range("A1:A7").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

What issues were you having? The with-keyword? I've thought about requesting a with-command ... or is it the xlSolid constant? In the Macro Code (VB Module), right clicking>quick info shows xlSolid=1. Or is it the understanding that all the VBA code it operating from within the WorkBook object? (*correction - it's the Application object)

plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011

Yeah, I had a relatively difficult time deciphering the VBA code from the Macro Recorder just to do a color fill-in for a cell.

[color=green]' VBScript code[/color]
    Range("A1:A7").Select
    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
What issues were you having? The with-keyword? I've thought about requesting a with-command ... or is it the xlSolid constant? In the Macro Code (VB Module), right clicking>quick info shows xlSolid=1. Or is it the understanding that all the VBScript code it operating from within the WorkBook object?


Yeah, I was looking at the VBA code. I ended up with:

xlSheet.Range("C" . A_Index).Interior.ColorIndex := 3

I guess now that I have the proper code, I can try to decipher VBA a bit more easily, but it wasn't completely obvious to me at first. And I'm sure I'm going to have problems with when to use xlSheet object or xlApp object, whatevs. And probably not knowing when to using the .Range because VBA script for only one cell did not have .Range.

jethrow
  • Moderators
  • 2854 posts
  • Last active:
  • Joined: 24 May 2009

And I'm sure I'm going to have problems with when to use xlSheet object or xlApp object, whatevs.

You can always test what Type of Com Object it is in AHK using ComObjType:
MsgBox, % ComObjType(xlSheet, "Name")

Also, I was wrong. I believe the VBA code is operating from within the Application object.

plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011

You can always test what Type of Com Object it is in AHK using ComObjType:

MsgBox, % ComObjType(xlSheet, "Name")


Please excuse me for my lack of experience and knowledge with COM/AHK Objects, but what goes into "Name" exactly?

jethrow
  • Moderators
  • 2854 posts
  • Last active:
  • Joined: 24 May 2009
Well, if you want the name, use "Name" . If you want the IID, use "IID" . Not including a second param returns the variant type. See ComObjType.

plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011

Well, if you want the name, use "Name" . If you want the IID, use "IID" . Not including a second param returns the variant type. See ComObjType.


Okay, got it now. Sorry, it's definitely not my intention to clutter a thread with useless information/requests. I at least hope that my annoying questions will prevent others from asking the same thing in the future.

So basically, using this function will give me back a Type that will be similar to the VBA script in order to help me determine to use either xlSheet, xlApp, etc. when translating it, correct?

UPDATE:

Okay, now I seem to be getting a command of when to use said objects of the book, application, and sheet. Since those are mostly standardized, I'm sure I will see the appropriate usage in MSDN. Fairly easy, thanks for the clarification.

sinkfaze
  • Moderators
  • 6367 posts
  • Last active:
  • Joined: 18 Mar 2008

I have had no end of issues specifically translating colors for cell highlighting.


Why would you need to translate them? You just need to use the Interior.Color property to get the color code and set it later the same way:

; for the active selection
MsgBox %	ComObjActive("Excel.Application").Selection.Interior.Color