Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 522
  • Last Modified:

Reading sheetnames from excel and insert it into a checkboxlist

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
clooak
Asked:
clooak
  • 4
  • 2
1 Solution
 
Rory ArchibaldCommented:
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
 
skiltzCommented:

    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
 
clooakAuthor Commented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Rory ArchibaldCommented:
you need:
For Each ws In xlWB.Worksheets

Regards,
Rory
0
 
clooakAuthor Commented:
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
 
clooakAuthor Commented:
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
 
clooakAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now