Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1670
  • Last Modified:

Read Data from Excel using OLEDB in Visual Basic 2010

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
motokent
Asked:
motokent
  • 3
  • 3
  • 2
2 Solutions
 
carsRSTCommented:
You might try this...

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

Use single quotes.
0
 
motokentAuthor Commented:
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
 
13598Commented:
Could you just make a copy of it and rename it without the space, do all your processing and delete copy?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
carsRSTCommented:
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
 
motokentAuthor Commented:
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
 
13598Commented:
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
 
motokentAuthor Commented:
That worked when the quotes included the $.  Thanks!
0
 
13598Commented:
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now