Filtering Data from Excel Table and Show It in VB

[nairda]

Hi there,
I need help processing data from Excel in VB. I have a MSHFlexgrid to show data extracted from Excel. I'm able to show a range of cells from Excel table to my Flexgrid, but that's it. The problem is, I can't filter the data I wanted to show from Excel table. Is it possible to filter Excel data using SQL command? I've tried several ways including SQL command like "select Name from tbcustomers where Name....."
Please help me, anyone.

This is the code i've made so far:

Private Sub CmdShow_Click()
    Dim xlapp As Excel.Application
    Dim xlbook As Excel.Workbook
    Set xlapp = New Excel.Application
    Set xlbook = xlapp.Workbooks.Add
    
    Clipboard.Clear
    With MSHFlexGrid1
        .Col = 0
        .Row = 0
        .ColSel = .Cols - 1
        .RowSel = .Rows - 1
        Clipboard.SetText .Clip
    End With
    
    With xlapp.ActiveWorkbook.ActiveSheet
        .Range("A1:R8000").Select
        .Paste
    End With
    xlapp.Visible = True
    End
End Sub

Thank you very much!
Nairda

[Killer42]

If you have a look in the index of articles etc. at the top of the forum, there's an entry there on Excel which I think covers this pretty well.

[nairda]

If you have a look in the index of articles etc. at the top of the forum, there's an entry there on Excel which I think covers this pretty well.

Hi, thank you Killer42,
But I'm so sorry, I can't find the article you've told me.
I only found how to show only visible cells from filtered Excel table. It's not covering my problem.
My problem is I want VB to filter my Excel data and show it to my MSHFlexgrid.
I have an Excel table (tbstudents) contains a view columns (StudentName, Address, Grade, etc). I want to show all data of a student named "Charlie".
So, how to make VB search and show Charlie's data?

In Access database, I use this code:

"Select * from tbstudents where StudentName  = '" & Text1.Text & "'", con, adOpenKeyset, adLockOptimistic"

But I can't used it on Excel.table
Please help.

Nairda

[Killer42]

Oh, so you want to take the data from the FlexGrid, filter it, and place the result in Excel - is that right? Now sure how you'd go about that directly. The brief article I pointed out shows how to select only the visible cells after filtering in Excel.

Perhaps you could do a three-stage process? That is, paste to Excel, then apply the filter, then use the "select visible cells" from the sample and copy it somewhere else. Obviously this is not the most elegant way to go about it, but it may work until you can find something better.

Or, can you do the filtering before you take the data from Excel to place it in the FlexGrid? If so, then that sample will almost certainly be helpful.

[nairda]

Oh, so you want to take the data from the FlexGrid, filter it, and place the result in Excel - is that right? Now sure how you'd go about that directly. The brief article I pointed out shows how to select only the visible cells after filtering in Excel.

Perhaps you could do a three-stage process? That is, paste to Excel, then apply the filter, then use the "select visible cells" from the sample and copy it somewhere else. Obviously this is not the most elegant way to go about it, but it may work until you can find something better.

Or, can you do the filtering before you take the data from Excel to place it in the FlexGrid? If so, then that sample will almost certainly be helpful.

Thank you very much,
Yes, I want to take data FROM Excel, and then put it on my FlexGrid.
So, I have to filter the data from Excel before place it in my Flexgrid, right?
Is there anyway that VB could automate the Excel filtering so I don't have to filter Excel manually before place the data in my Flexgrid?

Thank you so much again.

Nairda

[Killer42]

So, I have to filter the data from Excel before place it in my Flexgrid, right?

I think that's probably the more efficient way (after all, why move data you don't want?) but certainly not the only way.

Is there anyway that VB could automate the Excel filtering so I don't have to filter Excel manually before place the data in my Flexgrid?

I'm sure there is. I don't recall the syntax, but it's easy to get Excel to generate the code for you. Just start recording a macro in Excel, then set the filter you want, then stop recording. Hopefully (works for most functions, but not all functions) the macro will now contain the code to do what you want. It should then be relatively simple to transfer that to your program.

[nairda]

I think that's probably the more efficient way (after all, why move data you don't want?) but certainly not the only way.

I'm sure there is. I don't recall the syntax, but it's easy to get Excel to generate the code for you. Just start recording a macro in Excel, then set the filter you want, then stop recording. Hopefully (works for most functions, but not all functions) the macro will now contain the code to do what you want. It should then be relatively simple to transfer that to your program.

Ok then,
I'll give it a try.
Thank you very much for your help.

Regards,
nairda