CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    [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.

  2. #2
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,900

    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

  3. #3
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    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.

  4. #4
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,900

    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=

  5. #5
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    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.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  6. #6
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    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
  •  





Click Here to Expand Forum to Full Width

Featured