C# and Excel advanced filter

Udi Goldstein

I'm trying to filter data from one workbook to another obne, but I get an unpsecified exception.

I can't find many examples for adavnced filtering in excel using c# and I don't know Where's exactly my error.

Here's my code:




public void excel()
    {
        Excel.Application xlApp;
        Excel.Workbook xlWorkBookImport;
        Excel.Workbook xlWorkBookOriginal;
        Excel.Worksheet xlWorkSheetImport;
        Excel.Worksheet xlWorkSheetOriginal;

        xlApp = new Excel.ApplicationClass();
        xlWorkBookImport = xlApp.Workbooks.Open("c:/open.xls");
        xlWorkBookOriginal = xlApp.Workbooks.Open("c:/open1.xls");
        xlWorkBookOriginal.Activate();
        xlWorkSheetOriginal = (Excel.Worksheet)xlWorkBookOriginal.Worksheets.get_Item(1);
        xlWorkSheetOriginal.get_Range("1:7").Delete();
        xlWorkBookImport.Activate();
        xlWorkSheetImport = (Excel.Worksheet)xlWorkBookImport.Worksheets.get_Item(1);
        xlWorkSheetOriginal.Cells.AdvancedFilter(Excel.XlFilterAction.xlFilterCopy, Type.Missing, CopyToRange: "a1:a6", Unique: true);

        xlApp.Visible = true;
    }

Anyone can help me with a solution?

Thanks,

Udi

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs14y ago
When I use your code, it works for that line. It gets an error on the AdvancedFilter call.

I am not experienced with Excel. Perhaps you need to use Excel to determine what to use for the filter, then put that in your code.

Be sure to call Quit for the Excel application object at the end of the program. If you have not been doing that and if you see multiple instances of Excel executing when you look at the processes using the Task Manager, then the best thing to do is to restart your system before proceeding and be sure you use Quit in your code. Be sure to use Quit even if your program exits due to an error.

Also it would help if you describe what the program is doing and/or describe the data that the program is processing. That can save time for people trying to help. Since the filter is not working, the sample code might not be sufficient for determining what the solution needs to be.
Udi Goldstein14y ago
Hi there,

I got past the 'open' code, because I tried the code before I've added the advanced filter code to it.
The last row of the code that works is the delete line:
xlWorkSheetOriginal.get_Range("1:7").Delete(); That's why I know fir sure that there's something wrong with the advanced filter code. Udi
Sam Hobbs14y ago
You should use try/catch. If you had then you would have gotten a better indication of the problem.

Also, when asking for help, you need to specify what line of code is getting an error such as this.

Your code is likely not making it past the open methods. In the filename, you are using forward slashes, not backslashes. I don't thnk that will work. You need to use a backslash and in C# a backslash needs to be escaped so you need to use two backslashes to get one backslash. I suggest putting the file in you Documents folder and then using the entire path in your source code. If you precede the file path string with an at sign ("@") then you can use only one backslash. So the open method might look like the following.

xlWorkBookImport = xlApp.Workbooks.Open(@"C:\Users\Sam\Documents\open.xlsx");

I think that what is happening is that the open fails but you do not get an error message from that. Then a subsequent method call is getting the confusing error. Confusing general errors such as what you are getting often result from ignorance of a preceding error.