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