Solved

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

Posted on 2014-01-16
2
1,121 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 50

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Run Program using VBScript 3 72
Enable Clear Text in Win 8.1 7 45
How Does Quick Books store date / time? 3 104
What the difference between blend and Visual Studio 3 166
This article shows how to make a Windows 7 gadget that accepts files dropped from the Windows Explorer.  It also illustrates how to give your gadget a non-rectangular shape and how to add some nifty visual effects to text displayed in a your gadget.…
This article surveys and compares options for encoding and decoding base64 data.  It includes source code in C++ as well as examples of how to use standard Windows API functions for these tasks. We'll look at the algorithms — how encoding and decodi…
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

792 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