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,

I would have to guess that you are using some object(s) that are not fully
qualified and/or released before attempting to close the file
But without any idea of your code, it's hard to tell.

NickHK

"Arif Ali" <Arif Ali@discussions.microsoft.com> wrote in message
news:F74D2325-B7AF-4D3F-8100-D6F407E5A818@microsoft.com...
> 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,

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?

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,

"halimnurikhwan@yahoo.com" 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?
>
>

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 Before:=ThisWorkbook.NewBook.Sheets("Sheet1")

ThisWorkbook.NewBook.Activate

fname =
ThisWorkbook.oExcel.GetSaveAsFilename(InitialFileName:=(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!!!!

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" <ArifAli@discussions.microsoft.com> wrote in message
news:FF77E4EE-F9AA-494E-B8D5-0FDCDC7B2876@microsoft.com...
> 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
Before:=ThisWorkbook.NewBook.Sheets("Sheet1")
>
> ThisWorkbook.NewBook.Activate
>
> fname =
> ThisWorkbook.oExcel.GetSaveAsFilename(InitialFileName:=(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!!!!
>

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?

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

- 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" <ArifAli@discussions.microsoft.com> wrote in message
news:7A7C7678-EF34-45FA-882B-BD96AD9631DC@microsoft.com...
> 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?

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" <ArifAli@discussions.microsoft.com> wrote in message
> news:7A7C7678-EF34-45FA-882B-BD96AD9631DC@microsoft.com...
> > 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?
>
>
>

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" <ArifAli@discussions.microsoft.com> wrote in message
news:442706AA-F4AC-4A2B-9452-0C8322682BBF@microsoft.com...
> 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" <ArifAli@discussions.microsoft.com> wrote in message
> > news:7A7C7678-EF34-45FA-882B-BD96AD9631DC@microsoft.com...
> > > 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?
> >
> >
> >

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

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" <ArifAli@discussions.microsoft.com> wrote in message
news:442706AA-F4AC-4A2B-9452-0C8322682BBF@microsoft.com...
> 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:=(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
.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

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,

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

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

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" <ArifAli@discussions.microsoft.com> wrote in message
news:5300EDAF-04F7-41CA-8F30-F887164DFFEB@microsoft.com...
> 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
>

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.

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.