Excel stays open in Task Manager after closing (VBA)

[exceldemon]

I have a VBA script from a 3D modeling program I use that opens a Excel Workbook and adds text to it. Everything works just fine but when I close the workbook, Excel still shows up in the Task Manager. If I run my macro again, it doesnt work. If I end the task in the Task Manager, it works again.


Here is the code I use to open the existing Worksheet that hads a header, then imports text.

Private Function convertexcel()
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet

Set xlApp = New Excel.Application
xlApp.Visible = False
xlApp.DisplayAlerts = False
    ChDir "V:\MyFolder"
    Workbooks.Open FileName:= _
        "V:\MyFolder\ENGR STANDARD.xlsx" _
        , UpdateLinks:=0, Notify:=False
    Range("A12:M15").Select
    Selection.Delete Shift:=xlUp
    Range("D19").Select

With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\tempbom.txt", _
        Destination:=Range("$A$12"))
        .Name = "tempbom"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlFormatFromLeftOrAbove
        .SavePassword = False
        .SaveData = True
        '.AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Rows("12:12").Select
    Selection.Delete Shift:=xlUp
'    Columns("C:C").Select
'    With Selection
'        .HorizontalAlignment = xlLeft
'    End With

    Range("D1").Select

xlApp.Visible = True
End Function

 

[VoG]

Perhaps like this

    Range("D1").Select

xlApp.Visible = True
xlApp.Quit
End Function

 

[exceldemon]

Perhaps like this

    Range("D1").Select

xlApp.Visible = True
xlApp.Quit
End Function

That exits Excel. I dont want to exit Excel with the macro. The user needs to work on the Worksheet and save it to the desired location.

 

[Juan Pablo González]

The actual problem is that you have unqualified object references in your code, so you need to change stuff like

ActiveWorkbook

to

xlApp.ActiveWorkbook

or

Range

with

xlApp.Range

if you don't, then the code will create another Excel instance, which will stay open in the task manager. You need to qualify ALL objects like that.

 

[exceldemon]

The actual problem is that you have unqualified object references in your code, so you need to change stuff like

ActiveWorkbook

to

xlApp.ActiveWorkbook

or

Range

with

xlApp.Range

if you don't, then the code will create another Excel instance, which will stay open in the task manager. You need to qualify ALL objects like that.

Ok, I added xlApp. before each object. Is there a way not have to add that before each line? Rather than typing xlApp. before everything?

This works.

Private Function convertexcel()
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet

Set xlApp = New Excel.Application
xlApp.Visible = False
xlApp.DisplayAlerts = False
ChDir "V:\MyFolder"
xlApp.Workbooks.Open FileName:= _
"V:\MyFolder\ENGR STANDARD.xlsx" _
, UpdateLinks:=0, Notify:=False
xlApp.Range("A12:M15").Select
xlApp.Selection.Delete Shift:=xlUp
xlApp.Range("D19").Select

With xlApp.ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\tempbom.txt", _
Destination:=xlApp.Range("$A$12"))
.Name = "tempbom"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlFormatFromLeftOrAbove
.SavePassword = False
.SaveData = True
'.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
xlApp.Rows("12:12").Select
xlApp.Selection.Delete Shift:=xlUp
' Columns("C:C").Select
' With Selection
' .HorizontalAlignment = xlLeft
' End With

xlApp.Range("D1").Select

xlApp.Visible = True
End Function

 

[Juan Pablo González]

You can use With

With xlApp
.Range("A4").Value = 1
End With

 

[exceldemon]

You can use With

With xlApp
.Range("A4").Value = 1
End With

That will work.

Thanks