Solved

Reading sheetnames from excel and insert it into a checkboxlist

Posted on 2008-06-10
9
514 Views
Last Modified: 2011-09-20
Hello!

Im a bit confussed and ive got a question regarding importing sheetnames from a excel.

Im about to read from an excel document read it sheets and insert it into a checkboxlist in a asp.net page.
IM using oledbb4.0 with extention 8.0 (old excel)

Any idea how to accomplish this?
The actual part im stuck at is how to read the sheetnames after that i will insert the sheetnames ive got into a dataset and by that way set he checkboxlist to read from the dataset.

But back to my problem Any idea how to read the sheetnames? It canbe more than once just now one:) So i gotta have a loop of some kind.

Any idea ? I would be thnakful
0
Comment
Question by:clooak
  • 4
  • 2
9 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 21750236
Something like this should get you started:

Function ListSheetsInFile(ByVal strFile As String) As String()
    Dim xlConn As Object 'ADODB.Connection
    Dim xlSheets As Object 'ADODB.Recordset
    Dim astrSheets() As String, strSheet As String
    Dim lngSheetCounter As Long
    On Error GoTo err_handler
    'connect to the file
    Set xlConn = CreateObject("ADODB.Connection")
    With xlConn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Extended Properties") = "Excel 8.0;IMEX=1"
        .Open strFile
    End With
    
    'see sheet names in the immediate window
    Set xlSheets = xlConn.OpenSchema(20) '20=adSchemaTables
    With xlSheets
        Do While Not .EOF
            ReDim Preserve astrSheets(lngSheetCounter)
            Debug.Print .Fields("TABLE_NAME").Value & vbTab & .Fields("CARDINALITY").Value
'            astrSheets(lngSheetCounter) = Mid$(strSheet, 2, InStr(1, strSheet, "$") - 2)
'            lngSheetCounter = lngSheetCounter + 1
            .MoveNext
        Loop
    End With
clean_up:
    On Error Resume Next
    ListSheetsInFile = astrSheets()
    xlSheets.Close
    Set xlSheets = Nothing
    xlConn.Close
    Set xlConn = Nothing
    Exit Function
    
err_handler:
    MsgBox Err.Number & ": " & Err.Description
    Resume clean_up
End Function

Open in new window

0
 
LVL 7

Expert Comment

by:skiltz
ID: 21750528

    Public Function getDatabaseTables() As DataTable
 
        If cn.State = ConnectionState.Closed Then
            Try
                cn.Open()
            Catch ex As Exception
  
                Exit Function
 
            End Try
        End If
 
 
        Dim dt As New DataTable
 
        Try
            dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
        Catch ex As Exception
 
            dt.Dispose()
            Throw New Exception("Getting tables from database failed, check connection string")
        Finally
            cn.Close()
        End Try
 
        Return dt
 
    End Function

Open in new window

0
 

Author Comment

by:clooak
ID: 21751001
I didnt really get ur solutions But ive tried another way now by adding directly onto the  checkboxlist but iive got a problem in the loop. Specified detail about the error look a few rows down
Error fails at where i have the "For each ws in Worksheets i will fail by errors (Worksheets is type and cannot be used as a expression"

Any idea on how to solve this I need obviosly somekind of directaccess by command to the workbook or something.

<code>
 'Processing xls data
        Dim xlApp As Excel.Application
        Dim xlWB As Excel.Workbook
        xlWB = xlApp.Workbooks.Open(filenamepath)
        Dim xlsheet As Excel.Worksheet
        Dim ws As Worksheet
        For Each ws In Worksheets
            Me.tablelist.AddItem(ws.Name)
        Next ws
</code>
Any idea? I would appreciate it
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 21751043
you need:
For Each ws In xlWB.Worksheets

Regards,
Rory
0
 

Author Comment

by:clooak
ID: 21751305
Ive just got one more question for some reason now i realized that im not able to use addItem on a checkboxlist. I was thinking about filling a dataset instead. Would it be good or do you perfer doing it another way?

Im glad for any help and snippet! I was thinking about just doing a fill and replace the loop
0
 

Accepted Solution

by:
clooak earned 0 total points
ID: 21751630
If anyone feel like helping me fast Ii will add another 100 points .:)

Ive just got one more question for some reason now i realized that im not able to use addItem on a checkboxlist. I was thinking about filling a dataset instead. Would it be good or do you perfer doing it another way?

this is how my code loooks now
    Dim xlApp As Excel.Application
        Dim xlWB As Excel.Workbook
        xlWB = xlApp.Workbooks.Open(filenamepath)
        Dim xlsheet As Excel.Worksheet
        Dim ws As Worksheet
        Dim ds As New DataSet("tempset")

        For Each ws In xlWB.Worksheets
            Me.tablelist.AddItem(ws.Name)
        Next ws
end code

I want to add the data to the dataset called ds instead, Would it just to be fill the table any other way have to take?
0
 

Author Comment

by:clooak
ID: 21789728
I found how to solve it myself and theferoe I will not give any points to anyone else but rorya who gave me a just a direction that helped me alot

Any other guys thanks for helping and im sorry!

Regards
Clooak
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question