«

this code opens the workbook.. either protects or unprotects a sheet..

then saves.. so can write to it..

it unprotects then saves fine.. then writes to it no prob.. it resets
the password again no prob.. but on close of the workbook object.. it
goes into the workbook_beforeclose event within the vba of the file!!
at this point it fails for no apparent reason as this event works
perfectly when closing through excel !!

=============================================

Sub CreateTemp()

Dim str As String = Server.MapPath("Downloads\" &
(Session("mCallCentreID") & "_download"))
Dim DirNew = New DirectoryInfo(str)

Dim MasterFile As String = Server.MapPath("Downloads\Return.xls")
Dim FileNew As New FileInfo(MasterFile)

Try
If Not DirNew.Exists Then
DirNew.Create()
str = str & "\Return.xls"
FileNew.CopyTo(str, True)
End If

UnlockXLSheet(True, str)
WriteIDtoXL(str)
UnlockXLSheet(False, str)

Catch ex As Exception
Throw New Exception("Error : " + ex.ToString())
End Try

End Sub

Private Function xlConn(ByVal FilePath As String) As String

xlConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
FilePath & ";Extended Properties=""Excel 8.0;HDR=YES;MAXSCANROWS=4"""

End Function

Private Sub WriteIDtoXL(ByVal str)
Dim conn1 As New System.Data.OleDb.OleDbConnection(xlConn(str))
conn1.Open()

Dim cmd As New System.Data.OleDb.OleDbCommand
cmd.Connection = conn1
cmd.CommandText = "INSERT INTO [CCID] (ID) values (" &
Session("mCallCentreID") & ")"
cmd.ExecuteNonQuery()
conn1.Close()

End Sub

Private Sub UnlockXLSheet(ByVal UnlockSheet As Boolean, ByVal
strPath As String)

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'Start a new workbook in Excel.
oExcel = CreateObject("Excel.Application")
oBook = oExcel.workbooks.open(strPath)

'Add headers to the worksheet on row 1.
oSheet = oBook.sheets("Instructions")
If UnlockSheet Then
oSheet.unprotect(Password:="pw")
Else
oSheet.protect(Password:="pw", DrawingObjects:=True,
Contents:=True, Scenarios:=True)
End If


oBook.Save()

NAR(oSheet)
oBook.close(True)
NAR(oBook)
oExcel.Quit()

NAR(oExcel)

GC.Collect()
GC.WaitForPendingFinalizers()

End Sub