Solved

Reading sheetnames from excel and insert it into a checkboxlist

Posted on 2008-06-10
9
513 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now