VBA Explorer keeps showing closed workbooks macros

[siduri]

As the title suggests, after closing another workbook that runs a macro it keeps showing up in the VBA Explorer.
Every time I run the macro a new instance of the closed workbook is showing up in the VBA Explorer.


What the macro is doing:
*Open a template workbook
*Copies data to it from the current workbook
*Saves it under a different name


All the above is enclosed in a loop, since I need to be able to do this for a few dozen entries.


I have tried clearing the objects from memory but apparently I'm still doing something wrong.


Thank in advance!


Macro code:

Sub Openworkbook_Click()
'Updateby Extendoffice 20161008
    Dim sWb As Workbook
    Dim dWb As Workbook
    Dim wbName As String
    Dim newName As String
    Dim relPath As String
    Dim i As Integer
    On Error Resume Next
    
    Set sWb = ActiveWorkbook
    
    'While loop
    i = 3
    Do While sWb.Sheets(1).Range("B" & i) <> ""
    'Set destination workbook
        Set dWb = Workbooks.Open("D:\1. WORK\AUDA\in progress\Betonvæg_test.xlsm")
        
        'Geometry copy
        sWb.Sheets(1).Range("B" & i).Copy
        dWb.Sheets(1).Range("K13").PasteSpecial
        sWb.Sheets(1).Range("C" & i).Copy
        dWb.Sheets(1).Range("K14").PasteSpecial
        sWb.Sheets(1).Range("D" & i).Copy
        dWb.Sheets(1).Range("K15").PasteSpecial
        
        'Reinforcement copy
        sWb.Sheets(1).Range("G" & i).Copy
        dWb.Sheets(1).Range("J19").PasteSpecial
        sWb.Sheets(1).Range("H" & i).Copy
        dWb.Sheets(1).Range("K19").PasteSpecial
        
        sWb.Sheets(1).Range("I" & i).Copy
        dWb.Sheets(1).Range("J20").PasteSpecial
        sWb.Sheets(1).Range("J" & i).Copy
        dWb.Sheets(1).Range("K20").PasteSpecial
        
        sWb.Sheets(1).Range("K" & i).Copy
        dWb.Sheets(1).Range("J21").PasteSpecial
        sWb.Sheets(1).Range("L" & i).Copy
        dWb.Sheets(1).Range("K21").PasteSpecial
        
        sWb.Sheets(1).Range("M" & i).Copy
        dWb.Sheets(1).Range("J22").PasteSpecial
        sWb.Sheets(1).Range("N" & i).Copy
        dWb.Sheets(1).Range("K22").PasteSpecial
        
        'Material properties
        sWb.Sheets(1).Range("E" & i).Copy
        dWb.Sheets(1).Range("E17").PasteSpecial
        sWb.Sheets(1).Range("F" & i).Copy
        dWb.Sheets(1).Range("E18").PasteSpecial
        
        'Other
        sWb.Sheets(1).Range("O" & i).Copy
        dWb.Sheets(1).Range("E12").PasteSpecial
        sWb.Sheets(1).Range("P" & i).Copy
        dWb.Sheets(1).Range("E13").PasteSpecial
        sWb.Sheets(1).Range("Q" & i).Copy
        dWb.Sheets(1).Range("E14").PasteSpecial
        sWb.Sheets(1).Range("R" & i).Copy
        dWb.Sheets(1).Range("E15").PasteSpecial
        
        'Copy loads
        sWb.Sheets(1).Range("S" & i).Copy
        dWb.Sheets(1).Range("F33").PasteSpecial
        sWb.Sheets(1).Range("T" & i).Copy
        dWb.Sheets(1).Range("G33").PasteSpecial
        
        sWb.Sheets(1).Range("U" & i).Copy
        dWb.Sheets(1).Range("F34").PasteSpecial
        sWb.Sheets(1).Range("V" & i).Copy
        dWb.Sheets(1).Range("G34").PasteSpecial
        
        sWb.Sheets(1).Range("W" & i).Copy
        dWb.Sheets(1).Range("G35").PasteSpecial
        sWb.Sheets(1).Range("X" & i).Copy
        dWb.Sheets(1).Range("F35").PasteSpecial
        
        'Save with different name & close
        newName = "Betonvæg_" & sWb.Sheets(1).Range("C" & i) & "x" & sWb.Sheets(1).Range("D" & i) & ".xlsm"
        relPath = ThisWorkbook.Path & ""
        
        Application.DisplayAlerts = False
        dWb.SaveAs Filename:=relPath & newName
        Application.DisplayAlerts = True
        Workbooks(newName).Close SaveChanges:=True
        
        'Clear destination object
        Set dWb = Nothing
        
        'Increment i to read next line
        i = i + 1
         
    Loop
    
End Sub

 

[ParamRay]

Hello, I recall this once happened to me a few months ago. I had a macro that used a loop to create, save, and then close several workbooks. Once the macro finished running, all the workbooks were still visible in the VBA project explorer, despite them being closed in Excel. I believe it was because of a glitch caused by a COM add-in. Try un-checking all your COM add-ins, and see if the problem goes away. (You can do this via File --> Options --> Add-ins --> Manage: COM Add-ins.) Not sure if there's a more permanent solution, or whether perhaps the problem will disappear once you re-check the add-ins again.

 

[siduri]

Hi,

Thanks for the answer ParamRay.
I un-checked all COM Add-ins and that helped improve the speed of the process.

What I noticed is that when I run the macro via a button and then open the VBA Explorer the extra workbook is not showing up anymore. However when I run the macro from the VBA Explorer or even have the Explorer opend while running the macro via the button, the closed workbooks always show up.
I think I'll have to just proceed like this for now.