[RESOLVED] get Excel Worksheet names with SQL Query ??
I'm using the following code to retrieve data from uploaded Excel Spreadsheets, however the specs we got says "Worksheet named 'Sales' or something simular" .. The sample files we got have 3 names for the Same sheet, however they each start with 'Sale' .. Sales , Sale Totals, Sales Total ....
Code:
Dim oledbconn As New OleDbConnection
Dim oledbcmd As New OleDbCommand
Dim strConn As String
Try
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties=""Excel 8.0;IMEX=1;HDR=YES;"""
oledbconn.ConnectionString = strConn
oledbconn.Open()
oledbcmd.CommandType = CommandType.Text
oledbcmd.CommandText = "SELECT * FROM [Sales$]"
Now how can i query the Excel Speadsheet and get a list of Worksheet names, where i can the check and pass the correct name to the above code..
BTW. I need to do the same for another worksheet in the same file....
Re: get Excel Worksheet names with SQL Query ??
With VBA you can do the following
Code:
Sub wsNames()
Dim wS As Worksheet
For Each wS In Worksheets
MsgBox wS.Name
Next wS
End Sub
Re: get Excel Worksheet names with SQL Query ??
Thanks George..
Its a handy tip..
However that does not help.. I think i didn't give enough info originally..
Clients will be uploading Excel Files on to the Web Server, and a ASP application will be checking the contents of the file (Using a SQL Server Database to store the results) and either accept or regect files as they are uploaded.. The server does not have Excel installed.....
Thanks
Gremmy...
Re: get Excel Worksheet names with SQL Query ??
Quote:
Clients will be uploading Excel Files on to the Web Server, and a ASP application will be checking the contents of the file (Using a SQL Server Database to store the results) and either accept or regect files as they are uploaded.. The server does not have Excel installed.....
Presumably you will store the uploaded file in some directory which can then be accessed at some stage through its name or an index
(Not sure why you need the worksheet names to do that ?)
However, there must be something else you haven't also said, which is why you must have the excel worksheet names without the use of Excel
So, do you want to be able to read the details in excel files without the help of Excel on the Machine ?
What you now need to know is about "BIFF" Format, which is the binary format of an excel file
Good luck with that - there are a few links on the web - I have a sample excel binary format creator if you cant find one (works with VB6)
http://www.google.com.au/search?hl=e...e+Search&meta=
Re: get Excel Worksheet names with SQL Query ??
Use the LIKE keyword. Not sure of the wildcard. Probably %
BOL should help...
EDIT: They did. It was %
Re: get Excel Worksheet names with SQL Query ??
I was given this code to return the Excel Worksheet names from an excel file using SQL's TableSchema..
Code:
Dim oledbconn As New OleDbConnection
Dim strConn As String
Dim ExcelTables As DataTable
Try
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties=""Excel 8.0;IMEX=1;HDR=YES;"""
oledbconn.ConnectionString = strConn
Server.ScriptTimeout = 60 * 60
oledbconn.Open()
ExcelTables = oledbconn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})
Dim dr As DataRow
Dim i As Integer = 0
For Each dr In ExcelTables.Rows
If dr.Item(3) = "TABLE" Then
If InStr(dr.Item(2), "$") > 0 Then '//work book tab
With ddlNamedRanges
.Items.Insert(i, dr.Item(2))
.Items(0).Value = i.ToString
End With
i += 1
End If
End If
Next
With ddlNamedRanges
.DataBind()
End With
Catch ex As Exception
Throw
Finally
oledbconn.Close()
I've not had a chance to test it yet, however i figured i'd share it here, and resolve this thread...
Gremmy