-
March 20th, 2009, 05:57 AM
#1
[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....
Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
WPF Articles : 3D Animation 1 , 2 , 3
Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.
-
March 20th, 2009, 07:10 AM
#2
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
-
March 23rd, 2009, 01:47 AM
#3
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...
Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
WPF Articles : 3D Animation 1 , 2 , 3
Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.
-
March 23rd, 2009, 06:14 AM
#4
Re: get Excel Worksheet names with SQL Query ??
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=
-
March 24th, 2009, 09:42 PM
#5
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 %
Last edited by dglienna; March 24th, 2009 at 09:46 PM.
-
March 25th, 2009, 01:12 PM
#6
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
Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
WPF Articles : 3D Animation 1 , 2 , 3
Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|