38 Instances/Processes of EXCEL Running!??!?!?!?

[mrlemmer11]

Hi all,

So, im working in WORD VBA and I have the following code to call my excel document for information. As I am doing multiple edits/testing to my WORD VBA UserForms, I am constantly starting and stopping my UF often time with code errors. After a couple hours I wanted to edit the source Excel file, but it said it was locked by another user (me) and I had to wait. I looked in task manager and saw EXCEL listed 38 times..... so I am apparently not closing the call to it correctly. Can anyone help? Thanks!

Private Sub Userform_Initialize()
Dim sourcedoc
Dim oExcel As Excel.ApplicationDim oWB As Workbook
Set oExcel = New Excel.Application
D


Application.ScreenUpdating = False
Set sourcedoc = oExcel.Workbooks.Open(FileName:="C:\users\michael\Desktop\SongIndex\BASlist.xlsx")


arBand = Range("L2:L9")
arAlbum = Range("I2:J245")
arSong = Range("E2:G298")


sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub

 

[GCExcel]

Hello,
Not sure if it will help, but maybe try by adding this line of code at the end.

Set oExcel = Nothing

 

[Leith Ross]

Hello mrlemmer11,

A better approach is to create a single instance of the application and the quit the application when you macro is finished. You can achieve this by assigning the application object to a global variable,

Global oExcel As Object

Private Sub Userform_Initialize()

    Dim sourcedoc   As Object
    Dim oWB         As Workbook
    
        If oExcel Is Nothing Then
            Set oExcel = CreateObject("Excel.Application")
        End If
        
        Application.ScreenUpdating = False
        Set sourcedoc = oExcel.Workbooks.Open(Filename:="C:\users\michael\Desktop\SongIndex\BASlist.xlsx")

        arBand = Range("L2:L9")
        arAlbum = Range("I2:J245")
        arSong = Range("E2:G298")

        sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
        
        oExcel.Quit
        
End Sub