Solved

Read Data from Excel using OLEDB in Visual Basic 2010

Posted on 2010-08-27
8
1,616 Views
Last Modified: 2013-11-27
I need to read contents of an Excel file into my VB application's datagrid control.  I'm using a solution from a previous question:
=================
= (Dhaest):Here is another example, surely written in vb.net (the version of .net has no impact)
= Read Data from Excel using OLEDB
= http://vb.net-informations.com/excel-2007/vb.net_excel_oledb.htm
=================
It works great, except for one thing.  I don't have control over the actual Excel file and it happens to have a space at the beginning of the sheet name (example:  " Vlan - 32" vs "Vlan -32").  The program chokes because of the space.  Here's the error message:
System.Data.OleDb.OleDbException (0x80040E14): Invalid bracketing of name 'Vlan - 32$'.

Any ideas?
Imports System.Data

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles Button1.Click

        Try

            Dim MyConnection As System.Data.OleDb.OleDbConnection

            Dim DtSet As System.Data.DataSet

            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

            MyConnection = New System.Data.OleDb.OleDbConnection _

            ("provider=Microsoft.Jet.OLEDB.4.0;" & _

            " Data Source='c:\testfile.xls'; " & _

             "Extended Properties=Excel 8.0;")

            MyCommand = New System.Data.OleDb.OleDbDataAdapter _

                ("select * from [ Vlan - 32$]", MyConnection)

            MyCommand.TableMappings.Add("Table", "TestTable")

            DtSet = New System.Data.DataSet

            MyCommand.Fill(DtSet)

            DataGridView1.DataSource = DtSet.Tables(0)

            MyConnection.Close()

        Catch ex As Exception

            MsgBox(ex.ToString)

        End Try

    End Sub

End Class

Open in new window

0
Comment
Question by:motokent
  • 3
  • 3
  • 2
8 Comments
 
LVL 16

Expert Comment

by:carsRST
ID: 33545265
You might try this...

("select * from [' Vlan - 32'$]", MyConnection)

Use single quotes.
0
 

Author Comment

by:motokent
ID: 33545445
Tried it. No luck. The only thing that works for me so far is to remove the space, but that's not an option for the final product.
0
 
LVL 16

Expert Comment

by:13598
ID: 33545627
Could you just make a copy of it and rename it without the space, do all your processing and delete copy?
0
 
LVL 16

Accepted Solution

by:
carsRST earned 400 total points
ID: 33545727
If you tried exactly what i put in, I noticed I didn't put the single quote right (you might have caught).
Should be:
' Vlan - 32$'


0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:motokent
ID: 33545747
I'm open to anything, but if I copy the file and rename the sheet then I'm back to my very original problem.... How do you open and work with an Excel file in Visual Basic 2010.

This was my original posting from earlier today:  "How do I open and read an Excel file in Visual Basic 2010?"
0
 
LVL 16

Assisted Solution

by:13598
13598 earned 100 total points
ID: 33545775
Well I just tried @carsRST and it works after his correction. That should do it. Enclose the sheet name including the $ within single quotes.
0
 

Author Comment

by:motokent
ID: 33545781
That worked when the quotes included the $.  Thanks!
0
 
LVL 16

Expert Comment

by:13598
ID: 33546358
I don't know exactly what you are trying to accomplish but since you are open to anything you could just add a spreadsheet control and access the sheet directly without a datagrid. It would simplify things. You may want to try that in the future:
http://support.microsoft.com/kb/235883
 
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

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

18 Experts available now in Live!

Get 1:1 Help Now