Javascript Disabled Detected

You currently have javascript disabled. Several functions may not work. Please re-enable javascript to access full functionality.

COM, Excel autofilter question

Started by stressbaby , Oct 09 2009 08:08 PM

Please log in to reply

9 replies to this topic

#1 stressbaby

Members
150 posts

Posted 09 October 2009 - 08:08 PM

Hello,

Sorry if this is OT. I have a spreadsheet which comes to me with a series of built in autofilters and macros. Each macro has a box in the first (header) row, with a label, and you can run the macros by clicking on the box as if it were a giant button.

With the help of Jethrow and others here, I wrote a nice executable which merges patient demographic data to the spreadsheet. It adds columns to the right side of the table with this information. This has been tested by a couple of other docs around the country and it works great both in Excel 2003 and 2007.

However, after running the ahk/executable and adding the demographics, the macros no longer work. When clicked, Excel runs and runs forever, with "Calculating: (2 Processors(2)): XX%" in the lower right. It runs up to 100% and starts over again and again. I eventually have to kill it through task manager. It has something to do with column headers; I added a couple of columns with "dummy data" at the end and found that with a certain amount of dummy data in columns added to the right, the headers are no longer headers, they start showing up in the middle of the spreadsheet. The guy in SC who wrote the macros suggested changing "Header:=xlGuess" to "Header:=xlYes" in the macro, but that didn't work.

In case anyone can see the flaw in the built in macros, here is the entire code of one of the macros which won't run; it is possible that I could get him to revise them if the flaw is found:

Sub CHD_LDL100()
'
' CHD_LDL100 Macro

