Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    1,294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel VBA modules still open (ExcelXP SP3)

    I have excel code which does the following.
    in a main spreadsheet> search for a record > extract the record
    create a new workbook> paste the record data > format the cells > save the new workbook in a location with a meaningful name.> close the workbook
    repeat for next record.

    my issue is as im stepping thru the code i noticed that for each new workbook ive created - the VBA project module still appears even when I close that excel workbook. (see below pic)

    any ideas on why this is happening and how to clear it.
    my concern is there are many records to extract - with all those workbooks 'open in the background' is that going to consume memory in Excel & then eventuallly my Excel will crash?

    many thanks
    Diana

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel VBA modules still open (ExcelXP SP3)

    It looks like you are not closing the workbooks...

    Could you provide the code so we could make suggestions. Without seeing the code it is not clear what is happening.

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    1,294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA modules still open (ExcelXP SP3)

    Very long code segment moved to attachment by HansV (also deleted duplicate post)

    steve

    i was going to attach the excel file but its nearly 2MG [img]/forums/images/smilies/ohmy.gif[/img])

    heres the code....

    cheers diana

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA modules still open (ExcelXP SP3)

    I suspect that the active workbook changes during execution of the code. Try changing MoveSaveResults to this:

    Sub MoveSaveResults()
    Dim wbk As Workbook
    Application.Goto Reference:="Results"
    Selection.CurrentRegion.Copy
    Set wbk = Workbooks.Add

    wbk.ActiveSheet.Paste
    wbk.Sheets("Sheet1").Name = Filtername

    Application.DisplayAlerts = False
    wbk.Sheets("Sheet2").Delete
    wbk.Sheets("Sheet3").Delete
    Application.DisplayAlerts = True

    Application.CutCopyMode = False

    FormatResults

    wbk.SaveAs Filename:= _
    "Cocuments and SettingssnortonMy Documentsresults" & Filtername
    wbk.Close
    Set wbk = Nothing
    End Sub

    This version sets a Workbook variable and closes it at the end.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel VBA modules still open (ExcelXP SP3)

    You could also change the code to read:
    <code>Set wbk = Workbooks.Add(xlWBATWorksheet)</code>
    so there is only one sheet in the new workbook and the code to delete sheets 2 and 3 is then unnecessary.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel VBA modules still open (ExcelXP SP3)

    WOW --- Thanx for that tip Rory!! I never new you could do that. I read up in the help that this argument determines how a Workbook is created! This is useful!
    Cheers
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •