Filter Excel Data from VB.net
Filter Excel Data from VB.net
Filter Excel Data from VB.net
Filter Excel Data from VB.net
Filter Excel Data from VB.net
Filter Excel Data from VB.net Filter Excel Data from VB.net Filter Excel Data from VB.net Filter Excel Data from VB.net Filter Excel Data from VB.net Filter Excel Data from VB.net Filter Excel Data from VB.net Filter Excel Data from VB.net
Filter Excel Data from VB.net Filter Excel Data from VB.net
Filter Excel Data from VB.net
Go Back  Xtreme Visual Basic Talk > > > Filter Excel Data from VB.net


Reply
 
Thread Tools Display Modes
  #1  
Old 12-06-2006, 05:07 PM
Sasijrao Sasijrao is offline
Newcomer
 
Join Date: Dec 2006
Posts: 4
Default Filter Excel Data from VB.net


Can any one please explain to me how can I pass criteria to filter in Vb.net/C# ?
(The application opens a EXCEl sheet filters data based on passed values and copies the filterd data into new work book and emails it). Am not able to figure out how to filter data in excel via Vb.net automation.
I have some thing like this..
Dim range1 As Excel.Range = xlsSheet.Range("A8:R8")
range1.Select()
xlsSheet.Application.Selection.AutoFilter()
'In the given range filter for the criteria where the field 1 has to be filtered by "USR1A1"
xlsSheet.Application.Selection.filter(Filed:=1, Criteria:="USR1A1")

Any help is greatly appreciated.
Reply With Quote
  #2  
Old 12-07-2006, 05:05 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Hi,

I've made an example that hopefully will get you started. I use an Excel workbook with a worksheet named "Data". In this worksheet there exist three fields (Department, Status and Amount).

For each of these fields there exist one condition which is used in the filtering process:

- First condition is "equal A"
- Second condition is "equal 1" or "larger then 1"
- Third condition is "not empty".

It's notable that these conditions are all of the datatype string.

Code:
Sub XL_Autofiltering() Dim xlApp As New Excel.Application Dim xlwbook As Excel.Workbook = xlApp.Workbooks.Open("c:\Autofilter.xls") Dim xlWsheet As Excel.Worksheet = CType(xlwbook.Worksheets("Data"), Excel.Worksheet) Dim xlRange As Excel.Range = xlWsheet.UsedRange Dim i As Integer = 0 'An array of the conditions Dim arrCondition() As String = {"A", ">=1", "<>"} 'An array of number of fields. Dim arrFields() As Integer = {1, 2, 3} 'Iterate through the fields and conditions. For i = 0 To 2 xlRange.AutoFilter(Field:=arrFields(i), Criteria1:=arrCondition(i)) Next 'Save the workbook. xlwbook.Save() 'Make Excel visible and available to the user. With xlApp .Visible = True .UserControl = True End With 'Cleaning up and prepare the objects for the Garbish collector. xlRange = Nothing xlWsheet = Nothing xlwbook = Nothing xlApp = Nothing End Sub
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
  #3  
Old 12-18-2006, 12:11 PM
Sasijrao Sasijrao is offline
Newcomer
 
Join Date: Dec 2006
Posts: 4
Default Select filtered Data in Excel

thanks for the reply.

how would I select the diplayed rows in excel using vb.net?

Is ther an equivalent to
'Selection.CurrentRegion.Select()
'Selection.Copy()

Any help is appreciated
Reply With Quote
  #4  
Old 12-18-2006, 01:08 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Well, if your Excel Application reference is defined as:
Code:
Dim xlApp As New Excel.Application
Then you could use:
Code:
xlApp.Selection.CurrentRegion.Select() xlApp.Selection.Copy()
But this is poor code. Better would be:
Code:
xlApp.Selection.CurrentRegion.Copy()
Better still is to not rely on user-operations such as the current Active Cell or Selection. You should specify precisely which cell you mean, say:
Code:
xlApp.Range("A1").CurrentRegion.Copy()
Never use xlApp.Select() or xlApp.Selection if you can avoid it...

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #5  
Old 12-18-2006, 02:26 PM
Sasijrao Sasijrao is offline
Newcomer
 
Join Date: Dec 2006
Posts: 4
Default Help with Excel Automation using VB.net

Thank you mike for your reply.
Here is my proble,.
1. I have an excel file which I have to open the file and filter data using criteria and copy the filtered data only(Displayed rows in excel) to a new workbook and save it.

Do you have an code samples for this?

