vb.net get data from Excel Table (not worksheet)

I want to read data from an Excel (v14) spreadsheet that has many Tables. Not sure if I'm using the right terminology - to explain: select data in the spreadsheet, click Insert, Table.
I gave each table a name. Now I want to read each set of table data from a Visual Studio 2010 VB.Net app. I can do it the old fashioned way - open the spreadsheet and loop thru the worksheet cells, or OleDb Select * from Sheet1$, but I think it would be nice to get the tables directly instead.

Is this possible? I've poked around a bit and haven't been able to find anything on working with Excel Tables from vb.net.
bkienzleAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

Tables are like named Ranges since XL2007

pls refer to mhtml:http://officeimg.vo.msecnd.net/en-us/files/165/865/AF010288256.mht

Selecting parts of tables
You might need to work with specific parts of a table. Here is a couple of examples on how to achieve that. The code comments show you where Excel 2003 differs from 2007.

Sub SelectingPartOfTable()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    '1: with the listobject
    With oSh.ListObjects("Table1")
        MsgBox .Name
        'Select entire table
        .Range.Select
        'Select just the data of the entire table
        .DataBodyRange.Select
        'Select third column
        .ListColumns(3).Range.Select
        'Select only data of first column
        'No go in 2003
        .ListColumns(1).DataBodyRange.Select
        'Select just row 4 (header row doesn't count!)
        .ListRows(4).Range.Select
    End With
    
    'No go in 2003
    '2: with the range object
    'select an entire column (data only)
    oSh.Range("Table1[Column2]").Select
    'select an entire column (data plus header)
    oSh.Range("Table1[[#All],[Column1]]").Select
    'select entire data section of table
    oSh.Range("Table1").Select
    'select entire table
    oSh.Range("Table1[#All]").Select
    'Select one row in table
    oSh.Range("A5:F5").Select
End Sub

Open in new window

Regards
0
 
bkienzleAuthor Commented:
Perfect
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.