Aughhh! Opening then Closing Excel in App - Excel remains in memory 7

Nicholas · Feb 11, 2000

On my form I have a routine Private Sub SendToExcel() in which I have this code: 
 
Dim objExcel as Excel.Application 
Dim objWorkbook As Excel.Workbook 
Dim objWorkSheet As Excel.Worksheet 
Dim objRange As Excel.Range 
 
I then open Excel as: 
 
Set objExcel = GetObject("", "Excel.Application&quot

objExcel.Visible = False 
Set objWorkbook = objExcel.Workbooks.Add 
objExcel.DisplayAlerts = False 
 
The recorset is sent to Excel and formatted. I end with: 
 
Set objExcel = Nothing 
Set objWorkSheet = Nothing 
Set objWorkbook = Nothing 
Set objRange = Nothing 
 
I noticed during testing, that Excel was opening with a blank sheet but no errors occurred. I opened Task Manager and saw "Excel" listed 23 times, once for each time I ran the routine! 
 
The only time Excel was unloaded was if I ended my entire programme. My questions are: 
 
1. How to run only one Excel instance 
2. Unload Excel completely once this routine is finished. 
 
I certainly do not want 23 instances of this app. 
 
Thanks all - thank you, thank you! 
 
Nick 
Nicholas, Bank of America NetO <a href=mailto: > </a> <a href= > </a>

edderic · Feb 11, 2000

excel_app.ActiveWorkbook.Close False 
 
' Close Excel. 
excel_app.Quit 
Set excel_sheet = Nothing 
Set excel_app = Nothing 
Eric De Decker <a href=mailto:vbg.be@vbgroup.nl>vbg.be@vbgroup.nl</a> <a href=

http://members.tripod.lycos.nl/VisualBasicCenter/>Visual

Basic Center</a>

Nicholas · Feb 11, 2000

Actually the Excel spreadsheet must remain open for the user; however, if closed "Excel.exe" remains in memory and with each run of this routine, another "Excel.exe" remains until my programme is closed. 
 
I resorted to this and it worked well, 
 
1. Created a separate .exe that will create the Excel worksheet and leave Excel Open 
 
2. Then from the The MainApp.exe shelled out to this second .exe as this: RetVal = Shell(app.path & "Some.exe /ToExcel", 1) 
 
3. The second .exe loaded with " If Command <> "/ToExcel" then Unload Me " 
 
4. The second .exe created/formatted the workbook and unloaded 
 
5. The Excel object lost its dependency on the .exe and did not remain in memory. 
 
The original idea was posted here by DougP. 
 
Anyone have better ideas than mine? 
 
- Nick Nicholas, Bank of America NetO <a href=mailto: > </a> <a href= > </a>

Snaggs · Feb 11, 2000

You might try destroying your objects in the opposite order they are created. The first object you create should be the last one you destroy. Instead of doing this: 
 
Set objExcel = GetObject("", "Excel.Application&quot

objExcel.Visible = False 
Set objWorkbook = objExcel.Workbooks.Add 
objExcel.DisplayAlerts = False 
 
Set objExcel = Nothing 
Set objWorkSheet = Nothing 
Set objWorkbook = Nothing 
Set objRange = Nothing 
 
You should close them in this order: 
 
Set objExcel = GetObject("", "Excel.Application&quot

objExcel.Visible = False 
Set objWorkbook = objExcel.Workbooks.Add 
objExcel.DisplayAlerts = False 
 
Set objRange = Nothing 
Set objWorkbook = Nothing 
Set objWorkSheet = Nothing 
Set objExcel = Nothing 
 
By closing objExcel first, it should close all worksheets and workbooks as long as there are no other references to it. It's a good programming practice to do this in the reverse order when you destroy your objects. 
 
For example on a database, if I close the database, then try to close the recordset that was created from the database, I would get an error. Since the recordset was closed when I closed the database. 
 
ie: 
 
Set db = db.OpenDatabase() 
set rs = db.OpenRecordSet() 
 
If I close db now, it will close the recordset too, so if I execute 
 
set db = Nothing, the recordset gets destroyed too. So when I finally get around to. 
 
set rs = Nothing, It's already Nothing and I get an error here. The proper way to close these would be like this: 
 
rs.Close 
set rs = Nothing 
 
db.Close 
set db = Nothing. 
 
Destroy your first created object, last. 
 
Steve 
<A HREF="mailto:sdmeier@jcn1.com">sdmeier@jcn1.com</A>

chiph · Feb 15, 2000

Nick - 
 
    I just had this same problem. Do like Steve said, and destroy your object references in the reverse order in which you created them. 
    Here's a function you (or someone else) might need: 
 
Private Function NumberToExcelColumn(lColNum As Long) As String 
 
    Dim RVal As String 
 
    If lColNum <= 26 Then 
        RVal = Chr$(lColNum + Asc("A&quot

- 1) 
    Else 
        RVal = Chr$((lColNum Mod 26) + Asc("A&quot

- 1) 
        RVal = Chr$((lColNum \ 26) + Asc("A&quot

- 1) & RVal 
    End If 
 
    NumberToExcelColumn = RVal 
End Function 
 
This works OK since Excel doesn't have more than 255 columns, so the conversion to alpha doesn't need more than two places. 
 
BTW, are you in Charlotte, or San Francisco? 
 
Chip H.

Nicholas · Feb 16, 2000

I am at the Villa site in Richmond - are you at the Tempe site? 
 
BTW, I really appreciate all the replies I read to my and others' questions. Great knowledge base we have here! 
 
Nicholas, Bank of America NetO <a href=mailto: > </a> <a href= > </a>

chiph · Feb 16, 2000

Hi Nicholas! 
 
No, I'm not a BofA employee ... I did some contracting work 6 years ago in Charlotte for NationsBanc Services (before the merger), and I know a couple of people there, and a few more in the Bank-with-a-'k' in uptown Charlotte. 
 
Glad I was of some help. 
 
Chip H.

ComputorMike · Jun 8, 2001

Hi Nicholas

If you ever get the correct answer please let me know.
I open over 300 Excel invoices and get the data out of them. No matter how I dispose the Excel Object, in any order there is always an instance of Excel left in memory.
This is not a problem until you try to open Excel again. My only work around has been <CTR><ALT><SHIFT>, the three finger salute and stop the Excel task. This problem is locally and across the Network. Win 2000 pro is a little more stable than Win 98.

Mike ;-(

datras · Jun 8, 2001

Hi
Have you solved the problem?
I also had the problem that excel keept running in the back ground. I found in the end that I first of all did not set all object to nothing and that I was using wrong code when making excel visible. I wrote: excel.application.visible, while it should be objXLApp.visible.

The code below work for me in VBA 2000.

Lena

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objResultsSheet As Excel.Worksheet
Dim objChartSheet As Excel.Worksheet
Dim objXLRange As Excel.Range

Set objXLApp = CreateObject("excel.application&quot

Set objXLBook = objXLApp.Workbooks.Add

objXLBook.Sheets(1).Select
objXLBook.Sheets(1).Name = "Data"
objXLBook.Sheets(2).Select
objXLBook.Sheets(2).Name = "Charts"
Set objResultsSheet = objXLBook.Worksheets("data&quot

Set objChartSheet = objXLBook.Worksheets("charts&quot

' code transferring data and creating charts

objXLApp.Visible = True

objXLBook.Close
objXLApp.Quit
Set objXLRange = Nothing
Set objResultsSheet = Nothing
Set objChartSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

ComputorMike · Jun 8, 2001

Hi Lena
I wrote this function and it did not solve problem.
Maybe I am missing some thing.

Function StopExcel()
Dim oExcelApp As Object
Set oExcelApp = CreateObject("Excel.Application&quot

Do While Not (oExcelApp Is Nothing)
oExcelApp.Quit
Set oExcelApp = Nothing
If Not (oExcelApp Is Nothing) Then oExcelApp.Quit 'Quit Excel
If Not (oExcelApp Is Nothing) Then Set oExcelApp = Nothing
Loop

End Function

MarkSweetland · Jun 8, 2001

Sometimes its easier to use the instance of Excel that is already in memory rather than try to remove them and then create another instance.

' Test to see if there is a copy of
' Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.

' Getobject function called without the first argument
' returns a reference to an instance of the
' application. If the application isn't running,
' an error occurs.

Set xlApp = GetObject(, "Excel.Application&quot

If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.
'Redirect error handling
On Error GoTo err_handler
If ExcelWasNotRunning Then
' Create the first instance
Set xlApp = CreateObject("Excel.application&quot

End If

' At this point xlApp refers to the open Excel Application

Mark

MikeCox · Jun 8, 2001

You may want to visit vb2themax.com. I found a class module there that accepts the contents of an ADO recordset and exports to an Excel spreadsheet. You'll probably have to tweek a couple of things to get it to work for your purposes, but it handles all the Excel stuff (including termination), and the client code can focus on destroying the class, not the Excel application. This also has the benefit of early binding, which is a little faster than CreateObject, and it solved my multiple Excel instance problem. At the end of the export, setting m_objXL.Visible = True means that the user closes the application from within the Excel environment, and I've never see a residual instance of Excel since. Just be sure to create a reference to the Excel type library to support the early binding. Hope that helps!
- Mike

ComputorMike · Jun 11, 2001

Hi Mike
Let me first thank you for bringing Steven Miller code to our attention. He brings to light most of the issues you would want to do with Excel. But even his code leaves Excel in memory. I ASSUME this is due to an inherent problem that active x components used to access Excel leak in memory? The problem is not a showstopper. But I would like to exit and leave Excel out of memory, like an accountant would like to find the last penny. Thanks for the link to Steve Miller good code.

ljprivara · Jun 11, 2001

Mark Sweetland's suggestion is correct. Write a procedure which assumes excel is already open. Of course, if it isn't, a run-time error occurs. However, the "on error" statement takes care of that.

Dim obXLApp As Object
Dim obXLWorkbook As Object

On Error Resume Next

Set obXLApp = GetObject(, "Excel.Application&quot

If Err.Number <> 0 Then
Set obXLApp = CreateObject("Excel.Application&quot

Set obXLWorkbook = obXLApp.Workbooks.Add ' add workbook
End If

Before program terminates, set objects to nothing.

DreewToo · Jun 17, 2001

I'm finding a remaining open instance of excel in my app, but it is created after I try and copy an excel worksheet. After many changes, I'm still lost on what the solution is to stop the new instance of excel from opening.

Code:

Sub ...

xlwb.Worksheets("DETAIL&quot

.Activate
Set xlsheet = xlwb.ActiveSheet

xlsheet.Copy after:=xlapp.ActiveWorkbook.Worksheets("Sheet2&quot

'Second instance of excel is created above
'and exits routine.

Set xlsheet = xlwb.ActiveSheet

What am I missing?
Drew

barrettsteele · Jun 17, 2001

Maybe this isn't related to you guys' exact issue, but I have another question along this line...

I'd like my program to be able to send any of several arrays of data to an Excel graph, and have that graph 'pop up' (or already just be on an existing form, ready to be drawn) with the data plotted.

I'm very much a newbie at VB, and am wondering how this is done -- and is it really more trouble than it's worth?

thanks!
b.

DreewToo · Jun 3, 2002

An unusual solution I found while looking thru CodeHounds.com was arrived at by Dan Jinks and Paul Mazaika. They found that by setting it up as follows, no hidden instance of excel is created:

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objResultsSheet As Object 'Rather than excel.worksheet

Hope this helps,
Drew Bell

dvannoy · Jun 3, 2002

I have been looking for something like this for a while. OK I am goingto ask a real stupid question. How would I get the data from a grid to move to excel once excel opens?? I have been looking through the code and dont really see where it grabs a recordset...can someone fill me in..

Thanks in advance

dvannoy · Jun 4, 2002

ANYONE??

TheKing · Jun 4, 2002

Well dvannoy, I also am comming from a data grid to Excel.
I couldn't get it to work so I just use the recordset that I had prevously used to fill the data grid.

Code:

RS_REP.MoveFirst
s = 0         
    r = RS_REP.Fields.Count
        RowNum = 0
    Do While (RS_REP.EOF = False)
        s = s + 1
        RowID = Trim(s)
            For ColIdx = 1 To r
                ColSet = Int((ColIdx - 1) / 26)
                    If (ColSet = 0) Then
                        ColId = Chr(ColIdx + 64)
                    Else
                        ColId = Chr(ColSet + 64) & Chr((ColIdx - (ColSet * 26)) + 64)
                    End If
                objExcel.Range(ColId & RowID).Value = RS_REP.Fields(ColIdx - 1)
            Next ColIdx
        RS_REP.MoveNext
    Loop
Set objExcel = Nothing

RS_REP is my recordset

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Aughhh! Opening then Closing Excel in App - Excel remains in memory 7

IS-IT--Management

Programmer

IS-IT--Management

Programmer

Programmer

IS-IT--Management

Programmer

MIS

Programmer

MIS

MIS

Programmer

MIS

Programmer

Programmer

Technical User

Programmer

MIS

MIS

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor