Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closed workbook remains in memory.
Excel 2000 SP3
I have an add-in I wrote, (One of many) myaddin.xla For a simple breakdown, The add-in opens excel files, updates them via ADO 2.7. Closes and saves the files. The add-in is called from an application called AutoMate. Using the following code. Private Sub Main Dim xlApp As New Excel.Application xlApp.Workbooks.Open "C:\myaddin.xla" xlApp.Visible = True xlApp.Run "MyModuleName" xlApp.Quit Set xlApp = Nothing End Sub This add-in updates 200+ workbooks. The problem I am having is that it is running out of memory about half way through. I am seeing something strange that may be someone else has seen and can give me some advise on. After the add-in opens, updates and closes the workbook. The workbook is still listed in the VBAProject window. Still in memory. It is closing the Excel Workbooks but not destroying them. I am using the following syntax. xlApp.ActiveWorkbook.Close SaveChanges:= True Thank you in advance for any thoughts and or help. Kent Prokopy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closed workbook remains in memory.
Hi Kent,
After the add-in opens, updates and closes the workbook. The workbook is still listed in the VBAProject window. Still in memory. It is closing the Excel Workbooks but not destroying them. I am using the following syntax. This is symptomatic of your VB code continuing to have a reference to the workbook in a variable somewhere. Using the following code. Private Sub Main Dim xlApp As New Excel.Application xlApp.Workbooks.Open "C:\myaddin.xla" xlApp.Visible = True xlApp.Run "MyModuleName" xlApp.Quit Set xlApp = Nothing End Sub You don't explicitly close the workbook here, so Excel may be waiting for a 'Save Changes' prompt. How about: Private Sub Main Dim xlApp As Excel.Application Dim xlWkBk As Excel.Workbook Set xlApp = New Excel.Application Set xlWkBk = xlApp.Workbooks.Open("C:\myaddin.xla") xlApp.Visible = True xlApp.Run "MyModuleName" xlWkBk.Close False Set xlWkBk = Nothing xlApp.Quit Set xlApp = Nothing End Sub Regards Stephen Bullen Microsoft MVP - Excel www.BMSLtd.co.uk |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closed workbook remains in memory.
Stephen
Let me try this again. The section you commented on is the part that calls my add-in. The Add-In is the problem. In the Add-In Loop through the 200+ workbooks Workbooks.Open "FilePathAndName.xls" Populate with some data from ADO calls...This populates up to 100 sheets of data in each workbook. ActiveWorkbook.Close SaveChanges:=True ' This line should close and destroy the workbook. It is not... End Loop "Stephen Bullen" wrote in message ... Hi Kent, After the add-in opens, updates and closes the workbook. The workbook is still listed in the VBAProject window. Still in memory. It is closing the Excel Workbooks but not destroying them. I am using the following syntax. This is symptomatic of your VB code continuing to have a reference to the workbook in a variable somewhere. Using the following code. Private Sub Main Dim xlApp As New Excel.Application xlApp.Workbooks.Open "C:\myaddin.xla" xlApp.Visible = True xlApp.Run "MyModuleName" xlApp.Quit Set xlApp = Nothing End Sub You don't explicitly close the workbook here, so Excel may be waiting for a 'Save Changes' prompt. How about: Private Sub Main Dim xlApp As Excel.Application Dim xlWkBk As Excel.Workbook Set xlApp = New Excel.Application Set xlWkBk = xlApp.Workbooks.Open("C:\myaddin.xla") xlApp.Visible = True xlApp.Run "MyModuleName" xlWkBk.Close False Set xlWkBk = Nothing xlApp.Quit Set xlApp = Nothing End Sub Regards Stephen Bullen Microsoft MVP - Excel www.BMSLtd.co.uk |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closed workbook remains in memory.
Hi Kent,
Workbooks.Open "FilePathAndName.xls" Instead of relying on the ActiveWorkbook, I prefer to use a direct reference: Set oBook = Workbooks.Open(...) 'Do Stuff oBook.Close SaveChanges:=True Populate with some data from ADO calls...This populates up to 100 sheets of data in each workbook. This is where I'd imagine your problem lies, with a reference leaking out. If you comment out those lines, does the rest work fine? Alternatively, are there any other addins that might be hooking a workbook_open event? Regards Stephen Bullen Microsoft MVP - Excel www.BMSLtd.co.uk |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closed workbook remains in memory.
Just a thought: are you using ADO to query an open workbook, perhaps
using Jet's SELECT..INTO syntax targeting the active workbook, thus falling foul of the ADO memory leak bug (MSDN Q319998)? -- Stephen Bullen wrote in message ... Hi Kent, Workbooks.Open "FilePathAndName.xls" Instead of relying on the ActiveWorkbook, I prefer to use a direct reference: Set oBook = Workbooks.Open(...) 'Do Stuff oBook.Close SaveChanges:=True Populate with some data from ADO calls...This populates up to 100 sheets of data in each workbook. This is where I'd imagine your problem lies, with a reference leaking out. If you comment out those lines, does the rest work fine? Alternatively, are there any other addins that might be hooking a workbook_open event? Regards Stephen Bullen Microsoft MVP - Excel www.BMSLtd.co.uk |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closed workbook remains in memory.
Yes I am using ADO. But not on an open excel file.
Stephen, I am going to try your suggestion below. Thank you. "onedaywhen" wrote in message om... Just a thought: are you using ADO to query an open workbook, perhaps using Jet's SELECT..INTO syntax targeting the active workbook, thus falling foul of the ADO memory leak bug (MSDN Q319998)? -- Stephen Bullen wrote in message ... Hi Kent, Workbooks.Open "FilePathAndName.xls" Instead of relying on the ActiveWorkbook, I prefer to use a direct reference: Set oBook = Workbooks.Open(...) 'Do Stuff oBook.Close SaveChanges:=True Populate with some data from ADO calls...This populates up to 100 sheets of data in each workbook. This is where I'd imagine your problem lies, with a reference leaking out. If you comment out those lines, does the rest work fine? Alternatively, are there any other addins that might be hooking a workbook_open event? Regards Stephen Bullen Microsoft MVP - Excel www.BMSLtd.co.uk |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closed workbook remains in memory.
Another thought: if your data source isn't an open workbook, is it
then a closed Excel workbook, MS Access or SQL Server database e.g. you're using the MS OLDDB provider for Jet? If so you may be able to use the SELECT..INTO or INSERT INTO..SELECT with the IN keyword (both are MS proprietary syntax) to update all your workbooks without having to open them. Not only would it avoid the problem of the workbook remaining open (because you never opened it!) it also happens to be faster. BTW this morning I had the problem you described i.e. vba project remains in the VBE after the workbook was closed. Cause? I'd inadvertently been querying an open workbook! -- "Kent Prokopy" wrote in message ... Yes I am using ADO. But not on an open excel file. Stephen, I am going to try your suggestion below. Thank you. "onedaywhen" wrote in message om... Just a thought: are you using ADO to query an open workbook, perhaps using Jet's SELECT..INTO syntax targeting the active workbook, thus falling foul of the ADO memory leak bug (MSDN Q319998)? -- Stephen Bullen wrote in message ... Hi Kent, Workbooks.Open "FilePathAndName.xls" Instead of relying on the ActiveWorkbook, I prefer to use a direct reference: Set oBook = Workbooks.Open(...) 'Do Stuff oBook.Close SaveChanges:=True Populate with some data from ADO calls...This populates up to 100 sheets of data in each workbook. This is where I'd imagine your problem lies, with a reference leaking out. If you comment out those lines, does the rest work fine? Alternatively, are there any other addins that might be hooking a workbook_open event? Regards Stephen Bullen Microsoft MVP - Excel www.BMSLtd.co.uk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening Excel, Book1 opens, remains open with other workbook open | Excel Discussion (Misc queries) | |||
VBAProject remains open after file is closed | Excel Discussion (Misc queries) | |||
Value from a closed workbook | Excel Discussion (Misc queries) | |||
Visual Basic Code Remains in "memory" | Excel Worksheet Functions | |||
copy worksheet from closed workbook to active workbook using vba | Excel Worksheet Functions |