Please HELP!!!

[laurent david]

I want to filter data in an Excel Worksheet using the automatic filter. I tried to use the [_FilterDataBase] hidden object. It works, but the following error occur regularly at the line where [_FilterDataBase] is used : error 424.

Can you help me, I didn't find sufficient documentation to resolve this problem...

Thanks.


Before running that program, I apply a filter on the worksheet.


Sub MyFilter()
Dim CurrentWorksheet as Excel.Worksheet
Dim XlApp as Excel.Application
Dim Fic
Dim strExcelPath as string
Dim strExcelFile as string
Dim strOutptPath as string
Dim strOutptFile as string
Dim FirstCol as Integer
Dim FirstLine as Integer
Dim StartLine as Integer
Dim LastCol as Integer
Dim LastLine as Integer
Dim areaCount as Integer
Dim i as Integer
Dim j as Integer
Dim k as Integer
Dim Range1

strExcelPath = "c:\temp"
strExcelFile = "class1.xls"
strOutptPath = "c:\temp"
strOutptFile = "test.dat"

set XlApp = CreateObject("Excel.Application")
XlApp.Visible = true
XlApp.Workbooks.Open (strExcelPath & "\" & strExcelFile)
XlApp.Workbooks(strExcelFile).Worksheets(mid(strExcelFile, 1, len(strExcelFile) - 4)).Activate
set CurrentWorksheet = XlApp.Windows(1).ActiveSheet

Fic = FreeFile()
Open strOutptPath & "\" & strOutptFile for Output as #Fic

CurrentWorksheet.Activate
[_FilterDataBase].SpecialCells(xlVisible).Select
areaCount = Selection.Areas.Count

'1st line of excel Worksheet contains titles : Data start at 2nd line
for i = 1 to areaCount
set Range1 = Selection.Areas(i)
FirstCol = Range1.Column
FirstLine = Range1.Row
LastCol = FirstCol + Range1.Columns.Count - 1
LastLine = FirstLine + Range1.Rows.Count - 1
StartLine = 1
If i = 1 And FirstLine = 1 then
If LastLine = 1 then
GoTo eti
End If
StartLine = 2
End If
for j = StartLine to LastLine - FirstLine + 1
print #Fic, Range1(j, 2).Value
next j
eti:
next i

Close #Fic
XlApp.ActiveWorkbook.Close wdDoNotSaveChanges
XlApp.Application.Quit
set XlApp = nothing
End Sub




Laurent