Murray Brown
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.OL EDB.4.0;" & _
"Data Source=" & Server.MapPath("~/Temp/Exc elImport" & 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
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
Dim xConnStr As String = "Provider=Microsoft.Jet.OL
"Data Source=" & Server.MapPath("~/Temp/Exc
"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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER