Solved

Read Data from Excel using OLEDB in Visual Basic 2010

Posted on 2010-08-27
8
1,602 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 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

10 Experts available now in Live!

Get 1:1 Help Now