Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Application for Advanced filter Macro
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.Adva ncedFilter _ 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Application for Advanced filter Macro
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. -- Regards, Tom Ogilvy "Poogy" wrote in message om... 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.Adva ncedFilter _ 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filter Macro Failing | Excel Discussion (Misc queries) | |||
Advanced Filter Macro | Excel Discussion (Misc queries) | |||
Advanced Filter Macro | Excel Discussion (Misc queries) | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |