[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-01-16
2
Medium Priority
?
1,225 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 54

Accepted Solution

by:
Rgonzo1971 earned 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

834 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