227 replies to this topic

[Mickers]

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]

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]

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]

@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]

@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]

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]

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.

[plastikglass]

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]

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]

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]

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]

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]

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]

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]

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