Closing an excel workbook fires Workbook_BeforeClose

Discussion in 'ASP .Net' started by Winshent, Nov 3, 2004.

  1. Winshent

    Winshent Guest

    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
     
    Winshent, Nov 3, 2004
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. philip herman

    'Document not saved' error - excel workbook

    philip herman, Jul 11, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    5,450
    philip herman
    Jul 11, 2003
  2. =?Utf-8?B?SmltIEhlYXZleQ==?=

    Excel _WorkBook vs. Excel.Workbook

    =?Utf-8?B?SmltIEhlYXZleQ==?=, Sep 29, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    3,649
    Steve C. Orr [MVP, MCSD]
    Sep 29, 2004
  3. Yelena Kaplun
    Replies:
    1
    Views:
    5,176
    Yelena Kaplun
    Jan 19, 2005
  4. Replies:
    0
    Views:
    993
  5. Replies:
    3
    Views:
    999
Loading...

Share This Page