'
    Selection.AutoFilter Field:=1
    Selection.AutoFilter Field:=2
    Selection.AutoFilter Field:=3
    Selection.AutoFilter Field:=4
    Selection.AutoFilter Field:=5
    Selection.AutoFilter Field:=6
    Selection.AutoFilter Field:=7
    Selection.AutoFilter Field:=8
    Selection.AutoFilter Field:=9
    Selection.AutoFilter Field:=10
    Selection.AutoFilter Field:=11
    Selection.AutoFilter Field:=12
    Selection.AutoFilter Field:=13
    Selection.AutoFilter Field:=14
    Selection.AutoFilter Field:=15
    Selection.AutoFilter Field:=16
    Selection.AutoFilter Field:=17
    Selection.AutoFilter Field:=18
    Selection.AutoFilter Field:=19
    Selection.AutoFilter Field:=20
    Selection.AutoFilter Field:=21
    Selection.AutoFilter Field:=22
    Selection.AutoFilter Field:=23
    Selection.AutoFilter Field:=24
    Selection.AutoFilter Field:=25
    Selection.AutoFilter Field:=26
    Selection.AutoFilter Field:=27
    Selection.AutoFilter Field:=28
    Selection.AutoFilter Field:=29
    Selection.AutoFilter Field:=30
    Selection.AutoFilter Field:=31
    Selection.AutoFilter Field:=32
    Selection.AutoFilter Field:=33
    Selection.AutoFilter Field:=34
    Selection.AutoFilter Field:=35
    Selection.AutoFilter Field:=36
    Selection.AutoFilter Field:=37
    Selection.AutoFilter Field:=38
    Selection.AutoFilter Field:=39
    Selection.AutoFilter Field:=40
    Selection.AutoFilter Field:=41
    Selection.AutoFilter Field:=42
    Selection.AutoFilter Field:=43
    Selection.AutoFilter Field:=44
    Selection.AutoFilter Field:=45
    Selection.AutoFilter Field:=46
    Selection.AutoFilter Field:=47
    Selection.AutoFilter Field:=48
    Selection.AutoFilter Field:=49
    Selection.AutoFilter Field:=50
    Selection.AutoFilter Field:=51
    Selection.AutoFilter Field:=52
    Selection.AutoFilter Field:=53
    Selection.AutoFilter Field:=54
    Selection.AutoFilter Field:=55
    Selection.AutoFilter Field:=56
    Selection.AutoFilter Field:=57
    Selection.AutoFilter Field:=58
    Selection.AutoFilter Field:=59
    Selection.AutoFilter Field:=60
    Selection.AutoFilter Field:=61
    Selection.AutoFilter Field:=62
    Selection.AutoFilter Field:=63
    Selection.AutoFilter Field:=64
    Selection.AutoFilter Field:=65
    Selection.AutoFilter Field:=66
    Selection.AutoFilter Field:=67
    Selection.AutoFilter Field:=68
    Selection.AutoFilter Field:=69
    Selection.AutoFilter Field:=70
    Selection.AutoFilter Field:=71
    Selection.AutoFilter Field:=72
    Selection.AutoFilter Field:=73
    Selection.AutoFilter Field:=74
    Selection.AutoFilter Field:=75
    Selection.AutoFilter Field:=76
    Selection.AutoFilter Field:=77
    Selection.AutoFilter Field:=78
    Selection.AutoFilter Field:=79
    Selection.AutoFilter Field:=80
    Selection.AutoFilter Field:=81
    Selection.AutoFilter Field:=82
    Selection.AutoFilter Field:=83
    Selection.AutoFilter Field:=84
    Selection.AutoFilter Field:=85
    Selection.AutoFilter Field:=86
    Selection.AutoFilter Field:=87
    Selection.AutoFilter Field:=88
    Selection.AutoFilter Field:=89
    Selection.AutoFilter Field:=90
    Selection.AutoFilter Field:=91
    Selection.AutoFilter Field:=92
    Selection.AutoFilter Field:=93
    Selection.AutoFilter Field:=94
    Selection.AutoFilter Field:=95
    Selection.AutoFilter Field:=96
    Selection.AutoFilter Field:=97
    Selection.AutoFilter Field:=98
    Selection.AutoFilter Field:=99
    Selection.AutoFilter Field:=100
    Selection.AutoFilter Field:=101
    Selection.AutoFilter Field:=102
    Selection.AutoFilter Field:=103
    Selection.AutoFilter Field:=104
    Selection.AutoFilter Field:=105
    Selection.AutoFilter Field:=106
    Selection.AutoFilter Field:=107
    Selection.AutoFilter Field:=108
    Selection.AutoFilter Field:=109
    Selection.AutoFilter Field:=110
    Selection.AutoFilter Field:=111
    Selection.AutoFilter Field:=112
    Selection.AutoFilter Field:=113
    Selection.AutoFilter Field:=114
    Selection.AutoFilter Field:=115
    Selection.AutoFilter Field:=116
    Selection.AutoFilter Field:=117
    Selection.AutoFilter Field:=118
    Selection.AutoFilter Field:=119
    Selection.AutoFilter Field:=120
    Selection.AutoFilter Field:=121
    Selection.AutoFilter Field:=122
    Selection.AutoFilter Field:=123
    Selection.AutoFilter Field:=124
    Selection.AutoFilter Field:=125
    Selection.AutoFilter Field:=126
    Selection.AutoFilter Field:=127
    Selection.AutoFilter Field:=128
    Selection.AutoFilter Field:=129
    Selection.AutoFilter Field:=130
    Selection.AutoFilter Field:=131
    Selection.AutoFilter Field:=132
    Selection.AutoFilter Field:=133
    Selection.AutoFilter Field:=134
    Selection.AutoFilter Field:=135
    Selection.AutoFilter Field:=136
    Selection.AutoFilter Field:=137
    Selection.AutoFilter Field:=138
    Selection.AutoFilter Field:=139
    Selection.AutoFilter Field:=140
    Selection.AutoFilter Field:=141
    Selection.AutoFilter Field:=142
    Selection.AutoFilter Field:=143
    Selection.AutoFilter Field:=144
    Selection.AutoFilter Field:=145
    Selection.AutoFilter Field:=146
    Selection.AutoFilter Field:=147
    Selection.AutoFilter Field:=148
    Selection.AutoFilter Field:=149
    Selection.AutoFilter Field:=150
    Selection.AutoFilter Field:=151
    Selection.AutoFilter Field:=152
    Selection.AutoFilter Field:=153
    Selection.AutoFilter Field:=154
    Selection.AutoFilter Field:=155
    Selection.AutoFilter Field:=156
    Selection.AutoFilter Field:=157
    Selection.AutoFilter Field:=158
    Selection.AutoFilter Field:=12, Criteria1:="Y"
    Selection.AutoFilter Field:=35, Criteria1:=">=100", Operator:=xlAnd
    Cells.Select
    Range("A1").Select
    Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range("AG2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    Range("A1").Select
End Sub

So anyway, my attempted workaround is to send autofilter commands on my own with COM. I had to tweak quotation marks but I finally got an ahk script that will save and load, but it still won't run even a simplifed version of the VB command.

From the macro:

Sub macro1()
'
' macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
    ActiveSheet.Range("$A$1:$FB$4571").AutoFilter Field:=12, Criteria1:="=Y", _
        Operator:=xlAnd
End Sub

In AHK:

COM_Init()
pxl := GetExcel()
COM_Invoke(pxl, "ActiveSheet.Range['$A$1:$FB$4571'].AutoFilter Field:=12, Criteria1:='=Y', Operator:=xlAnd")
COM_Release(pxl), COM_Term()

I get COM Error:

Function Name: "AutoFilter Field:=12, Criteria1:='=Y', Operator:=xlAnd"
ERROR: Unknown name. (0x80020006)

So my question is, can someone tell me the proper syntax for this command? Ideally I would also need an "AND" statement like this:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
    ActiveSheet.Range("$A$1:$FB$4571").AutoFilter Field:=12, Criteria1:="=Y", _
        Operator:=xlAnd
    ActiveSheet.Range("$A$1:$FB$4571").AutoFilter Field:=35, Criteria1:=">=100" _
        , Operator:=xlAnd
End Sub

Thanks in advance for any help.

Back to top

#2 tank

Members
4101 posts

Posted 10 October 2009 - 02:30 AM

great question thanks for giving detail
i can answer part of this

COM_Invoke(pxl, "ActiveSheet.Range['$A$1:$FB$4571'].AutoFilter",12,"=Y",1)

untested but in theory it should be this

Back to top

#3 stressbaby

Members
150 posts

Posted 11 October 2009 - 12:17 AM

I love this forum.
Tank, it works like a charm! I appreciate all of your contributions!

While not critical, it would be cool to be able to "reset" to all patients. There is a built-in macro for this, and the code is here:

Sub All_pts()
'
' All_pts Macro
' Macro recorded 6/22/2005 by xxxx
'

'
    Selection.AutoFilter Field:=1
    Selection.AutoFilter Field:=2
    Selection.AutoFilter Field:=3
    Selection.AutoFilter Field:=4
    Selection.AutoFilter Field:=5
    Selection.AutoFilter Field:=6
    Selection.AutoFilter Field:=7
    Selection.AutoFilter Field:=8
    Selection.AutoFilter Field:=9
    Selection.AutoFilter Field:=10
    Selection.AutoFilter Field:=11
    Selection.AutoFilter Field:=12
    Selection.AutoFilter Field:=13
    Selection.AutoFilter Field:=14
    Selection.AutoFilter Field:=15
    Selection.AutoFilter Field:=16
    Selection.AutoFilter Field:=17
    Selection.AutoFilter Field:=18
    Selection.AutoFilter Field:=19
    Selection.AutoFilter Field:=20
    Selection.AutoFilter Field:=21
    Selection.AutoFilter Field:=22
    Selection.AutoFilter Field:=23
    Selection.AutoFilter Field:=24
    Selection.AutoFilter Field:=25
    Selection.AutoFilter Field:=26
    Selection.AutoFilter Field:=27
    Selection.AutoFilter Field:=28
    Selection.AutoFilter Field:=29
    Selection.AutoFilter Field:=30
    Selection.AutoFilter Field:=31
    Selection.AutoFilter Field:=32
    Selection.AutoFilter Field:=33
    Selection.AutoFilter Field:=34
    Selection.AutoFilter Field:=35
    Selection.AutoFilter Field:=36
    Selection.AutoFilter Field:=37
    Selection.AutoFilter Field:=38
    Selection.AutoFilter Field:=39
    Selection.AutoFilter Field:=40
    Selection.AutoFilter Field:=41
    Selection.AutoFilter Field:=42
    Selection.AutoFilter Field:=43
    Selection.AutoFilter Field:=44
    Selection.AutoFilter Field:=45
    Selection.AutoFilter Field:=46
    Selection.AutoFilter Field:=47
    Selection.AutoFilter Field:=48
    Selection.AutoFilter Field:=49
    Selection.AutoFilter Field:=50
    Selection.AutoFilter Field:=51
    Selection.AutoFilter Field:=52
    Selection.AutoFilter Field:=53
    Selection.AutoFilter Field:=54
    Selection.AutoFilter Field:=55
    Selection.AutoFilter Field:=56
    Selection.AutoFilter Field:=57
    Selection.AutoFilter Field:=58
    Selection.AutoFilter Field:=59
    Selection.AutoFilter Field:=60
    Selection.AutoFilter Field:=61
    Selection.AutoFilter Field:=62
    Selection.AutoFilter Field:=63
    Selection.AutoFilter Field:=64
    Selection.AutoFilter Field:=65
    Selection.AutoFilter Field:=66
    Selection.AutoFilter Field:=67
    Selection.AutoFilter Field:=68
    Selection.AutoFilter Field:=69
    Selection.AutoFilter Field:=70
    Selection.AutoFilter Field:=71
    Selection.AutoFilter Field:=72
    Selection.AutoFilter Field:=73
    Selection.AutoFilter Field:=74
    Selection.AutoFilter Field:=75
    Selection.AutoFilter Field:=76
    Selection.AutoFilter Field:=77
    Selection.AutoFilter Field:=78
    Selection.AutoFilter Field:=79
    Selection.AutoFilter Field:=80
    Selection.AutoFilter Field:=81
    Selection.AutoFilter Field:=82
    Selection.AutoFilter Field:=83
    Selection.AutoFilter Field:=84
    Selection.AutoFilter Field:=85
    Selection.AutoFilter Field:=86
    Selection.AutoFilter Field:=87
    Selection.AutoFilter Field:=88
    Selection.AutoFilter Field:=89
    Selection.AutoFilter Field:=90
    Selection.AutoFilter Field:=91
    Selection.AutoFilter Field:=92
    Selection.AutoFilter Field:=93
    Selection.AutoFilter Field:=94
    Selection.AutoFilter Field:=95
    Selection.AutoFilter Field:=96
    Selection.AutoFilter Field:=97
    Selection.AutoFilter Field:=98
    Selection.AutoFilter Field:=99
    Selection.AutoFilter Field:=100
    Selection.AutoFilter Field:=101
    Selection.AutoFilter Field:=102
    Selection.AutoFilter Field:=103
    Selection.AutoFilter Field:=104
    Selection.AutoFilter Field:=105
    Selection.AutoFilter Field:=106
    Selection.AutoFilter Field:=107
    Selection.AutoFilter Field:=108
    Selection.AutoFilter Field:=109
    Selection.AutoFilter Field:=110
    Selection.AutoFilter Field:=111
    Selection.AutoFilter Field:=112
    Selection.AutoFilter Field:=113
    Selection.AutoFilter Field:=114
    Selection.AutoFilter Field:=115
    Selection.AutoFilter Field:=116
    Selection.AutoFilter Field:=117
    Selection.AutoFilter Field:=118
    Selection.AutoFilter Field:=119
    Selection.AutoFilter Field:=120
    Selection.AutoFilter Field:=121
    Selection.AutoFilter Field:=122
    Selection.AutoFilter Field:=123
    Selection.AutoFilter Field:=124
    Selection.AutoFilter Field:=125
    Selection.AutoFilter Field:=126
    Selection.AutoFilter Field:=127
    Selection.AutoFilter Field:=128
    Selection.AutoFilter Field:=129
    Selection.AutoFilter Field:=130
    Selection.AutoFilter Field:=131
    Selection.AutoFilter Field:=132
    Selection.AutoFilter Field:=133
    Selection.AutoFilter Field:=134
    Selection.AutoFilter Field:=135
    Selection.AutoFilter Field:=136
    Selection.AutoFilter Field:=137
    Selection.AutoFilter Field:=138
    Selection.AutoFilter Field:=139
    Selection.AutoFilter Field:=140
    Selection.AutoFilter Field:=141
    Selection.AutoFilter Field:=142
    Selection.AutoFilter Field:=143
    Selection.AutoFilter Field:=144
    Selection.AutoFilter Field:=145
    Selection.AutoFilter Field:=146
    Selection.AutoFilter Field:=147
    Selection.AutoFilter Field:=148
    Selection.AutoFilter Field:=149
    Selection.AutoFilter Field:=150
    Selection.AutoFilter Field:=151

    Selection.AutoFilter Field:=152
    Selection.AutoFilter Field:=153
    Selection.AutoFilter Field:=154
    Selection.AutoFilter Field:=155
    Selection.AutoFilter Field:=156
    Selection.AutoFilter Field:=157
    Selection.AutoFilter Field:=158
    
    Cells.Select
    Range("A1").Select
    Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range("E2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    Range("A1").Select
End Sub

Back to top

#4 stressbaby

Members
150 posts

Posted 11 October 2009 - 08:58 PM

Figured it out, easy enough!

clearFilters:
WinActivate, Microsoft Excel
WinWaitActive, Microsoft Excel
SendInput !ac
return

Back to top

#5 stressbaby

Members
150 posts

Posted 24 November 2009 - 12:09 AM

In my Excel spreadsheet, I was screening for those women over 40 who had never had a mammogram. Here was the code, worked great:

F_MAMM40Extract:
COM_Init()
pxl := GetExcel()
macro_rows := COM_Invoke(pxl, "ActiveSheet.UsedRange.Rows.Count")
macro_columns := COM_Invoke(pxl, "ActiveSheet.UsedRange.Columns.Count")
COM_Invoke(pxl, "Cells.item[" macro_columns "].item[2].Select")
macroAddress := COM_Invoke(pxl, "ActiveCell.Address") ;$FC$2
macroColumn := SubStr(macroAddress,2,InStr(Address,"$",0,2)-2) ; FC
macroRange = $A$1:$%macroColumn%$%macro_rows%
[color=red]COM_Invoke(pxl, "ActiveSheet.Range[" macroRange "].AutoFilter",4,"=F",1) 
COM_Invoke(pxl, "ActiveSheet.Range[" macroRange "].AutoFilter",5,">=40",1) 
COM_Invoke(pxl, "ActiveSheet.Range[" macroRange "].AutoFilter",56,"=",1) [/color]
COM_Release(pxl), COM_Term()
return

Now there are new guidelines for mammograms and I need to filter by age >=50 AND age <=74. This doesn't work:

F_MAMM50_74Extract:
COM_Init()
pxl := GetExcel()
macro_rows := COM_Invoke(pxl, "ActiveSheet.UsedRange.Rows.Count")
macro_columns := COM_Invoke(pxl, "ActiveSheet.UsedRange.Columns.Count")
COM_Invoke(pxl, "Cells.item[" macro_columns "].item[2].Select")
macroAddress := COM_Invoke(pxl, "ActiveCell.Address") ;$FC$2
macroColumn := SubStr(macroAddress,2,InStr(Address,"$",0,2)-2) ; FC
macroRange = $A$1:$%macroColumn%$%macro_rows%
[color=red]COM_Invoke(pxl, "ActiveSheet.Range[" macroRange "].AutoFilter",4,"=F",1) 
COM_Invoke(pxl, "ActiveSheet.Range[" macroRange "].AutoFilter",5,">=50",1) 
COM_Invoke(pxl, "ActiveSheet.Range[" macroRange "].AutoFilter",5,"<=74",1) 
COM_Invoke(pxl, "ActiveSheet.Range[" macroRange "].AutoFilter",56,"=",1) [/color]
COM_Release(pxl), COM_Term()
return

The filter basically ignores the >=50 command and gives me all the women <=74 who haven't had a mammogram... down to age 0!

I made a macro that filters for age between 50 and 74:

ActiveSheet.Range("$A$1:$FB$4751").AutoFilter Field:=5, Criteria1:=">=50", _
        Operator:=xlAnd, Criteria2:="<=74"

I'm just not sure of the COM syntax to autofilter using two criteria from the same field.

Thanks in advance.

Back to top

#6 tank

Members
4101 posts

Posted 24 November 2009 - 01:51 AM

great detail again

com_invoke(pxl, "ActiveSheet.Range[" macroRange "].AutoFilter",4,">=50",1,"<=74")

of course untested

Back to top

#7 stressbaby

Members
150 posts

Posted 24 November 2009 - 02:25 PM

Of course, it works.
Thanks again, tank.

Back to top

#8 Inad

Members
3 posts

Posted 30 April 2012 - 03:32 PM

Hi there,

I try to get a cvs file filtered.

I need to filter in column F for UNR (get this via user input) and as output I need the associated data in column B.

This is what I tried, but it is not working.

Workbook := ComObjGet(FilePath)

a :=Workbook.Sheets(1)
a.Range["A1:J10"].AutoFilter , "cn User", UNR, 1

I get the autofilter set, but it is not filtering for UNR

Can anyone help?

Thanks in advanced