Retrieve sheets in workbook (Excel & SQL)

[witzulu]

Hi

I am currently using the following Query to retrieve a excel sheet
[SIZE=-1]

[/B][/SIZE][SIZE=-1][B]select * from [sheet1$][/B][/SIZE][SIZE=-1][B]

[/SIZE]It Works the only problem is the datasheet will not always be named sheet1 thus I need a sql Query to retrieve a list of all the datasheets available in a workbook. The problem with this is I have not been able to find a way to do this. Can anyone help me with this please?

 

[BadgerByte]

Hey,

This is from an answer I posted on VBcity.

The worksheets are registered as tables when you're using OleDB. I've copied this straight out of some working code so ignore the listbox etc.. but this should give you the basic idea...

schemaTable = myconnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _ 
                                                                           New Object() {Nothing, Nothing, Nothing, "TABLE"}) 
            myconnection.Close() 


            ListBox1.DisplayMember = "TABLE_NAME" 
            ListBox1.ValueMember = "TABLE_NAME" 
            ListBox1.DataSource = schemaTable 

            'ListBox1.Refresh() 
            'ListBox1.Update()

The Column TABLE_NAME in the resulting schemaTable will contain all the worksheet names for your excel file.

Hope this helps.

 

[klong]

Hello,

Did you get this to work? I'm having a similar problem. I loaded the names of the excel worksheets in a listbox. The trouble I have is when I try to query the the worksheet. The Microsoft Jet Engine can't seem to find the table object. If I explicitly type in the table name([Segment1$]) the query works but when I try to pass in the table name(worksheet) as the SelectedValue from the ListBox, I have problems.

Dim xlConn As New OleDbConnection(conStr)
Dim dt As System.Data.DataTable

xlConn.Open()
dt = xlConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
ListBox1.DisplayMember = "TABLE_NAME"
ListBox1.ValueMember = "TABLE_NAME"
ListBox1.DataSource = dt
ListBox1.Refresh()
ListBox1.Update()

Dim qStr As String
qStr = CStr(ListBox1.SelectedValue)

Dim xlCmd As New OleDbCommand
Dim xlDa As New OleDbDataAdapter
Dim xlDs As New DataSet

Try

xlCmd = xlConn.CreateCommand
xlCmd.CommandText = "SELECT * FROM qStr"
xlDa.SelectCommand = xlCmd
xlDa.Fill(xlDs, "PlayList")
DataGrid1.DataSource = xlDs
DataGrid1.DataMember = "PlayList"
Catch ex As Exception
MsgBox("Error: " & ex.Source & ": " & ex.Message, _
MsgBoxStyle.OKOnly, "Load Data")
End Try


Any help you can provide would be greatly appreciated. Thanks.


Kerry

 

[jmcilhinney]

That's because you are NOT passing in the SelectedValue from the ListBox. This line:

[SIZE=2][SIZE=2][SIZE=2]xlCmd.CommandText = "SELECT * FROM qStr"[/SIZE][/SIZE][/SIZE]

is literally searching for a table name "qStr". If want to use the qStr variable then you would need to do this:

[SIZE=2][SIZE=2][SIZE=2]xlCmd.CommandText = "SELECT * FROM " & qStr[/SIZE][/SIZE][/SIZE]

so it use the value of the qStr variable. Further, I'm guessing that the names you get do not include the brackets or the dollar. If so you would have to add those yourself:

[SIZE=2][SIZE=2][SIZE=2]xlCmd.CommandText = "SELECT * FROM [" & qStr & "$]"[/SIZE][/SIZE][/SIZE]

 

[DavidT_macktool]

Asked and answered here...

http://www.vbdotnetforums.com/showthread.php?t=7515&highlight=excel+worksheet