(Slightly) advanced Excel COM troubles

[PaulLeavitt]

Hey AHK'ers!
I'm trying to write a script to do some manipulations to an excel spreadsheet before pulling information out of it to be used in another application, but I can't get some of the COM commands to work.
Here's my code as is:

Code: Select all

#!g::
Xl.Worksheets(1).ShowAllData               ;Because the Excel doc is filtered when I receive it
Xl.Intersect(Columns("G"), ActiveSheet.UsedRange).Replace "0*", "#N/A", xlPart              ;
Xl.Intersect(Columns("G"), ActiveSheet.UsedRange).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
Return

And this is the error that AHK gives me: "Error: Call to nonexistent function. Specifically: Columns("G"), ActiveSheet.UsedRange).Replace "0*", "#N/A", xlPart"

I would normally just use VBA in Excel, but I want this script to be attributable within my company so I was hoping to keep it all within one file. But if you have another suggestion for how I could use VBA in Excel, and call it from AHK somehow, that would work as well.
Thanks!
Paul

[kon]

Untested, but I see a couple things that need to be changed:

Code: Select all

#!g::
Xl.Worksheets(1).ShowAllData
AS := Xl.ActiveSheet
Xl.Intersect(AS.Columns("G"), AS.UsedRange).Replace("0*", "#N/A", 2)	; xlPart = 2
Xl.Intersect(AS.Columns("G"), AS.UsedRange).SpecialCells(2, 16).EntireRow.Delete	; xlCellTypeConstants = 2, xlErrors = 16
Return

[TLM]

kon beat me to it ( also untested ) ..
however this taught me how to create a union and find its intersection via AHK

Code: Select all

Xl.ActiveSheet.Range( "B2:C8`,C6:D11" ).Value := 1 ; escape comma to create union
Xl.ActiveSheet.Range( "B2:C8 C6:D11" ).Value := 2  ; leave space to find intersection range

Result:

this will definitely come in handy in the future

[PaulLeavitt]

Ahhhh! You're amazing! It works perfectly!!
So I'm curious now. Why does it require "AS" before the range designations? And why does changing xlPart to "2" have any effect?
Thanks so much Kon!

And that's really interesting TLM, So escaping the comma creates a union which in your example, only the range C6:C8 would have the value "1", correct?

Paul

[TLM]

Why does it require "AS" before the range designations?

For convenience AS := Xl.ActiveSheet creates an ActiveSheet object so you don't have to type it each time you need it.

So escaping the comma creates a union which in your example, only the range C6:C8 would have the value "1", correct?

When I created the union of rangesB2:C8 and C6:D11 they both get the value 1.
When I used B2:C8 C6:D11 to find the intersection, the intersecting cell range ( C6:C8 ) gets the value 2.

BTW after some testing, I've found that:
Xl.ActiveSheet.Range( "B2:C8 C6:D11" ).Address
Xl.Application.Intersect( Xl.ActiveSheet.Range( "B2:C8" ), Xl.ActiveSheet.Range( "C6:D11" ) ).Address
Both do the same thing

Also, it looks like there's no limit to how many unions you can make ( I've made 3 at once so far )

[kon]

So I'm curious now. Why does it require "AS" before the range designations?

...
Syntax

expression.ActiveSheet

expression A variable that represents an Application object.

Remarks

If you don’t specify an object qualifier, this property returns the active sheet in the active workbook.
...

In VBA you can use ActiveSheet.UsedRange because it knows to use its own application object if one isn't specified. But AutoHotkey can have multiple COM objects, or none at all, in one script. It has no way of automatically knowing that ActiveSheet belongs to a certain application object. So we specify it: Xl.ActiveSheet.UsedRange
In my previous post we save a reference to the active sheet with AS := Xl.ActiveSheet. Note that the following are equivalent:

Code: Select all

AS := Xl.ActiveSheet
Xl.Intersect(AS.Columns("G"), AS.UsedRange).Replace("0*", "#N/A", 2)    ; xlPart = 2

vs

Code: Select all

Xl.Intersect(Xl.ActiveSheet.Columns("G"), Xl.ActiveSheet.UsedRange).Replace("0*", "#N/A", 2)    ; xlPart = 2

And why does changing xlPart to "2" have any effect?

link

Also, it looks like there's no limit to how many unions you can make ( I've made 3 at once so far )

Pretty cool!

[TLM]

Also, it looks like there's no limit to how many unions you can make ( I've made 3 at once so far )

Pretty cool!

quite
not to fully derail this thread but multiple intersections don't seem to work unless I do this trick

