Link to home
Start Free TrialLog in
Avatar of NeoTek
NeoTek

asked on

Excel/VB Fustration

Hi,

This is a continuation from this post:

https://www.experts-exchange.com/questions/21286336/Edit-An-Excel-File.html

Here is my code:

'Creates excel attachment
        oExcel = CreateObject("Excel.Application")
        Dim wbk As Excel.Workbook 'Excel Workbook
        Dim sPath = Server.MapPath("/Timesheets/Template.xls") 'Path to timesheet template
        wbk = oExcel.Workbooks.Open(Filename:=sPath, UpdateLinks:=False, ReadOnly:=False)
        With wbk.ActiveSheet
            .Range("A4").Value += txtEmpName.Text
            .Range("A6").Value += txtDept.Text
            .Range("F4").Value += txtPayPerEnd.Text
            .Range("F6").Value += txtEmpNum.Text

            .Range("B11").Value = txtMonIn.Text
            .Range("C11").Value = txtMonOut.Text
            .Range("D11").Value = txtMonLun.Text
            .Range("E11").Value = lblMonTot.Text
            .Range("F11").Value = txtMonRea.Text

            .Range("B12").Value = txtTueIn.Text
            .Range("C12").Value = txtTueOut.Text
            .Range("D12").Value = txtTueLun.Text
            .Range("E12").Value = lblTueTot.Text
            .Range("F12").Value = txtTueRea.Text

            .Range("B13").Value = txtWedIn.Text
            .Range("C13").Value = txtWedOut.Text
            .Range("D13").Value = txtWedLun.Text
            .Range("E13").Value = lblWedTot.Text
            .Range("F13").Value = txtWedRea.Text

            .Range("B14").Value = txtThuIn.Text
            .Range("C14").Value = txtThuOut.Text
            .Range("D14").Value = txtThuLun.Text
            .Range("E14").Value = lblThuTot.Text
            .Range("F14").Value = txtThuRea.Text

            .Range("B15").Value = txtFriIn.Text
            .Range("C15").Value = txtFriOut.Text
            .Range("D15").Value = txtFriLun.Text
            .Range("E15").Value = lblFriTot.Text
            .Range("F15").Value = txtFriRea.Text

            .Range("B16").Value = txtSatIn.Text
            .Range("C16").Value = txtSatOut.Text
            .Range("D16").Value = txtSatLun.Text
            .Range("E16").Value = lblSatTot.Text
            .Range("F16").Value = txtSatRea.Text

            .Range("B17").Value = txtSunIn.Text
            .Range("C17").Value = txtSunOut.Text
            .Range("D17").Value = txtSunLun.Text
            .Range("E17").Value = lblSunTot.Text
            .Range("F17").Value = txtSunRea.Text

            .Range("E18").Value = lblTotTot.Text

        End With
        oExcel.DisplayAlerts = False
        wbk.SaveCopyAs(Server.MapPath("/Timesheets/Timesheet.xls"))
        oExcel.Quit()
        wbk = Nothing
        oExcel = Nothing
        GC.Collect()
        GC.WaitForPendingFinalizers()

It works perfectly fine, but the EXCEL.EXE process doesn't quit.  I have to manually go into the Task Manager and kill it. Any ideas?

NeoTek
Avatar of iboutchkine
iboutchkine
try to add this import
Imports System.Runtime.InteropServices

and after oExcel=Nothing
 add this

            Marshal.ReleaseComObject(wbk)
            Marshal.ReleaseComObject(oExcel)

            GC.Collect()
            GC.WaitForPendingFinalizers()
Avatar of iboutchkine
iboutchkine
try it also before oExcel=Nothing
Avatar of NeoTek
NeoTek

ASKER

When you do it after, you get the error:

Object reference not set to an instance of an object.

When you add it before, you don't receive an error, but the process stays.
Avatar of iboutchkine
iboutchkine
try to do it in a loop

While (Marshal.ReleaseComObject(oExcel) <> 0) ' Repeat until no more COM references
End While


oExcel=Nothing
GC.Collect()
GC.WaitForPendingFinalizers()
ASKER CERTIFIED SOLUTION
Avatar of Donnie4572
Donnie4572
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SoftEng007
SoftEng007
you are releasing the excel object before release the workbook object.

try this:


 oExcel.DisplayAlerts = False
        wbk.SaveCopyAs(Server.MapPath("/Timesheets/Timesheet.xls"))
        wbk.Close()
        wbk = Nothing
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()
        GC.WaitForPendingFinalizers()


Avatar of NeoTek
NeoTek

ASKER

When I don't use the

With wbk.ActiveSheet
     ...
End With

The EXCEL.EXE process gets killed. Thanks.

NeoTek
Avatar of NeoTek
NeoTek

ASKER

Actually that wasn't it.  When I edit more than 4 cells the process stays.  Anything below, it goes away.  Any ideas on why this is happening?

NeoTek