Solved

Read Data from Excel using OLEDB in Visual Basic 2010

Posted on 2010-08-27
8
1,636 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
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…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

615 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