|
|
Hello,
I am facing problem in writing to an Excel file from ASP.NET. I want to change the value of a cell (B7) on the button click from ASP.NET web enabled browser. Following is the code of my ASP.NET web application:
<%@ Page Language="VB" Debug="true" %>
<script runat="server">
' write_to_plc.aspx
'
Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES"""
Sub Button1_Click(sender As Object, e As EventArgs)
Update_Individual_Cells()
End Sub
Public Sub Update_Individual_Cells()
'==========================================================================
' Update individual cells on the PlcData worksheet;
' specifically, cell B7 is modified.
'==========================================================================
' NOTE: The connection string indicates that the table does *NOT*
' have a header row.
Dim conn As New System.Data.OleDb.OleDbConnection(m_sConn1.Replace("HDR=YES", "HDR=NO"))
conn.Open()
Dim cmd As New System.Data.OleDb.OleDbCommand()
cmd.Connection = conn
'Updates a cell B7 of the spreadsheet, PlcData to "1".
cmd.CommandText = "UPDATE [PlcData$B7:B7] SET F1 = '1'"
cmd.ExecuteNonQuery()
conn.Close()
End Sub
</script>
<html>
<head>
</head>
<body>
<form runat="server">
Click this button to start an experiment:<button id="Button1" style="WIDTH: 100px; HEIGHT: 25px; BACKGROUND-COLOR: #e6e6fa" type="button" runat="server" onserverclick="Button1_Click">Click</button>
</form>
</body>
</html>
I want to get an Excel macro executed, when a cell value is changed. So, I wrote the following macro in “ThisWorkbook” of test.xls file:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Msgbox “hello”
End Sub
So, whenever the cell value is changed from 0 to 1, this macro should get executed.
The problem is that the ASP.NET web application updates the value from 0 to 1, but the macro doesn’t get executed until I open the file manually.
Can someone please fix this?
I tried another thing … I kept the excel file open prior to executing ASP.NET code. It gives me error message: The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.
I do not know about how to fix this either.
It will be great, if someone could help me with this. I appreciate your help.
Thanks,
Aparna
|
|
|
|
|
|
|
|
aparna