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.

Please Login or Register  to view this content.

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

"David Layzell" <davel@cglmfg.com> wrote in message
news:x_qdnccqIo1mQ3jZnZ2dnUVZ_qKdnZ2d@golden.net...
> 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.
>
>

Please Login or Register  to view this content.

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

"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:OTGDVHuwGHA.2432@TK2MSFTNGP06.phx.gbl...
> 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
>
> "David Layzell" <davel@cglmfg.com> wrote in message
> news:x_qdnccqIo1mQ3jZnZ2dnUVZ_qKdnZ2d@golden.net...
>> 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.
>>
>>

Please Login or Register  to view this content.

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

"David Layzell" <davel@cglmfg.com> wrote in message
news:_6udnfmZVZm1bnjZnZ2dnUVZ_qOdnZ2d@golden.net...
> 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
>
> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:OTGDVHuwGHA.2432@TK2MSFTNGP06.phx.gbl...
>> 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
>>
>> "David Layzell" <davel@cglmfg.com> wrote in message
>> news:x_qdnccqIo1mQ3jZnZ2dnUVZ_qKdnZ2d@golden.net...
>>> 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.
>>>
>>>

Please Login or Register  to view this content.

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