Home > Archive > Visual Basic Syntax > April 2005 > Excel remaining in memory
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Excel remaining in memory
|
|
|
| Hi,
Forgive me if this is the wrong group, but I'm not sure which is the correct
group!
I am updating a spreadsheet via VB6 by pasting in the values in a number of
textboxes. The function is shown below. I have tried everything I can
think of, but whatever I do EXCEL.EXE remains in memory, and an additional
instance is created every time I run the function. This is driving me mad,
so I;d really appreaciate any help you could give me.
Many thanks,
Simon
Private Sub cmdSave_Click()
Dim sData As String
sData = ""
For Each oText In Me.txtMU
sData = sData & oText.Text & vbCr
Next
Clipboard.Clear
Clipboard.SetText sData
Dim xlApp As Excel.Application
Dim xlWb As Workbook
Dim xlWs As Worksheet
Dim xlRng As Excel.Range
Set xlApp = New Excel.Application
Set xlWb = xlApp.Workbooks.Open(App.Path & "\pricing.xls")
Set xlWs = xlWb.Worksheets("Admin")
Set xlRng = xlWs.Range("Values")
xlRng.Select
xlWs.Paste
xlWb.Save
xlWb.Close
xlApp.Application.Quit
Set xlRng = Nothing
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
End Sub
| |
| Jeff Johnson [MVP: VB] 2005-02-28, 9:01 pm |
|
"Simon" <Simon@nospam.com> wrote in message
news:cvvrbk$os8$1@sparta.btinternet.com...
> I am updating a spreadsheet via VB6 by pasting in the values in a number
> of textboxes. The function is shown below. I have tried everything I can
> think of, but whatever I do EXCEL.EXE remains in memory, and an additional
> instance is created every time I run the function. This is driving me
> mad, so I;d really appreaciate any help you could give me.
> xlWb.Close
> xlApp.Application.Quit
>
>
> Set xlRng = Nothing
> Set xlWs = Nothing
> Set xlWb = Nothing
> Set xlApp = Nothing
Hmmm. No guarantees, but how about setting xlRng and xlWs to Nothing before
closing xlWb, and setting xlWb to Nothing before calling Quit?
Total shot in the dark.
| |
| Jan Hyde 2005-03-01, 9:00 am |
| "Simon" <Simon@nospam.com>'s wild thoughts were released on
Mon, 28 Feb 2005 19:27:48 +0000 (UTC) bearing the following
fruit:
>Hi,
>
>Forgive me if this is the wrong group, but I'm not sure which is the correct
>group!
>
>I am updating a spreadsheet via VB6 by pasting in the values in a number of
>textboxes. The function is shown below. I have tried everything I can
>think of, but whatever I do EXCEL.EXE remains in memory, and an additional
>instance is created every time I run the function. This is driving me mad,
>so I;d really appreaciate any help you could give me.
>
>Many thanks,
>
>Simon
>
>Private Sub cmdSave_Click()
> Dim sData As String
> sData = ""
> For Each oText In Me.txtMU
> sData = sData & oText.Text & vbCr
> Next
>
> Clipboard.Clear
> Clipboard.SetText sData
>
> Dim xlApp As Excel.Application
> Dim xlWb As Workbook
> Dim xlWs As Worksheet
> Dim xlRng As Excel.Range
>
> Set xlApp = New Excel.Application
> Set xlWb = xlApp.Workbooks.Open(App.Path & "\pricing.xls")
> Set xlWs = xlWb.Worksheets("Admin")
> Set xlRng = xlWs.Range("Values")
>
> xlRng.Select
> xlWs.Paste
> xlWb.Save
>
> xlWb.Close
> xlApp.Application.Quit
>
>
> Set xlRng = Nothing
> Set xlWs = Nothing
> Set xlWb = Nothing
> Set xlApp = Nothing
>
>End Sub
>
Make Excel visible, that may reveal you problem.
Jan Hyde (VB MVP)
--
The first scientists who studied fog were mistified. (Mike Bull)
[Abolish the TV Licence - http://www.tvlicensing.biz/]
| |
|
| Thanks Jan, When I make Excel visible I see the empty application (ie no
workbook is open). Even if I try and 'xlApp.quit' again, it simply hides
the app and leaves it in memory, rather than actually shutting it down.
Of course, if, having made it visible, I manually leave Excel, it is cleared
from memory, but this isn't a solution my client will find acceptable!
Do you have any more thoughts?
Many thanks,
S
| |
|
| > Hmmm. No guarantees, but how about setting xlRng and xlWs to Nothing >
> before closing xlWb, and setting xlWb to Nothing before calling Quit?
> Total shot in the dark.
Thanks Jeff, playing around with the order like that has made no
difference.... any other thoughts?
Simon
| |
| Jan Hyde 2005-03-01, 4:02 pm |
| "Simon" <Simon@nospam.com>'s wild thoughts were released on
Tue, 1 Mar 2005 14:46:48 +0000 (UTC) bearing the following
fruit:
>Thanks Jan, When I make Excel visible I see the empty application (ie no
>workbook is open). Even if I try and 'xlApp.quit' again, it simply hides
>the app and leaves it in memory, rather than actually shutting it down.
>
>Of course, if, having made it visible, I manually leave Excel, it is cleared
>from memory, but this isn't a solution my client will find acceptable!
>
>Do you have any more thoughts?
Start a new project, and open excel with it and then shut it
down again. If it still happens then post that code. If it
doesn't then try adding more of your code to it until the
problem occurs.
Jan Hyde (VB MVP)
--
Polarize: What penguins see with (Hershy)
[Abolish the TV Licence - http://www.tvlicensing.biz/]
| |
|
| Jan, the problem only occurs when I am opening an existing workbook. If I
create a new one, memory is cleared ok. The following Sub shows the
problem:
Private Sub Command1_Click()
Dim xlApp As Excel.Application
Dim xlWb As Workbook
Set xlApp = New Excel.Application
'With this line in place, memory is not cleared
'Set xlWb = xlApp.Workbooks.Open(App.Path & "\testbook.xls")
'With this line in place, memory clears OK
Set xlWb = xlApp.Workbooks.Add
xlWb.Close
xlApp.Quit
Set xlWb = Nothing
Set xlApp = Nothing
End Sub
Again, thanks for your help,
Simon
| |
| Jan Hyde 2005-03-01, 4:02 pm |
| "Simon" <Simon@nospam.com>'s wild thoughts were released on
Tue, 1 Mar 2005 16:20:34 +0000 (UTC) bearing the following
fruit:
>Jan, the problem only occurs when I am opening an existing workbook. If I
>create a new one, memory is cleared ok. The following Sub shows the
>problem:
>
>Private Sub Command1_Click()
> Dim xlApp As Excel.Application
> Dim xlWb As Workbook
>
> Set xlApp = New Excel.Application
>
> 'With this line in place, memory is not cleared
> 'Set xlWb = xlApp.Workbooks.Open(App.Path & "\testbook.xls")
>
> 'With this line in place, memory clears OK
> Set xlWb = xlApp.Workbooks.Add
>
> xlWb.Close
> xlApp.Quit
>
> Set xlWb = Nothing
> Set xlApp = Nothing
>End Sub
>
>Again, thanks for your help,
>
Have you tried testing how many workbooks Excel thinks there
are after you close testbook.xls, also have you tried
recreating from scratch testbook.xls, have you checked out
any potential macros that might be associated with your
workbook?
Jan Hyde (VB MVP)
--
Lawsuit:Worn by lawyers (Jan Hyde)
[Abolish the TV Licence - http://www.tvlicensing.biz/]
| |
| Jan Hyde 2005-03-01, 4:02 pm |
| "Simon" <Simon@nospam.com>'s wild thoughts were released on
Tue, 1 Mar 2005 16:20:34 +0000 (UTC) bearing the following
fruit:
>Jan, the problem only occurs when I am opening an existing workbook. If I
>create a new one, memory is cleared ok. The following Sub shows the
>problem:
>
>Private Sub Command1_Click()
> Dim xlApp As Excel.Application
> Dim xlWb As Workbook
>
> Set xlApp = New Excel.Application
>
> 'With this line in place, memory is not cleared
> 'Set xlWb = xlApp.Workbooks.Open(App.Path & "\testbook.xls")
>
> 'With this line in place, memory clears OK
> Set xlWb = xlApp.Workbooks.Add
>
> xlWb.Close
> xlApp.Quit
>
> Set xlWb = Nothing
> Set xlApp = Nothing
>End Sub
>
>Again, thanks for your help,
>
>Simon
>
Just a thought, but try introducing a delay between closing
the workbook and closing excel.
Jan Hyde (VB MVP)
--
Polarize: What penguins see with (Hershy)
[Abolish the TV Licence - http://www.tvlicensing.biz/]
| |
|
| I've tried all your point and found:
1) After closing testbook.xls, xlApp.workbooks.count returns 0.
2) I created a new file testbook.xls which contains absolutely nothing.
3) No macros anywhere.
4) Tried introducing a delay but with no joy.
If I had any hair left I'd be pulling it out about now!
Dare I ask, any more ideas?
| |
| Jan Hyde 2005-03-02, 8:58 am |
| "Simon" <Simon@nospam.com>'s wild thoughts were released on
Tue, 1 Mar 2005 17:45:58 +0000 (UTC) bearing the following
fruit:
>I've tried all your point and found:
>1) After closing testbook.xls, xlApp.workbooks.count returns 0.
>2) I created a new file testbook.xls which contains absolutely nothing.
>3) No macros anywhere.
>4) Tried introducing a delay but with no joy.
>
>If I had any hair left I'd be pulling it out about now!
>
>Dare I ask, any more ideas?
>
Is it possible that two instances of Excel are somehow
getting opened?
Or how that Excel IS being closed but gets opened again
somewhere else?
Jan Hyde (VB MVP)
--
Foresight: Spectacles are (Jan Hyde)
[Abolish the TV Licence - http://www.tvlicensing.biz/]
| |
|
| I really don't think so. Watching Task Manager as I run the program shows
there is only only ever 1 instance running, and that disappears correctly
when using a newly created worksheet. When opening an existing worksheet it
is left after the program finishes.
As you can see in the test program above, there is nothing else that opens a
2nd instance.
Thanks again for your help!
| |
| Paul Clement 2005-03-07, 9:01 pm |
| On Tue, 1 Mar 2005 14:52:44 +0000 (UTC), "Simon" <Simon@nospam.com> wrote:
¤ > Hmmm. No guarantees, but how about setting xlRng and xlWs to Nothing >
¤ > before closing xlWb, and setting xlWb to Nothing before calling Quit?
¤ > Total shot in the dark.
¤
¤ Thanks Jeff, playing around with the order like that has made no
¤ difference.... any other thoughts?
¤
¤ Simon
¤
If you're not creating any implicit object references and are destroying all explicit object
references there might not be too much more you can do other than to use API function calls:
Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As Any, ByVal
lpWindowName As Any) As Long
Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As
Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Function TerminateExcel()
Dim lpClassName As String
Dim iHwnd As Long
Dim iReturn As Long
Const WM_QUIT = &H12
Do
lpClassName = "XLMain"
iHwnd = FindWindow(lpClassName, 0&)
If iHwnd Then
iReturn = PostMessage(iHwnd, WM_QUIT, 0&, 0&)
End If
Loop Until iHwnd = 0
End Function
Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)
| |
| Gulliver 2005-04-20, 12:19 am |
| quote:
Originally posted by Simon
I've tried all your point and found:
1) After closing testbook.xls, xlApp.workbooks.count returns 0.
2) I created a new file testbook.xls which contains absolutely nothing.
3) No macros anywhere.
4) Tried introducing a delay but with no joy.
If I had any hair left I'd be pulling it out about now!
Dare I ask, any more ideas?
I have worked a very similar problem all evening. I uninstalled (not just exited) Google Desktop, and everything is A-Okay for me. I hope it works for you. Also, I have alerted Google about the concern.
Regards,
Gulliver |
|
|
|
|