We help IT Professionals succeed at work.

Error message

EYoung
EYoung asked
on
(VB6 sp5 on NT 4.0)

I have a VB app that writes out records to an Excel spreadsheet.  Once the spreadsheet is filled in, it is automatically saved.  All of this works (for the most part) correctly.

However, at the end of the day when I am ready to go home, the following error message appears when I attempt to log off my workstation:


Microsoft Excel
   Do you want to save the changes you made to 'Book1'?
   Yes   No   Cancel


(The above message appears for each spreadsheet I have created during the day.  For example, if I run my app 7 times, I get 7 'Book1' error messages even though I have saved the spreadsheet under another name.  If I say "No", the following message appears:)

Microsoft Excel - Book1: Excel.exe - Application Error
   The instruction at "0x3013cda4" referred to memory at "0x005b000d".  The name could not be "read".
   Click on OK to Terminate the application.
   OK


Here are pieces of the code:

    'Create Excel objects
    Set E = CreateObject("Excel.Application")
    If Err <> 0 Then
        Set E = Nothing
        MsgBox "Excel could not be started on this computer"
        Exit Sub
    End If
    E.Workbooks.Add
    Set W = E.Workbooks(1).Worksheets(1)

        'Fill in some of the cells
        mRow = mRow + 1
        W.Cells(mRow, 1).Value = mCompany                   'CO#
        If mCompany = "1" Then
            W.Cells(mRow, 2).Value = mRecordID              'ID#
            W.Cells(mRow, 3).Value = PART_Rec.Extract(3)    'Desc
            W.Cells(mRow, 4).Value = PART_Rec.Extract(1)    'Part#
        End If
        W.Cells(mRow, 5).Value = mOn_Hand                   'On Hand
        W.Cells(mRow, 6).Value = ""                         'QTY RTN
        W.Cells(mRow, 7).Value = mOld_Min                   'OLD MIN
        W.Cells(mRow, 8).Value = ""                         'NEW MIN



    'Save the spreadsheet and close all connections
    Set W = Nothing
    FName = "C:\Downloads\PART_CO_" & mLine
    FName = FName & ".xls"
    E.Workbooks(1).SaveAs FName
    E.Workbooks.Close
    Set E.Workbooks = Nothing
    E.Quit
    Set E = Nothing
    W.Close
    Set W = Nothing


When I run the app, I do not have the Excel spreadsheet open.  It appears that I am either not saving or not closing Excel properly.

Thank you for any help,
EYoung
Comment
Watch Question

Commented:
Looks like your Excel.Application (E) is running.
Move your code around

W.Close
Set W = Nothing

Set E.Workbooks = Nothing
   E.Quit
   Set E = Nothing

Make sure that in Task Manager Excel.exe is not running
   

Commented:
It's not easy to troubleshoot this kind of memory leak.
Some suggestions here:
1. use getobject instead of createobject, this will at least reduce the error message from 7 book1 to 1 book1.
2.In your code,  set E=nothing should be the last clean up of all the excel related statement. clear worksheet first, then workbook, then excel.
3. Always use fully qualified reference when call any method of property. eg. use xlSheet.Range(xlSheet.cell(1,2)) instead of xlSheet.Range(Cell(1,2))

That message pops up unless you specifically tell it not to.

Use this code to stop it from happening:

E.Workbooks(1).Close False

That tells it to close the workbook and not bring up that message when it's closing.

Hope that helps.

Author

Commented:
Yes, moving around the closes seems to have eliminated the errors.  Thanks.

My thanks to the other experts for their helpful comments.

Regards,
EYoung

Author

Commented:
I hope Experts-Exchange sticks around.  I would gladly pay a yearly fee to keep EE alive.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.