asked on

Error message

(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

ASKER CERTIFIED SOLUTION

Slava_K

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GoodJun

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))

peteyhiggins

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.

EYoung

ASKER

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

My thanks to the other experts for their helpful comments.

Regards,
EYoung

EYoung

ASKER

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