Excel Automation problem with AutoFilters

[Nexus]

I've developed a VB application that does some OLE Automation using Excel.

I want to check that there is no autofilter currently active and that all
rows are shown (but still keep the AutoFilter enabled).

The code to this is within a VBA macro is

Activesheet.ShowAllData

(I believe)

However when I try to do this within VB e.g

Dim X as New Excel.Application
.......
X.ActiveSheet.ShowAllData

I get object does not support method.

Any ideas what I am doing wrong?

 

[Jim Cone]

My guess is that the application is not visible, so nothing is active.
It is best when automating Excel to set object references
to all Excel objects and avoid the use of "Active...".
Try something like this...

Dim xlApp As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet

Set xlApp = New Excel.Application
Set WB = xlApp.Workbooks.Add
Set WS = WB.Sheets(1)
'other stuff
WS.ShowAllData

Make sure to set all object variables to Nothing
when exiting Excel.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Nexus" <[email protected]>
wrote in message
I've developed a VB application that does some OLE Automation using Excel.

I want to check that there is no autofilter currently active and that all
rows are shown (but still keep the AutoFilter enabled).
The code to this is within a VBA macro is
Activesheet.ShowAllData
(I believe)

However when I try to do this within VB e.g

Dim X as New Excel.Application
.......
X.ActiveSheet.ShowAllData

I get object does not support method.
Any ideas what I am doing wrong?