The code does what I want when it first runs, moves data from spreadsheet to excel. However when I attempt to load it again using the fileupload control, I get an error "The process cannot access the file. because it is being used by another process." I have tried different things but it still errors out. Any help would be appreciated. Here is the code:
If fuGroupAwarenessXLS.HasFile Then
Dim strAttachmentPath As String = Application("RootPath") & "/UPLOADED_FILE/GROUP_GROWTH/"
Dim strFileName As String = Path.GetFileName(fuGroupAwarenessXLS.FileName)
strFileName = strFileName.Replace("'", "")
fuGroupAwarenessXLS.SaveAs(strAttachmentPath & "/" & strFileName)
fuGroupAwarenessXLS.Dispose()
Dim gv1 As New GridView
Dim objDa As New OleDbDataAdapter()
objDa.SelectCommand = ExcelConnection()
Dim objDs As New DataSet()
objDa.Fill(objDs)
gv1.DataSource = objDs.Tables(0).DefaultView
gv1.DataBind()
End If
Protected Function ExcelConnection() As OleDbCommand
' Connect to the Excel Spreadsheet
Dim xConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("~/UPLOADED_FILE/GROUP_GROWTH/" & fuGroupAwarenessXLS.PostedFile.FileName) & ";Extended Properties=Excel 12.0;"
' create your excel connection object using the connection string
Dim objXConn As New OleDbConnection(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 OleDbCommand("SELECT * FROM [Sheet1$]", objXConn)
Return objCommand
End Function
If fuGroupAwarenessXLS.HasFile Then
Dim strAttachmentPath As String = Application("RootPath") & "/UPLOADED_FILE/GROUP_GROWTH/"
Dim strFileName As String = Path.GetFileName(fuGroupAwarenessXLS.FileName)
strFileName = strFileName.Replace("'", "")
fuGroupAwarenessXLS.SaveAs(strAttachmentPath & "/" & strFileName)
fuGroupAwarenessXLS.Dispose()
Dim gv1 As New GridView
Dim objDa As New OleDbDataAdapter()
objDa.SelectCommand = ExcelConnection()
Dim objDs As New DataSet()
objDa.Fill(objDs)
gv1.DataSource = objDs.Tables(0).DefaultView
gv1.DataBind()
End If
Protected Function ExcelConnection() As OleDbCommand
' Connect to the Excel Spreadsheet
Dim xConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("~/UPLOADED_FILE/GROUP_GROWTH/" & fuGroupAwarenessXLS.PostedFile.FileName) & ";Extended Properties=Excel 12.0;"
' create your excel connection object using the connection string
Dim objXConn As New OleDbConnection(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 OleDbCommand("SELECT * FROM [Sheet1$]", objXConn)
Return objCommand
End Function