Thanks in advance
Reply With Quote
  #6  
Old 12-18-2006, 03:13 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Sorry, no it doesn't work that way around here. We'll help you through it, but you have to do the work yourself. But we're here when you get stuck and here to help you learn.

The example that Dennis gave in post #2 is excellent.

Another idea is to use the macro recorder found within Excel in > Tools > Macros > Record Macro... Then execute the filter, turn the recorder off, and then hit Alt+F1 to open the VBA IDE and see what commands were made. You will need to clean up the code that results, but it is definately a start. From there you can put your cursor within key words like "AutoFilter" and hit the F1 key to get help on that command, etc...

This should get you going. Show us what you can come up with, and we'll help you through it...

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #7  
Old 12-18-2006, 06:08 PM
Sasijrao Sasijrao is offline
Newcomer
 
Join Date: Dec 2006
Posts: 4
Default Help with programming

Code:
Dim xlsApp As Excel.Application Dim xlsWB As Excel.Workbook Dim xlsSheet As Excel.Worksheet Dim xlsCell As Excel.Range Dim xlsDatei As String xlsApp = New Excel.Application xlsApp.Visible = True xlsWB = xlsApp.Workbooks.Open("c:\USeWithVBNET.xls") xlsSheet = xlsWB.Worksheets(1) 'xlsSheet.Range("A8:R8").Select() Dim range1 As Excel.Range range1 = xlsSheet.Range("G8") Range1.Select() Range1.Formula = " " Range1 = xlsSheet.Range("N8") Range1.Select() Range1.Formula = " " 'ActiveCell.FormulaR1C1 = "" range1 = xlsSheet.Range("A8:C8") range1.Select() xlsSheet.Application.Selection.AutoFilter() range1.AutoFilter(Field:=1, Criteria1:="USFCR2") 'xlsSheet.Application.Selection.filter(Field:=1, Criteria:="USFCR1") 'xlsSheet.Rows.CurrentRegion.Select() 'xlsSheet.Rows.CurrentRegion.Copy() 'Selection.CurrentRegion.Select() 'Selection.Copy() xlsApp.Selection.CurrentRegion.Select() xlsApp.Selection.COPY() Dim rData As Excel.Range Dim RcopyData As Excel.Range rData = xlsSheet.Range("A1:Z1", xlsSheet.Range("A1:Z1").End(Excel.XlDirection.xlUp)) ' createworkbook(xlsApp.Selection.CurrentRegion.Copy()) ''''''************************************************** Dim objApp As Excel.Application Dim objBook As Excel.Workbook Dim objBooks As Excel.Workbooks Dim objSheets As Excel.Sheets Dim objSheet As Excel.Worksheet Dim objrange As Excel.Range ' Instantiate Excel and start a new workbook. objApp = New Excel.Application ' This works 'objApp = CreateObject("Excel.Application") ' This ALSO works objBooks = objApp.Workbooks objBook = objBooks.Add objSheets = objBook.Worksheets objSheet = objSheets.Item(1) objrange = objSheet.Range("A1") objrange.Select() [B][COLOR="Red"]objSheet.Paste(xlsApp.Selection.COPY())[/COLOR][/B] 'Set the range value. [B][COLOR="Blue"]objrange.Value = rData[/COLOR][/B] 'Return control of Excel to the user. objApp.Visible = True
it error out when I try to copy data to new worksheet in a newworkbook.

thanks to all for sharing there expertise!
Once Am done I would publish entire project.

Last edited by MikeJ; 12-19-2006 at 02:12 PM. Reason: Please use [vb] tags
Reply With Quote
  #8  
Old 12-19-2006, 09:29 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Can I assume that you are using 'Option Strict On'?

If that is the case, then I believe that your solution is to use:
Code:
objSheet.Paste(CType(xlsApp.Selection, Excel.Range).COPY())
If you are not using 'Option Strict On', then I don't know what's wrong.

I hope this helps!
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Filter Excel Data from VB.net
Filter Excel Data from VB.net
Filter Excel Data from VB.net Filter Excel Data from VB.net
Filter Excel Data from VB.net
Filter Excel Data from VB.net
Filter Excel Data from VB.net Filter Excel Data from VB.net Filter Excel Data from VB.net Filter Excel Data from VB.net Filter Excel Data from VB.net Filter Excel Data from VB.net Filter Excel Data from VB.net
Filter Excel Data from VB.net
Filter Excel Data from VB.net
 
Filter Excel Data from VB.net
Filter Excel Data from VB.net
 
-->