Solved

Reading sheetnames from excel and insert it into a checkboxlist

Posted on 2008-06-10
9
511 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
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 create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

758 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

22 Experts available now in Live!

Get 1:1 Help Now