VB Application for Advanced filter Macro

[Poogy]

Hi all,
I really need your advice please!!

I'm trying to build an application that's main purpose is to perform
several Advanced Filter actions on an Excel sheet.
the idea is to open an xls file of report, and to split the data to
new sheets within the same workbook, by automating this simple
operation.

i recorded a macro which works perfect on specific spreadsheet, but
doesn't work as part of my application, to enable working with any
other spreadsheet.

Since my problem is (mainly) in the function Advanced Filter, can u
please tell me what's wrong with this code??
' open the file in excel
Dim xlApp As Object
Set xlApp = CreateObject("excel.application")
xlApp.Visible = True
xlApp.Workbooks.Open ("C:\Projects\myreport.xls")
' add new sheet
xlApp.Sheets.Add
xlApp.Sheets("Sheet1").Select
xlApp.Sheets("Sheet1").Name = "mySheet"
' perform advanced filter on the original worksheet (now it's
number 2)
xlApp.Worksheets(2).Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy,
CriteriaRange:=xlApp.Worksheets(2).Range("K1:K2"),
CopyToRange:=xlApp.Worksheets(2).Range("A500")



if there's any other way to perform it, please reply...

almost desperate....
Poogy

 

[Tom Ogilvy]

code looks like it should work unless I missed something. What do you
mean it doesn't work? Are you getting an error message? What is the
message? What line of code is highlighted?

I notice your code looks like it is being run from VB or some other
application other than Excel.

If so, then xlFiltercopy is probably not defined when you use it since it
appears you are using late binding. Try changing

Action:=xlFilterCopy,
to
Action:=2

I suspect xlFiltercopy, being undefined is providing the value 0 rather than
2 which is what you want.