Link to home
Start Free TrialLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

VB.net read current spreadsheet

Hi

How would I use code similar to that below in my Excel Add in project to read
the current Excel spreadsheet

    Protected Function ExcelConnection() As SqlCommand
        Try

            Dim sMinAndSecond As String = Me.Second_Minute_of_Upload.Text 'workaround to stop file locking on the same name

            Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & Server.MapPath("~/Temp/ExcelImport" & sMinAndSecond & ".xls") & ";" & _
            "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""

            ' create your excel connection object using the connection string
            Dim objXConn As New SqlConnection(xConnStr)
            objXConn.Open()

            ' use a SQL Select command to retrieve the data from the Excel Spreadsheet
            ' the "table name" is the name of the worksheet within the spreadsheet
            ' in this case, the worksheet name is "Members" and is expressed as: [Members$]
            Dim objCommand As New SqlCommand("SELECT * FROM [Sheet1$]", objXConn)
            Return objCommand
        Catch ex As Exception
            Response.Write("There was an error connecting to the Excel spreadsheet! " & Err.Description)
        End Try

    End Function
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Murray Brown

ASKER

Thanks