Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBAProjects remain in memory after workbook is closed
I have a rudimentary pricing applicatiion which copies information from a
pricelist into a quote form located on a sheet within the same workbook. The user has a toolbar that they can use to copy this quote sheet to a new file. The program also saves a listing of all quotes generated in this fashion into a summary sheet. After creating the quote file, the application closes the new quote and allows the user to generate a new quote. The problem is that after generating several quotes (like 2-3), and viewing the summary list of quotes once or twice, Excel starts acting really strange. the panes lockup memory errors occur, etc. When running this project in debug mode, the VBAProjects associated with the closed workbooks are still in memory. So after a while there can be 5-10 sets of VBAProjects (with no code or userforms at all) in memory. In the past I learned that using fully qualfiied workbook, sheet and range names eliminated similar problems. But even with fully qualified references, this problem remains. I am urgently trying to release the latest version of this tool to my coworkers, but I am stumped as to the cause of this problem. Any ideas? Thanks, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBAProjects remain in memory after workbook is closed
Hi Ali,
Better you post your first running code, and let me guess that possibility is by : Dim Wb as Excel.Application or something like that, that not bring the object visible is true but visible is false . try to post your code ... that error occured Thanks, hAlim Arif Ali menuliskan: In the past I learned that using fully qualfiied workbook, sheet and range names eliminated similar problems. But even with fully qualified references, this problem remains. I am urgently trying to release the latest version of this tool to my coworkers, but I am stumped as to the cause of this problem. Any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBAProjects remain in memory after workbook is closed
Nick and hAlim,
Your emails both confirm my that it must be a qualification or object-release issue. I will check the code once more (a little later today) once more and if I cant see anything I will post the code. Otherwise I'll notify you what I found. Thanks, " wrote: Hi Ali, Better you post your first running code, and let me guess that possibility is by : Dim Wb as Excel.Application or something like that, that not bring the object visible is true but visible is false . try to post your code ... that error occured Thanks, hAlim Arif Ali menuliskan: In the past I learned that using fully qualfiied workbook, sheet and range names eliminated similar problems. But even with fully qualified references, this problem remains. I am urgently trying to release the latest version of this tool to my coworkers, but I am stumped as to the cause of this problem. Any ideas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBAProjects remain in memory after workbook is closed
OK Guys, I give up. I hope you still have your notifications on for this
posting! I was hesitant to post my code in the fear that it would be too messy (embarassing) or would require too much explanation. But at present I'm stumped. I'm obviously missing the point on this qualification issue so here goes. The general idea is that my "Quoter" workbook consists of: 1. A sheet called QuoteForm 2. A sheet called OrderEntry Form 3. Several Sheets with catalog items: part numbers, descriptions, prices; one on each row 4. There is a navigation toolbar (userform) to move to different sheets The user simply double clicks on the catalog items to be added to the quote, and they appear on the quote form and orderentry form. (For now this code is not included). When all items have been added, the user then saves the quoteform and/or order entry forms to a separate workbook. There are separate buttons that launch each of these options. We'll get to that part a little later. Remember the problem is that each time I open and subsequently close a workbook, the VBAProject says in memory. The only VBAProject that should stay in memory throughout the process is the main workbook that contains the code below. Public QSPath As String Public oExcel As Excel.Application Public QS, Quoter, NewBook As Excel.Workbook Public ParentSheet, FactorySuite, QuoteForm, OrderEntry, NewQuoteForm, _ NewOrderEntry As Excel.Worksheet Private Sub Workbook_Open() 'first define and assign global variables Dim QuoteSummaryExists As Boolean Set oExcel = GetObject(, "Excel.Application") Set Quoter = oExcel.ActiveWorkbook Set QuoteForm = Quoter.Sheets("QuoteForm") Set OrderEntry = Quoter.Sheets("OrderEntry") Set FactorySuite = Quoter.Sheets("FactorySuite") ThisWorkbook.Bookname = Quoter.Name ' Save name of activeworkbook Set fs = CreateObject("Scripting.FileSystemObject") homedrive = Environ("HOMEDRIVE") 'Capture Drive Letter Homepath = Environ("HOMEPATH") 'Capture MyDocuments Path Homepath = Homepath & "\My Documents\" QSPath = homedrive & Homepath ' Set QuoteSumary Path QuoteSummaryExists = fs.fileexists(QSPath & "QuoteSummary.xls") 'Check for existence of QuoteSummary If Not QuoteSummaryExists Then Call CreateQuoteSummary End If Quoter.Activate QuoteForm.Activate 'Switch to Quoter Spreadsheet If (QuoteForm.Range("b24").Value < "") Or (QuoteForm.Range("c15").Value < "") Or (QuoteForm.Range("M15").Value < "") Then vbans = vbNo vbans = MsgBox("Erase Existing Quote Information?", vbYesNo) If vbans = vbYes Then QuoteForm.Range("m17..r17").Value = CDate((Now())) 'Customer Name QuoteForm.Range("C15..I15").Value = "" ' Name QuoteForm.Range("C16..I16").Value = "" 'Address QuoteForm.Range("C17..I17").Value = "" 'City QuoteForm.Range("C18..E18").Value = "" 'State QuoteForm.Range("g18").Value = "" 'zip QuoteForm.Range("I18").Value = "" 'Quote Number QuoteForm.Range("m15..r15").Value = "" 'Salesperson ' QuoteForm.Range("m16..r16").Value = "" 'Quote Date ' QuoteForm.Range("m17..r17").Value = "" 'QuoteBody QuoteForm.Range("b24..r44").Value = "" End If End If oExcel.ActiveWindow.ScrollRow = 1 QuoteForm.Range("A1").Value = "" QuoteForm.Range("B24").Select ActiveCell.Value = "" 'ActiveWorkbook.Sheets("FactorySuite").Activate FactorySuite.Range("A1").Select UserForm1.MultiPage1.Style = fmTabStyleTabs UserForm1.Show 0 UserForm1.Left = 560 UserForm1.Top = 30 Set fs = Nothing End Sub If the quote summary does not exist, create it! Private Sub CreateQuoteSummary() Dim NewBook As Excel.Workbook oExcel.ScreenUpdating = False Set NewBook = oExcel.Workbooks.Add NewBook.Activate NewBook.Sheets("Sheet1").Activate NewBook.Sheets("Sheet1").Range("A1").Value = "Salesman" NewBook.Sheets("Sheet1").Range("B1").Value = "Quote Date" NewBook.Sheets("Sheet1").Range("C1").Value = "Customer" NewBook.Sheets("Sheet1").Range("D1").Value = "Quote Num" NewBook.Sheets("Sheet1").Range("E1").Value = "Quote Amt" NewBook.Sheets("Sheet1").Range("F1").Value = "FileName" NewBook.Sheets("Sheet1").Range("a1").Select NewBook.ActiveSheet.Name = "Quotes" NewBook.SaveAs (QSPath & "QuoteSummary.xls") NewBook.Close savechanges:=False oExcel.ScreenUpdating = True Set NewBook = Nothing End Sub From then on, as this spreadsheet gets populated in subsequent runs of the tool, there is a button click event that calls the following routine. This routine opens the quote summary, populates a listbox and then closes the spreadsheet. Public Sub InitQuoteSummaryWindow() oExcel.ScreenUpdating = False Set QS = oExcel.Workbooks.Add(QSPath & "QuoteSummary.xls") QS.Sheets("quotes").Activate QS.ActiveSheet.Range("A1").Select i = 0 Do While ActiveCell.Value < "" i = i + 1 ActiveCell.Offset(1, 0).Select Loop frmQuoteSummary.ListBox1.Font.Name = "Arial" frmQuoteSummary.ListBox1.Font.Size = 10 frmQuoteSummary.ListBox1.ColumnCount = 6 QS.Activate frmQuoteSummary.ListBox1.ColumnHeads = False frmQuoteSummary.ListBox1.RowSource = "a1:f" & CStr(i) frmQuoteSummary.ListBox1.MultiSelect = fmMultiSelectSingle frmQuoteSummary.ListBox1.ColumnWidths = "72;108;108;108;96;96" frmQuoteSummary.ListBox1.TextAlign = fmTextAlignLeft oExcel.ScreenUpdating = True QS.Close Set QS = Nothing End Sub Now lets look at how quotes and order entryforms are generated and saved. Here is the button click event one for SaveNewOrderEntry. Note that this event exists in a separate module and therefore includes the prefix Thisworkbook. before the global variables like QuoteForm, etc. (I have not included the code for the two other varioations: SaveQuoteForm and SaveBoth. If we cant solve the issue looking at the code above, we can look at those too.) Private Sub btnSaveNewOE_Click() Set ThisWorkbook.NewBook = ThisWorkbook.oExcel.Workbooks.Add ThisWorkbook.OrderEntry.Copy Befo=ThisWorkbook.NewBook.Sheets("Sheet1") ThisWorkbook.NewBook.Activate fname = ThisWorkbook.oExcel.GetSaveAsFilename(InitialFileN ame:=(Range("c9").Value) & " " & Format(Now(), "mmddyy")) If fname < False Then If Right(fname, 1) = "." Then fname = Left(fname, Len(fname) - 1) End If If Right(fname, 3) = "xls" Then ThisWorkbook.NewBook.SaveAs Filename:=fname Else ThisWorkbook.NewBook.SaveAs Filename:=fname & ".xls" End If ThisWorkbook.NewBook.Close savechanges:=False Else ThisWorkbook.NewBook.Close savechanges:=False End If Set ThisWorkbook.NewBook = Nothing End Sub Thanks in advance!!!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBAProjects remain in memory after workbook is closed
At a quick glance:
All this code is in a workbook ? If so, there's no need for the variable created in "Set oExcel = GetObject(, "Excel.Application")", as you already have the global Application object you can use. As, you are not setting this to Nothing (or it's not shown here anyway), it would maintain a reference to Excel from your WB and prevent it from unloading. Change this and see if it helps. NickHK "Arif Ali" wrote in message ... OK Guys, I give up. I hope you still have your notifications on for this posting! I was hesitant to post my code in the fear that it would be too messy (embarassing) or would require too much explanation. But at present I'm stumped. I'm obviously missing the point on this qualification issue so here goes. The general idea is that my "Quoter" workbook consists of: 1. A sheet called QuoteForm 2. A sheet called OrderEntry Form 3. Several Sheets with catalog items: part numbers, descriptions, prices; one on each row 4. There is a navigation toolbar (userform) to move to different sheets The user simply double clicks on the catalog items to be added to the quote, and they appear on the quote form and orderentry form. (For now this code is not included). When all items have been added, the user then saves the quoteform and/or order entry forms to a separate workbook. There are separate buttons that launch each of these options. We'll get to that part a little later. Remember the problem is that each time I open and subsequently close a workbook, the VBAProject says in memory. The only VBAProject that should stay in memory throughout the process is the main workbook that contains the code below. Public QSPath As String Public oExcel As Excel.Application Public QS, Quoter, NewBook As Excel.Workbook Public ParentSheet, FactorySuite, QuoteForm, OrderEntry, NewQuoteForm, _ NewOrderEntry As Excel.Worksheet Private Sub Workbook_Open() 'first define and assign global variables Dim QuoteSummaryExists As Boolean Set oExcel = GetObject(, "Excel.Application") Set Quoter = oExcel.ActiveWorkbook Set QuoteForm = Quoter.Sheets("QuoteForm") Set OrderEntry = Quoter.Sheets("OrderEntry") Set FactorySuite = Quoter.Sheets("FactorySuite") ThisWorkbook.Bookname = Quoter.Name ' Save name of activeworkbook Set fs = CreateObject("Scripting.FileSystemObject") homedrive = Environ("HOMEDRIVE") 'Capture Drive Letter Homepath = Environ("HOMEPATH") 'Capture MyDocuments Path Homepath = Homepath & "\My Documents\" QSPath = homedrive & Homepath ' Set QuoteSumary Path QuoteSummaryExists = fs.fileexists(QSPath & "QuoteSummary.xls") 'Check for existence of QuoteSummary If Not QuoteSummaryExists Then Call CreateQuoteSummary End If Quoter.Activate QuoteForm.Activate 'Switch to Quoter Spreadsheet If (QuoteForm.Range("b24").Value < "") Or (QuoteForm.Range("c15").Value < "") Or (QuoteForm.Range("M15").Value < "") Then vbans = vbNo vbans = MsgBox("Erase Existing Quote Information?", vbYesNo) If vbans = vbYes Then QuoteForm.Range("m17..r17").Value = CDate((Now())) 'Customer Name QuoteForm.Range("C15..I15").Value = "" ' Name QuoteForm.Range("C16..I16").Value = "" 'Address QuoteForm.Range("C17..I17").Value = "" 'City QuoteForm.Range("C18..E18").Value = "" 'State QuoteForm.Range("g18").Value = "" 'zip QuoteForm.Range("I18").Value = "" 'Quote Number QuoteForm.Range("m15..r15").Value = "" 'Salesperson ' QuoteForm.Range("m16..r16").Value = "" 'Quote Date ' QuoteForm.Range("m17..r17").Value = "" 'QuoteBody QuoteForm.Range("b24..r44").Value = "" End If End If oExcel.ActiveWindow.ScrollRow = 1 QuoteForm.Range("A1").Value = "" QuoteForm.Range("B24").Select ActiveCell.Value = "" 'ActiveWorkbook.Sheets("FactorySuite").Activate FactorySuite.Range("A1").Select UserForm1.MultiPage1.Style = fmTabStyleTabs UserForm1.Show 0 UserForm1.Left = 560 UserForm1.Top = 30 Set fs = Nothing End Sub If the quote summary does not exist, create it! Private Sub CreateQuoteSummary() Dim NewBook As Excel.Workbook oExcel.ScreenUpdating = False Set NewBook = oExcel.Workbooks.Add NewBook.Activate NewBook.Sheets("Sheet1").Activate NewBook.Sheets("Sheet1").Range("A1").Value = "Salesman" NewBook.Sheets("Sheet1").Range("B1").Value = "Quote Date" NewBook.Sheets("Sheet1").Range("C1").Value = "Customer" NewBook.Sheets("Sheet1").Range("D1").Value = "Quote Num" NewBook.Sheets("Sheet1").Range("E1").Value = "Quote Amt" NewBook.Sheets("Sheet1").Range("F1").Value = "FileName" NewBook.Sheets("Sheet1").Range("a1").Select NewBook.ActiveSheet.Name = "Quotes" NewBook.SaveAs (QSPath & "QuoteSummary.xls") NewBook.Close savechanges:=False oExcel.ScreenUpdating = True Set NewBook = Nothing End Sub From then on, as this spreadsheet gets populated in subsequent runs of the tool, there is a button click event that calls the following routine. This routine opens the quote summary, populates a listbox and then closes the spreadsheet. Public Sub InitQuoteSummaryWindow() oExcel.ScreenUpdating = False Set QS = oExcel.Workbooks.Add(QSPath & "QuoteSummary.xls") QS.Sheets("quotes").Activate QS.ActiveSheet.Range("A1").Select i = 0 Do While ActiveCell.Value < "" i = i + 1 ActiveCell.Offset(1, 0).Select Loop frmQuoteSummary.ListBox1.Font.Name = "Arial" frmQuoteSummary.ListBox1.Font.Size = 10 frmQuoteSummary.ListBox1.ColumnCount = 6 QS.Activate frmQuoteSummary.ListBox1.ColumnHeads = False frmQuoteSummary.ListBox1.RowSource = "a1:f" & CStr(i) frmQuoteSummary.ListBox1.MultiSelect = fmMultiSelectSingle frmQuoteSummary.ListBox1.ColumnWidths = "72;108;108;108;96;96" frmQuoteSummary.ListBox1.TextAlign = fmTextAlignLeft oExcel.ScreenUpdating = True QS.Close Set QS = Nothing End Sub Now lets look at how quotes and order entryforms are generated and saved. Here is the button click event one for SaveNewOrderEntry. Note that this event exists in a separate module and therefore includes the prefix Thisworkbook. before the global variables like QuoteForm, etc. (I have not included the code for the two other varioations: SaveQuoteForm and SaveBoth. If we cant solve the issue looking at the code above, we can look at those too.) Private Sub btnSaveNewOE_Click() Set ThisWorkbook.NewBook = ThisWorkbook.oExcel.Workbooks.Add ThisWorkbook.OrderEntry.Copy Befo=ThisWorkbook.NewBook.Sheets("Sheet1") ThisWorkbook.NewBook.Activate fname = ThisWorkbook.oExcel.GetSaveAsFilename(InitialFileN ame:=(Range("c9").Value) & " " & Format(Now(), "mmddyy")) If fname < False Then If Right(fname, 1) = "." Then fname = Left(fname, Len(fname) - 1) End If If Right(fname, 3) = "xls" Then ThisWorkbook.NewBook.SaveAs Filename:=fname Else ThisWorkbook.NewBook.SaveAs Filename:=fname & ".xls" End If ThisWorkbook.NewBook.Close savechanges:=False Else ThisWorkbook.NewBook.Close savechanges:=False End If Set ThisWorkbook.NewBook = Nothing End Sub Thanks in advance!!!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBAProjects remain in memory after workbook is closed
Yes all the code is in the workbook. I only added the oExcel as a result of
the problems I was having. Although the "Set oExcel = nothing" is not shown (I just recently added it to the workbook "BeforeClose" event) remember that many workbooks get opened and closed during the use of this application. Those are the ones I am seeing stuck in memory. In other words, does it really matter that I am not setting oExcel to Nothing, if the problems that I am seeing occur before I expect to release oExcel? Would it help for me to clarify further the operation of my book? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBAProjects remain in memory after workbook is closed
I just posted a response to this but it didnt show up yet so, first:
1. I only added the oExcel because of the problems I was having. 2. I destroy oExcel in the workbook beforeclose event. 3. Long before oExcel is nullified, there are several other books that are created and/or opened and closed. I cant see why this will have any effect on events that take place before I am done with oExcel. HOWEVER, I did take out the ref's to oExcel just now, and tested only the "InitQuoteSummary" function -- the QuoteSummary workbooks VBAProject stays in memory as QuoteSummary1. Even though Workbooks.Count = 1, i.e., the master project. Arif PS: Can you llok at Michael Beckinsales issue labelled: Repost: Excel not Active. I am afraid my intuition (i.e. as reflected in my response!) may have been wrong there. Especially since I am grappling with this current issue. Thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBAProjects remain in memory after workbook is closed
- My version of Excel (2K) does not have ThisWorkbook.BookName, so I guess
it is custom property ? - Range("C17..I17") cannot be valid; 2 x "." - Which line do you expect the workbook closed and the project to disappear from from the VBA IDE ? - You don't need all those .Activate and .Select. Just work with the WB/WS reference. These are mixed with oExcel.<Property and ActiveCell, so it confusing what you are referencing. - What is the routine "InitQuoteSummaryWindow" supposed to achieve ? - I don't understand why you need lines like "ThisWorkbook.oExcel.GetSaveAsFilename(..." NickHK "Arif Ali" wrote in message ... Yes all the code is in the workbook. I only added the oExcel as a result of the problems I was having. Although the "Set oExcel = nothing" is not shown (I just recently added it to the workbook "BeforeClose" event) remember that many workbooks get opened and closed during the use of this application. Those are the ones I am seeing stuck in memory. In other words, does it really matter that I am not setting oExcel to Nothing, if the problems that I am seeing occur before I expect to release oExcel? Would it help for me to clarify further the operation of my book? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBAProjects remain in memory after workbook is closed
Nick,
1. Bookname is variable I created. May No longer be needed. 2. THe places where I expect WB's to be closed are where I open, or create a new, WB and then close it. The simplest example when the user clicks the ViewQuoteSummaryButton, the InitQuoteSummary routine opens quotesummary.xls, populates a listbox, and close quotesummary.xls. Then the listbox is shown until they close it. 3. The initquotesummary could be renamed since its purpose changed slightly since it was created. It could be called populate_QS_List. 4. Fair enough about the confusion...ThisWorkbook.oExcel was put in since oExcel is defined in ThisWorkbook section of the project, whereas some of the subroutines are part of the UserForm object. Barring any revelations you might have, I will probably strip down this code and remove all WB-related subs, clean up the code, and then add them back in one at a time. This program has evolved over time and could use a little cleanup. Arif "NickHK" wrote: - My version of Excel (2K) does not have ThisWorkbook.BookName, so I guess it is custom property ? - Range("C17..I17") cannot be valid; 2 x "." - Which line do you expect the workbook closed and the project to disappear from from the VBA IDE ? - You don't need all those .Activate and .Select. Just work with the WB/WS reference. These are mixed with oExcel.<Property and ActiveCell, so it confusing what you are referencing. - What is the routine "InitQuoteSummaryWindow" supposed to achieve ? - I don't understand why you need lines like "ThisWorkbook.oExcel.GetSaveAsFilename(..." NickHK "Arif Ali" wrote in message ... Yes all the code is in the workbook. I only added the oExcel as a result of the problems I was having. Although the "Set oExcel = nothing" is not shown (I just recently added it to the workbook "BeforeClose" event) remember that many workbooks get opened and closed during the use of this application. Those are the ones I am seeing stuck in memory. In other words, does it really matter that I am not setting oExcel to Nothing, if the problems that I am seeing occur before I expect to release oExcel? Would it help for me to clarify further the operation of my book? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBAProjects remain in memory after workbook is closed
Clean it up first, using only "Application" to reference Excel, as you do
not need the oExcel reference at all. Try that first and see what you have then. NickHK "Arif Ali" wrote in message ... Nick, 1. Bookname is variable I created. May No longer be needed. 2. THe places where I expect WB's to be closed are where I open, or create a new, WB and then close it. The simplest example when the user clicks the ViewQuoteSummaryButton, the InitQuoteSummary routine opens quotesummary.xls, populates a listbox, and close quotesummary.xls. Then the listbox is shown until they close it. 3. The initquotesummary could be renamed since its purpose changed slightly since it was created. It could be called populate_QS_List. 4. Fair enough about the confusion...ThisWorkbook.oExcel was put in since oExcel is defined in ThisWorkbook section of the project, whereas some of the subroutines are part of the UserForm object. Barring any revelations you might have, I will probably strip down this code and remove all WB-related subs, clean up the code, and then add them back in one at a time. This program has evolved over time and could use a little cleanup. Arif "NickHK" wrote: - My version of Excel (2K) does not have ThisWorkbook.BookName, so I guess it is custom property ? - Range("C17..I17") cannot be valid; 2 x "." - Which line do you expect the workbook closed and the project to disappear from from the VBA IDE ? - You don't need all those .Activate and .Select. Just work with the WB/WS reference. These are mixed with oExcel.<Property and ActiveCell, so it confusing what you are referencing. - What is the routine "InitQuoteSummaryWindow" supposed to achieve ? - I don't understand why you need lines like "ThisWorkbook.oExcel.GetSaveAsFilename(..." NickHK "Arif Ali" wrote in message ... Yes all the code is in the workbook. I only added the oExcel as a result of the problems I was having. Although the "Set oExcel = nothing" is not shown (I just recently added it to the workbook "BeforeClose" event) remember that many workbooks get opened and closed during the use of this application. Those are the ones I am seeing stuck in memory. In other words, does it really matter that I am not setting oExcel to Nothing, if the problems that I am seeing occur before I expect to release oExcel? Would it help for me to clarify further the operation of my book? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBAProjects remain in memory after workbook is closed
Hi Nick and Ali,
You're right Nick, using Application reference which already exsist or available from its application is ineficient way look, because wee have to tidy them up after declare them, just try what Nick said, clean them up. I think Ali codes is very complicated, so another developer maybe have to read them all before reconstruct them. Thanks, Halim NickHK menuliskan: Clean it up first, using only "Application" to reference Excel, as you do not need the oExcel reference at all. Try that first and see what you have then. NickHK |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBAProjects remain in memory after workbook is closed
A couple of other things:
You are aware that lines like: Public QS, Quoter, NewBook As Excel.Workbook actually only give you NewBook as a WB. All the other variables are variants, essentially untyped. That's why you can get syntax help when you type "NewBook" + ".", but nothing with QS or Quoter. Why not create a QuoteSummary as a template, so you do not have to create it every time. You do not need the FSO just to check if a file exists; use GetAttr() or one of the other native VBA methods. Always use Option Explicit to force declaration of variables. Using GetObject does not guarantee you which instance of Excel you receive a reference to, if you have more than 1 instance running at the time. So I suppose in theory, oExcel may be completely different from Application, which would (I assume) not be what you are expected. Look into using named ranges. This makes it much easier to refer to cell(s). If you move them on the WS, you not have to update you code to reflect these change. I've tried to clear up your code, but there is much that I don't understand what you trying to achieve and some that is completely invalid e.g. Range("m17..r17"). NickHK "Arif Ali" wrote in message ... Nick, 1. Bookname is variable I created. May No longer be needed. 2. THe places where I expect WB's to be closed are where I open, or create a new, WB and then close it. The simplest example when the user clicks the ViewQuoteSummaryButton, the InitQuoteSummary routine opens quotesummary.xls, populates a listbox, and close quotesummary.xls. Then the listbox is shown until they close it. 3. The initquotesummary could be renamed since its purpose changed slightly since it was created. It could be called populate_QS_List. 4. Fair enough about the confusion...ThisWorkbook.oExcel was put in since oExcel is defined in ThisWorkbook section of the project, whereas some of the subroutines are part of the UserForm object. Barring any revelations you might have, I will probably strip down this code and remove all WB-related subs, clean up the code, and then add them back in one at a time. This program has evolved over time and could use a little cleanup. Arif "NickHK" wrote: Option Explicit Public QSPath As String Public QS As Workbook, NewBook As Workbook Public FactorySuite As Worksheet, QuoteForm As Worksheet, OrderEntry As Worksheet Private Sub Workbook_Open() Dim RetVal As Variant Set QuoteForm = Thisworkbook.Sheets("QuoteForm") Set OrderEntry = Thisworkbook.Sheets("OrderEntry") Set FactorySuite = Thisworkbook.Sheets("FactorySuite") QSPath = GetMyDocPath1 ' Set QuoteSumary Path" On Error Resume Next RetVal = GetAttr(QSPath & "\QuoteSummary.xls") If Err.Number 0 Then Call CreateQuoteSummary End If On Error GoTo 0 With QuoteForm If (.Range("b24").Value < "") Or (.Range("c15").Value < "") Or (.Range("M15").Value < "") Then RetVal = MsgBox("Erase Existing Quote Information?", vbYesNo) If RetVal = vbYes Then 'This "m17..r17" is invalid. I don't know which range you mean '.Range("m17..r17").Value = CDate((Now())) 'Does this ("b24..r44") mean all cells between B24 & R44 ? 'Anyway 'Clear data .Range("C15:E18").ClearContents .Range("I15:I18").ClearContents .Range("G18").ClearContents End If End If End With 'What do this achieve ? With FactorySuite .Range("A1").Select End With 'Put this in the UserForm_Initialize events 'UserForm1.MultiPage1.Style = fmTabStyleTabs 'UserForm1.Show 0 'UserForm1.Left = 560 'UserForm1.Top = 30 End Sub 'Make a template so you do not nedd this Private Sub CreateQuoteSummary() ScreenUpdating = False With Workbooks.Add With .Sheets("Sheet1") .Range("A1").Value = "Salesman" .Range("B1").Value = "Quote Date" .Range("C1").Value = "Customer" .Range("D1").Value = "Quote Num" .Range("E1").Value = "Quote Amt" .Range("F1").Value = "FileName" .Name = "Quotes" End With .SaveAs QSPath & "QuoteSummary.xls" .Close savechanges:=False End With ScreenUpdating = True End Sub 'Is frmQuoteSummary a UserForm ? 'In which workbook ? 'If in "QuoteSummary.xls" then this routine is meaningless, because you just open then close the WB 'If in another WB, then the[ListBox1.RowSource = "a1:f" & CStr(i)], will NOT refer to QS.Sheets("quotes") 'So, I don't understand what you are trying to do here. Public Sub InitQuoteSummaryWindow() Dim RowCount As Long ScreenUpdating = False Set QS = Workbooks.Open(QSPath & "QuoteSummary.xls") RowCount = QS.Sheets("quotes").Range("A1").CurrentRegion.Rows .Count With frmQuoteSummary.ListBox1 .Font.Name = "Arial" .Font.Size = 10 .ColumnCount = 6 .ColumnHeads = False .RowSource = "a1:f" & CStr(RowCount) .MultiSelect = fmMultiSelectSingle .ColumnWidths = "72;108;108;108;96;96" .TextAlign = fmTextAlignLeft End With ScreenUpdating = True QS.Close False Set QS = Nothing End Sub 'I don't understand what you are trying to do here, except create and save an empty file Private Sub btnSaveNewOE_Click() Dim FName As String With Workbooks.Add '[Range("c9").Value] refers to the activebook. Which is that ? FName = Thisworkbook.GetSaveAsFilename(InitialFileName:=(R ange("c9").Value) & " " & Format(Now(), "mmddyy")) If FName < False Then If Right(FName, 1) = "." Then FName = Left(FName, Len(FName) - 1) End If If Right(FName, 3) = "xls" Then .SaveAs Filename:=FName Else .SaveAs Filename:=FName & ".xls" End If End If .Close savechanges:=False End With End Sub Private Function GetMyDocPath1() As String GetMyDocPath1 = Environ("HOMEDRIVE") & Environ("HOMEPATH") & "\My Documents" End Function |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBAProjects remain in memory after workbook is closed
Nick and h.,
Sorry I didnt see your latest postings since 8-2. Thanks for your comments, esp Nick's on your last posting. There were some things that I didnt realize like the Public declaration issue. The reason I have(m17..r17) is that the quoteform sheet, created in the past by someone else as a standalone spreadsheet (no code) has many merged fields. I found this addressing mechanism the most efficient to make sure the data actually appears properly. I am so desparate to complete this project as there are others right behind it. However, I have been pulled into another endeavor that may keep me away from this until Monday (8-7). Stay tuned as I expect to work on this over the weekend. Thanks again, |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBAProjects remain in memory after workbook is closed
Guys,
Clearly there are some basic rules I have been violating, but let's simplify things. Take this routine for example - I changed the name from "InitQuoteSummaryWindow" to "DisplayQuoteSummaryListbox". This code exists in the Main Workbook. By the way there is NO code in any other workbook except this one. However there is code elsewhere in this workbook, behind UserForm1. UserForm1 is a toolbar with lot of different buttons on it. When the User clicks a button called "View Quote Summary", the button_click routine (located behind UserForm1 calls Thisworkbook.DisplayQuoteSummaryListbox. Subsequent to that call is the line frmQuoteSummary.Show. That's the only code in the button_Click routine for btnViewQuoteSummary. The sole purpose of this code is to: 1. Open the QuoteSummary.XLS workbook. 2. Populate a Listbox with its contents (this all works by the way) 3. Close the workbook. For the time being, the purpose of this listbox is so the user can simply view all the quotes that were generated in the past. Later I will enhance this to make it possible to open a previously saved quote by double-clicking within the listbox. But for right now it is just a viewer. I recognize that the ListBox lives on after QuoteSummary.xls is closed, but I do not believe that the listbox retains any connection to the workbook. Again it is only a viewer of historical information. For the time being, all a user can do is view it and close it when done. One other note: in order to reduce accidental mis-references, I have switched to 99% local references rather than global references. The only Global reference I still have is: Public Quoter as Excel.Workbook ' Referring to the main workbook This mean that aside from the above, within each routine, all references are created and assigned at the beginning of the sub and set to nothing at the end. As far as I'm concerned the following routine is a very simple routine, but I have two problems: 1. After the first time it is run, subsequent runs leave a copy of the VBAProject QuoteSummary1 in memory. If it runs 4 times, there are 3 copies of the VBAProject in memory! 2. There are no click event associated with the listbox. However, if I click on an item in the listbox, I get the message "There is not enough memory to complete this operation". (What operation?!) Public Sub DisplayQuoteSummaryListbox() Dim QS As Excel.Workbook Dim i As Integer Application.ScreenUpdating = False Set QS = Workbooks.Add(QSPath & "QuoteSummary.xls") QS.Sheets("Quotes").Activate QS.ActiveSheet.Range("A1").Select i = 0 Do While ActiveCell.Value < "" i = i + 1 ActiveCell.Offset(1, 0).Select Loop frmQuoteSummary.ListBox1.Font.Name = "Arial" frmQuoteSummary.ListBox1.Font.Size = 10 frmQuoteSummary.ListBox1.ColumnCount = 6 QS.Activate frmQuoteSummary.ListBox1.ColumnHeads = False frmQuoteSummary.ListBox1.RowSource = "a1:f" & CStr(i) frmQuoteSummary.ListBox1.MultiSelect = fmMultiSelectSingle frmQuoteSummary.ListBox1.ColumnWidths = "72;108;108;108;96;96" frmQuoteSummary.ListBox1.TextAlign = fmTextAlignLeft Application.ScreenUpdating = True QS.Close Set QS = Nothing End Sub |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBAProjects remain in memory after workbook is closed
I have now narrowed my Excel questions to two, and it seems that one is
unrelated to my code: 1. If you open Excel, it normally defaults to openning a template and calling the workbook Book1. If you go to Tools-Macros-Visual Basic Editor, you can see the modules (perhaps empty) associated with Book1. Now ALT-Tab to return to the spreadsheet (leaving the VBA Project open) and click File-Close. When you switch back to the VB Editor, the VBA Project for Book1 is still in memory! Why is that? (I have been pulling my hair out thinking that my code was creating this situation; however it appears that Excel does this with no help from me!) 2. I am using the following code to populate a Listbox in a UserForm. There are 6 columns in this listbox. As you can see the ListBox remains open at the end of the Sub. However, the QS spreadsheet is closed. I believe this is causing a problem since I have defined a rowsource that is in a spreadsheet that has been closed. I need a different way to populate the listbox - probably just need to remember or learn how to populate a listbox with AddItem. However I can not get it to work. THIS code results in the error "Not enough storage to complete operation", if you click on an item in the listbox. Further below (in item 2a) is the rewrite of this routine without rowsource, which also isnt working, Public Sub InitQuoteSummaryWindow() 'Dim MyFont As Font Application.ScreenUpdating = False Set QS = oExcel.Workbooks.Add(QSPath & "QuoteSummary.xls") QS.Sheets("Quotes").Activate QS.ActiveSheet.Range("A1").Select i = 0 Do While ActiveCell.Value < "" i = i + 1 ActiveCell.Offset(1, 0).Select Loop frmQuoteSummary.ListBox1.Font.Name = "Arial" frmQuoteSummary.ListBox1.Font.Size = 10 frmQuoteSummary.ListBox1.ColumnCount = 6 QS.Activate frmQuoteSummary.ListBox1.ColumnHeads = False frmQuoteSummary.ListBox1.RowSource = "a1:f" & CStr(i) frmQuoteSummary.ListBox1.MultiSelect = fmMultiSelectSingle frmQuoteSummary.ListBox1.ColumnWidths = "72;108;108;108;96;96" frmQuoteSummary.ListBox1.TextAlign = fmTextAlignLeft Application.ScreenUpdating = True QS.Close Set QS = Nothing End Sub 2a. Revised COde which does not completely work, but avoids the "storage" error. Public Sub InitQuoteSummaryWindow() On Error Resume Next Dim QS As Excel.Workbook Dim i As Integer Application.ScreenUpdating = False Set QS = Workbooks.Add(QSPath & "QuoteSummary.xls") QS.Sheets("quotes").Activate QS.ActiveSheet.Range("A1").Select frmQuoteSummary.ListBox1.Font.Name = "Arial" frmQuoteSummary.ListBox1.Font.Size = 10 frmQuoteSummary.ListBox1.ColumnCount = 6 'QS.Activate frmQuoteSummary.ListBox1.ColumnHeads = False 'frmQuoteSummary.ListBox1.RowSource = "a1:f" & CStr(i) frmQuoteSummary.ListBox1.MultiSelect = fmMultiSelectSingle frmQuoteSummary.ListBox1.ColumnWidths = "72;108;108;108;96;96" frmQuoteSummary.ListBox1.TextAlign = fmTextAlignLeft frmQuoteSummary.ListBox1.RowSource = 0 i = 0 Do While ActiveCell.Value < "" i = i + 1 With frmQuoteSummary.ListBox1 .AddItem ActiveCell.Value, i .List(i, 2) = ActiveCell.Offset(0, 1).Value .List(i, 3) = ActiveCell.Offset(0, 2).Value .List(i, 4) = ActiveCell.Offset(0, 3).Value .List(i, 5) = ActiveCell.Offset(0, 4).Value .List(i, 6) = ActiveCell.Offset(0, 5).Value End With ActiveCell.Offset(1, 0).Select Loop Application.ScreenUpdating = True QS.Close Set QS = Nothing End Sub |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBAProjects remain in memory after workbook is closed
For your point #1, are you using Google desktop ?
There is known bug in with this and this behaviour in Excel For #2: With frmQuoteSummary.ListBox1. .Font...etc .ColumnCount=6 .List=QS.Worksheets("Quotes").Range("A1:F" & i).Value ....etc End With NickHK "Arif Ali" wrote in message ... I have now narrowed my Excel questions to two, and it seems that one is unrelated to my code: 1. If you open Excel, it normally defaults to openning a template and calling the workbook Book1. If you go to Tools-Macros-Visual Basic Editor, you can see the modules (perhaps empty) associated with Book1. Now ALT-Tab to return to the spreadsheet (leaving the VBA Project open) and click File-Close. When you switch back to the VB Editor, the VBA Project for Book1 is still in memory! Why is that? (I have been pulling my hair out thinking that my code was creating this situation; however it appears that Excel does this with no help from me!) 2. I am using the following code to populate a Listbox in a UserForm. There are 6 columns in this listbox. As you can see the ListBox remains open at the end of the Sub. However, the QS spreadsheet is closed. I believe this is causing a problem since I have defined a rowsource that is in a spreadsheet that has been closed. I need a different way to populate the listbox - probably just need to remember or learn how to populate a listbox with AddItem. However I can not get it to work. THIS code results in the error "Not enough storage to complete operation", if you click on an item in the listbox. Further below (in item 2a) is the rewrite of this routine without rowsource, which also isnt working, Public Sub InitQuoteSummaryWindow() 'Dim MyFont As Font Application.ScreenUpdating = False Set QS = oExcel.Workbooks.Add(QSPath & "QuoteSummary.xls") QS.Sheets("Quotes").Activate QS.ActiveSheet.Range("A1").Select i = 0 Do While ActiveCell.Value < "" i = i + 1 ActiveCell.Offset(1, 0).Select Loop frmQuoteSummary.ListBox1.Font.Name = "Arial" frmQuoteSummary.ListBox1.Font.Size = 10 frmQuoteSummary.ListBox1.ColumnCount = 6 QS.Activate frmQuoteSummary.ListBox1.ColumnHeads = False frmQuoteSummary.ListBox1.RowSource = "a1:f" & CStr(i) frmQuoteSummary.ListBox1.MultiSelect = fmMultiSelectSingle frmQuoteSummary.ListBox1.ColumnWidths = "72;108;108;108;96;96" frmQuoteSummary.ListBox1.TextAlign = fmTextAlignLeft Application.ScreenUpdating = True QS.Close Set QS = Nothing End Sub 2a. Revised COde which does not completely work, but avoids the "storage" error. Public Sub InitQuoteSummaryWindow() On Error Resume Next Dim QS As Excel.Workbook Dim i As Integer Application.ScreenUpdating = False Set QS = Workbooks.Add(QSPath & "QuoteSummary.xls") QS.Sheets("quotes").Activate QS.ActiveSheet.Range("A1").Select frmQuoteSummary.ListBox1.Font.Name = "Arial" frmQuoteSummary.ListBox1.Font.Size = 10 frmQuoteSummary.ListBox1.ColumnCount = 6 'QS.Activate frmQuoteSummary.ListBox1.ColumnHeads = False 'frmQuoteSummary.ListBox1.RowSource = "a1:f" & CStr(i) frmQuoteSummary.ListBox1.MultiSelect = fmMultiSelectSingle frmQuoteSummary.ListBox1.ColumnWidths = "72;108;108;108;96;96" frmQuoteSummary.ListBox1.TextAlign = fmTextAlignLeft frmQuoteSummary.ListBox1.RowSource = 0 i = 0 Do While ActiveCell.Value < "" i = i + 1 With frmQuoteSummary.ListBox1 .AddItem ActiveCell.Value, i .List(i, 2) = ActiveCell.Offset(0, 1).Value .List(i, 3) = ActiveCell.Offset(0, 2).Value .List(i, 4) = ActiveCell.Offset(0, 3).Value .List(i, 5) = ActiveCell.Offset(0, 4).Value .List(i, 6) = ActiveCell.Offset(0, 5).Value End With ActiveCell.Offset(1, 0).Select Loop Application.ScreenUpdating = True QS.Close Set QS = Nothing End Sub |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBAProjects remain in memory after workbook is closed
Nick, thanks for all your input. Yesterday I implemented code you are
referring to (I researched the listbox and found the .List method). The "Not enough storage" error went away - I guess RowSource is a dynamic connection, not useful for a one-time upload of data. I should have realized this as it is logical, but in the heat of the moment I overlooked it. As for the google toolbar, I do have that on that machine. I wonder if this creates performance issues once a number of projects remain in memory? I would think so. Anyway, all's well that ends well. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Projects remain in VBE even after closing workbook | Excel Programming | |||
Closing Hidden Workbook when Active Workbook is Closed | Excel Programming | |||
VBAProjects still visible after workbook has closed | Excel Programming | |||
Closed workbook remains in memory. | Excel Programming | |||
Unprotect VBAProjects using VBA | Excel Programming |