VBScript MS Excel Problem

[David Layzell]

Hi All,

Just wondering if anyone knows the correct way to pass arguments to the
autofilter method when using VBScript to automate MS Excel. Below, please
find a code snippet that keeps failing.

Set o_excel = CreateObject("excel.application")
o_excel.visible = True
o_excel.workbooks.open v_file + ".csv"
Set o_wrksht = o_excel.activesheet
o_wrksht.cells(2, 1).autofilter
[B]o_wrksht.cells(2, 10).autofilter 10, "="[/B]

The last line returns an error, "Autofilter Method Of Range Class Failed".
Does anyone have any suggestions?

 

[Ed]

Hi, David. I've done AutoFilter within Excel VBA a few times, and very
little scripting. Excel VBA Help for the AutoFilter method shows the filter
applied to a range with the column identified in the Field argument. If you
can, step through or Stop this code just after

o_wrksht.cells(2, 1).autofilter

and see if your AutoFilter did indeed come on (the drop-down arrows are
showing). If not, then your next line would error because you can't set the
AutoFilter arguments if the filter isn't on.

If the AutoFilter is on, then just off the top of this shallow fount of
experience, I would say try something like:
o_wrksht.autofilter 10, "="

If that doesn't work, you might try setting an Excel range object and using
the filter on that.

HTH
Ed

Hi All,

Just wondering if anyone knows the correct way to pass arguments to the
autofilter method when using VBScript to automate MS Excel. Below, please
find a code snippet that keeps failing.

Set o_excel = CreateObject("excel.application")
o_excel.visible = True
o_excel.workbooks.open v_file + ".csv"
Set o_wrksht = o_excel.activesheet
o_wrksht.cells(2, 1).autofilter
[B]o_wrksht.cells(2, 10).autofilter 10, "="[/B]

The last line returns an error, "Autofilter Method Of Range Class Failed".
Does anyone have any suggestions?

 

[David Layzell]

Hi Ed,

Thanks for the wonderful response. You're 100x more helpful than Google
today. However, I've discovered the problem. Autofilter is indeed on,
however, when I try to autofilter with that particular cell selected, the
command fails, as there is no data in that column. Turns out the problem
wasn't with this specific command, rather, previous processing errors.
Thanks again for the help!

Dave

Hi, David. I've done AutoFilter within Excel VBA a few times, and very
little scripting. Excel VBA Help for the AutoFilter method shows the
filter applied to a range with the column identified in the Field
argument. If you can, step through or Stop this code just after

o_wrksht.cells(2, 1).autofilter

and see if your AutoFilter did indeed come on (the drop-down arrows are
showing). If not, then your next line would error because you can't set
the AutoFilter arguments if the filter isn't on.

If the AutoFilter is on, then just off the top of this shallow fount of
experience, I would say try something like:
o_wrksht.autofilter 10, "="

If that doesn't work, you might try setting an Excel range object and
using the filter on that.

HTH
Ed

Hi All,

Just wondering if anyone knows the correct way to pass arguments to the
autofilter method when using VBScript to automate MS Excel. Below, please
find a code snippet that keeps failing.

Set o_excel = CreateObject("excel.application")
o_excel.visible = True
o_excel.workbooks.open v_file + ".csv"
Set o_wrksht = o_excel.activesheet
o_wrksht.cells(2, 1).autofilter
[B]o_wrksht.cells(2, 10).autofilter 10, "="[/B]

The last line returns an error, "Autofilter Method Of Range Class
Failed". Does anyone have any suggestions?

 

[Ed]

Turns out the problem wasn't with this specific command, rather, previous

processing errors.

I hate it when that happens!! 8>{

Glad you found the issue. It's always good to see a problem solved.

Ed

Hi Ed,

Thanks for the wonderful response. You're 100x more helpful than Google
today. However, I've discovered the problem. Autofilter is indeed on,
however, when I try to autofilter with that particular cell selected, the
command fails, as there is no data in that column. Turns out the problem
wasn't with this specific command, rather, previous processing errors.
Thanks again for the help!

Dave

Hi, David. I've done AutoFilter within Excel VBA a few times, and very
little scripting. Excel VBA Help for the AutoFilter method shows the
filter applied to a range with the column identified in the Field
argument. If you can, step through or Stop this code just after

o_wrksht.cells(2, 1).autofilter

and see if your AutoFilter did indeed come on (the drop-down arrows are
showing). If not, then your next line would error because you can't set
the AutoFilter arguments if the filter isn't on.

If the AutoFilter is on, then just off the top of this shallow fount of
experience, I would say try something like:
o_wrksht.autofilter 10, "="

If that doesn't work, you might try setting an Excel range object and
using the filter on that.

HTH
Ed

Hi All,

Just wondering if anyone knows the correct way to pass arguments to the
autofilter method when using VBScript to automate MS Excel. Below,
please find a code snippet that keeps failing.

Set o_excel = CreateObject("excel.application")
o_excel.visible = True
o_excel.workbooks.open v_file + ".csv"
Set o_wrksht = o_excel.activesheet
o_wrksht.cells(2, 1).autofilter
[B]o_wrksht.cells(2, 10).autofilter 10, "="[/B]

The last line returns an error, "Autofilter Method Of Range Class
Failed". Does anyone have any suggestions?