Code: Select all

Xl.ActiveSheet.Range( R := "B2:D4`,D3:F5`,F4:H6`,H5:J7" ).Value := 1 ; multiple unions
 
For i, in ( v := StrSplit( R, "`," ) )
    if ( v.MaxIndex()-1 >= i )
        Xl.ActiveSheet.Range( v[ i ] " " v[ i+1 ] ).Value := 2

result:

perhaps there's a built in `excel` way of finding >1 intersection.. hrmm

[kon]

not to fully derail this thread but multiple intersections don't seem to work unless I do this trick
...
perhaps there's a built in `excel` way of finding >1 intersection.. hrmm

Returns a Range object that represents the rectangular intersection of two or more ranges.

It seems to me that a cell needs to be present in every range for it to be included in the returned range object. ie Cell D3 is the only cell in all three ranges:

Code: Select all

Xl.ActiveSheet.Range("B2:D4`,D1:F3`,D3:F5").Value := 1
Xl.ActiveSheet.Range("B2:D4 D1:F3 D3:F5").Value := 2

I might do something like this:

Code: Select all

Ranges := [	Xl.ActiveSheet.Range("B2:D4")
		  , Xl.ActiveSheet.Range("D3:F5")
		  , Xl.ActiveSheet.Range("F4:H6")
		  , Xl.ActiveSheet.Range("H5:J7")	]
for i, ThisRange in Ranges {
	ThisRange.Value := 1
	if (i = 1) {
		Combined := ThisRange
		continue
	}
	Xl.Intersect(Combined, ThisRange).Value := 2 ; the intersection of ThisRange and all of the previous ranges
	Combined := Xl.Union(Combined, ThisRange)
}

[TLM]

It seems to me that a cell needs to be present in every range for it to be included in the returned range object. ie Cell D3 is the only cell in all three ranges:

Code: Select all

Xl.ActiveSheet.Range("B2:D4`,D1:F3`,D3:F5").Value := 1
Xl.ActiveSheet.Range("B2:D4 D1:F3 D3:F5").Value := 2

yes indeed, tested ty

[PaulLeavitt]

Oh! Duh. Oops! I completely overlooked the fact that you declared "AS" to be a variable containing an address. Thanks for the explanation Kon! You all are super helpful! And thanks for the info on intersects TLM, I find I run into those with some regularity.

So it sounds like a lot of the problems I encounter in using AHK COM has to do with telling AHK what application I'm trying to work with. Does that sound right to you? Here's another problem I ran into.

I'm trying to duplicate a sheet using Xl.ActiveWorkbook.Sheets(1).Copy after:=ActiveWorkbook.Sheets(1) which works perfectly in Excel, but in AHK it skips everything after ".Copy" and simply creates a new workbook with the copied data. Is this a problem where it's not sure what to do with after:=ActiveWorkbook.Sheets(1)?

[kon]

In VBA, the option exists to write method calls two ways. Using Document.PrintOut Method (Word) as an example:
1. Named parameters. Ex (Word) ActiveDocument.PrintOut Background:=False, Copies:= 2
2. Account for each argument. Same method call as #1, but using this syntax: ActiveDocument.PrintOut False, , , , , , 2, , , , , , ,
In AHK, we need to use a syntax similar to #2, but we enclose the arguments in parenthesis, just like calling a normal AHK method or function. ie wdApp.ActiveDocument.PrintOut(False, , , , , , 2, , , , , , ,)

Untested (again), but I believe your code should look like: Xl.ActiveWorkbook.Sheets(1).Copy(,Xl.ActiveWorkbook.Sheets(1))

[PaulLeavitt]

Ok, that all makes sense, and your code worked beautifully Kon! Thanks so much! I'll keep method 2 in mind as I go forward.
Do you think that might be a subject worth covering in a thread all it's own? Information on COM in AHK is a bit sparse online. Just a thought.
Paul

[TLM]

In VBA, the option exists to write method calls two ways. Using Document.PrintOut Method (Word) as an example:
1. Named parameters. Ex (Word) ActiveDocument.PrintOut Background:=False, Copies:= 2
2. Account for each argument. Same method call as #1, but using this syntax: ActiveDocument.PrintOut False, , , , , , 2, , , , , , ,

partly why so many ppl hate vba lol

[kczx3]

This is somewhat related but how would one copy two non-adjacent rows of cells? I tried using the union method like so Range("A1:I1`,A534:I560").Copy() but this is copying everything in the range from 1 to 600. I just want to copy the table header row and a set of rows and paste into an outlook email as a table.