Solved

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

Posted on 2014-01-16
2
1,099 Views
Last Modified: 2014-01-17
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.
0
Comment
Question by:bkienzle
2 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39787731
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
 

Author Closing Comment

by:bkienzle
ID: 39788404
Perfect
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA Import all database objects 12 45
VBA Error Since Excel 2016 x64 5 93
Changing the installation path of this MSI 5 94
Recommendation vb6 to vb.net or others 14 112
This article shows how to make a Windows 7 gadget that extends its U/I with a flyout panel -- a window that pops out next to the gadget.  The example gadget shows several additional techniques:  How to automatically resize a gadget or flyout panel t…
This article describes a technique for converting RTF (Rich Text Format) data to HTML and provides C++ source that does it all in just a few lines of code. Although RTF is coming to be considered a "legacy" format, it is still in common use... po…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now