Solved Excel in Task Manager not closing

[silentwolf]

Hi guys,

I am a little in need for some advice please.

Sub UpdateAndSave(strFileName As String)
    Dim appExcel As Excel.Application
    Dim wkbExcel As Excel.Workbook
    Dim wksExcel As Excel.Worksheet
    
    Dim rngExcel As Excel.Range
    
    Set appExcel = HoleAnwendung("Excel.Application")
    
    If appExcel Is Nothing Then
        MsgBox "Kein Excel gefunden!", vbCritical, p_cstrAppTitel
    Else
        appExcel.Visible = True
        Set wkbExcel = appExcel.Workbooks.Open(strFileName, Local:=True)
        Set wksExcel = wkbExcel.Worksheets(1)

        Set rngExcel = Range("A1")
        
        If rngExcel = "IBAN" Then
        
        Dim clsVB As clsVBUpdate
        Set clsVB = New clsVBUpdate
        
        With clsVB
            .FormatSpalteF (strFileName)
            .CreditorUpdate (strFileName)
            .DeleteBlanks (strFileName)
            .ExtraLength (strFileName)
            UpdatedFileName = strFileName
        End With
        
        wksExcel.Name = UpdatedFileName
        End If
      
        Set clsVB = Nothing
    
        Dim strUpdatedFileName As String
        strUpdatedFileName = PfadUpdate & "Auszug_" & wksExcel.Name
        
        Dim objFSO As Object
        Set objFSO = CreateObject("Scripting.filesystemobject")
        
        strUpdatedFileName = strUpdatedFileName & ".xlsx"
        
        'Check if file is saved already
        If objFSO.FileExists(strUpdatedFileName) Then
                    MsgBox "File already saved!", vbCritical, p_cstrAppTitel
            wkbExcel.Close savechanges:=False
            
            Set objFSO = Nothing
            Set wkbExcel = Nothing
            Set wksExcel = Nothing

            appExcel.Quit
            Set appExcel = Nothing

            Exit Sub
        Else
        
        With wkbExcel
            .SaveAs FileName:=strUpdatedFileName, FileFormat:=xlOpenXMLWorkbook
            .Close True
        End With

        End If
      
            Set objFSO = Nothing
            Set wkbExcel = Nothing
            Set wksExcel = Nothing

            appExcel.Quit
            Set appExcel = Nothing
    End If
End Sub

in the above code I check if the file is already saved and if it is then exit the sub routine.

However when that is the case then excel closes alright but in the task manager Excel is still showing what do I do wrong in that above code?

Can`t seam to get it to work.

Could someone give me a hand on that one?

Many thanks in advance

 

[cheekybuddha]

You have an unqualified reference to an Excel object.

Change:

' ...
        Set rngExcel = Range("A1")
' ...

to:

' ...
        Set rngExcel = wksExcel.Range("A1")
' ...

 

[silentwolf]

Hi cheekybuddha,

many thanks!
It seams to work now!!

Fantastic )

 

[silentwolf]

Oh one more thing,

I do get sometimes a runtime Error 462
The remote Server Computer is missing or not accessable or something down that line..

Sorry I use a german version so I am not completly sure how it is how the wording is on that error in english

 

[cheekybuddha]

It's a long-standing bug which MS decided was a 'feature'!

Unqualified references to Excel objects in automation will leave a hanging instance of Excel in memory.

The problem is they can be very difficult to spot!

I have seen people resorting to all sorts of horrible workarounds of blanket killing all Excel processes using things like PSKill etc

 

[cheekybuddha]

I do get sometimes a runtime Error 462
The remote Server Computer is missing or not accessable or something down that line..

I'm not so sure about that one.

Does it happen if Excel is already open?

Sometimes it can happen when the workbook you are automating is already open and a cell is being edited (ie the cursor is in the cell, before you hit tab or return) which effectively locks the workbook until the user completes the edit.

 

[silentwolf]

I'm not so sure about that one.

Does it happen if Excel is already open?

Sometimes it can happen when the workbook you are automating is already open and a cell is being edited (ie the cursor is in the cell, before you hit tab or return) which effectively locks the workbook until the user completes the edit.

Thanks well I am trying to close and reboot for a start and see if it might help.
So I will get back to you as soon I did that and after I did check.

Many thanks in the meantime..

 

[silentwolf]

It seam it does what it wants...
Sometimes it closes Excel Instance in the Task Manager and other times not.
When I manually close the Excel Instance in the Task manager then again not always but sometimes I get the runtime Error.

Question?
Is it not the best Idea to run Excel from Access?
In one case I did run Excel Code within Access with if I remember right with the run command something like run.TheMakroName

or how do you guys do things like that?


Cheers )

 

[cheekybuddha]

Sometimes it closes Excel Instance in the Task Manager and other times not.

There must be another unqualified reference that we've missed.

Is the above your only Excel automation code?

 

[silentwolf]

hmm yes it is
I`ve been looking through the code and there is nothing I can spot...

 

[cheekybuddha]

What is HoleAnwendung() ?

Is it german version of CreateObject() ?

 

[silentwolf]

Private Sub btn_OpenExcel_Click()
   If IsNull(lst_Files.Value) Then
        MsgBox "Bitte eine Datei auswählen", vbCritical, p_cstrAppTitel
        Exit Sub
    Else
        Set objFolder = New clsFolderManager
        
        With objFolder
            .UpdateAndSave objFolder.FilePath
        End With
    End If
    Set objFolder = Nothing
End Sub

'clsFolderManager

Sub UpdateAndSave(strFileName As String)
    Dim appExcel As Excel.Application
    Dim wkbExcel As Excel.Workbook
    Dim wksExcel As Excel.Worksheet
    
    Dim rngExcel As Excel.Range
    
    Set appExcel = HoleAnwendung("Excel.Application")
    
    If appExcel Is Nothing Then
        MsgBox "Kein Excel gefunden!", vbCritical, p_cstrAppTitel
    Else
        appExcel.Visible = False
        Set wkbExcel = appExcel.Workbooks.Open(strFileName, Local:=True)
        Set wksExcel = wkbExcel.Worksheets(1)

        Set rngExcel = wksExcel.Range("A1")
        
        If rngExcel = "IBAN" Then
        
        Dim clsVB As clsVBUpdate
        Set clsVB = New clsVBUpdate
        
        With clsVB
            .FillEmptyCells (strFileName)
            .FormatSpalteF (strFileName)
            .CreditorUpdate (strFileName)
            .DeleteBlanks (strFileName)
            .ExtraLength (strFileName)

            UpdatedFileName = strFileName       'here starts the update of the file
        End With
        
        wksExcel.Name = UpdatedFileName
        End If
      
        Set clsVB = Nothing
    
        Dim strUpdatedFileName As String
        strUpdatedFileName = PfadUpdate & "Auszug_" & wksExcel.Name
        
        Dim objFSO As Object
        Set objFSO = CreateObject("Scripting.filesystemobject")
        
        strUpdatedFileName = strUpdatedFileName & ".xlsx"
        
        'Prüfe ob File existiert
        If objFSO.FileExists(strUpdatedFileName) Then
            MsgBox "File already saved!", vbCritical, p_cstrAppTitel
            wkbExcel.Close savechanges:=False
            
            Set objFSO = Nothing
            Set wkbExcel = Nothing
            Set wksExcel = Nothing
            Set rngExcel = Nothing
            
            appExcel.Quit
            Set appExcel = Nothing
            
            Exit Sub
        Else
        
        With wkbExcel
            .SaveAs FileName:=strUpdatedFileName, FileFormat:=xlOpenXMLWorkbook
            .Close True
        End With

        End If
      
            Set objFSO = Nothing
            Set wkbExcel = Nothing
            Set wksExcel = Nothing
            Set rngExcel = Nothing
            
            appExcel.Quit
            Set appExcel = Nothing
    End If
End Sub

Sub CreditorUpdate(strDatei As String)
    Dim Buchung() As Variant
    Dim i As Integer

    Buchung = Range("J2", Range("J1").End(xlDown))

    For i = LBound(Buchung, 1) To UBound(Buchung, 1)
        Buchung(i, 1) = ReplaceCreditor(Buchung(i, 1))
    Next i

    Range("J2").Resize(UBound(Buchung, 1), 1) = Buchung

End Sub

Sub DeleteBlanks(strDatei As String)
    Dim Buchung() As Variant
    Dim i As Integer

    Buchung = Range("J2", Range("J1").End(xlDown))

    For i = LBound(Buchung, 1) To UBound(Buchung, 1)
        Buchung(i, 1) = KillBlanks(Buchung(i, 1))
    Next i

    Range("J2").Resize(UBound(Buchung, 1), 1) = Buchung

End Sub

Sub ExtraLength(strDatei As String)
    Dim Umsatztext() As Variant
    Dim i As Integer

    Umsatztext = Range("J2", Range("J1").End(xlDown))
    For i = LBound(Umsatztext, 1) To UBound(Umsatztext, 1)
        Umsatztext(i, 1) = AddExtraSpaces(Umsatztext(i, 1))
    Next i
    Range("J2").Resize(UBound(Umsatztext, 1), 1) = Umsatztext

End Sub

Sub FormatSpalteF(strDatei As String)
    Columns("F").NumberFormat = "@"
End Sub

Sub FillEmptyCells(strDatei As String)
    Dim Zeile As Integer
    Dim ZeileMax As Long

    With ActiveSheet
    ZeileMax = .Cells(rows.Count, 1).End(xlUp).Row

        For Zeile = 2 To ZeileMax
            Select Case True '<<<<< Hier wird's wichtig>>>>>>

            Case .Cells(Zeile, 10).Value = ""
            .Cells(Zeile, 10).Value = .Cells(Zeile, 9).Value

            End Select
        Next Zeile
    End With
End Sub

Here are some Code Snippets I use for this Application not sure if that makes things a little clearer what I am trying to do

Sorry if the code above is to long!

 

[silentwolf]

What is HoleAnwendung() ?

Is it german version of CreateObject() ?

Function HoleAnwendung(strName As String) As Object
    On Error Resume Next
    Set HoleAnwendung = GetObject(, strName)
    If HoleAnwendung Is Nothing Then
        Set HoleAnwendung = CreateObject(strName)
    End If
End Function

 

[cheekybuddha]

Just would like to double-check that you have Option Explicit declared at the top of every code module.

Where is PfadUpdate declared?

 

[silentwolf]

Just would like to double-check that you have Option Explicit declared at the top of every code module.

Where is PfadUpdate declared?

Yes of course )
it is in a standard module just a simple function to return a path

 

[cheekybuddha]

Ah! I have just seen that CreditorUpdate(), DeleteBlanks(), ExtraLength(), FormatSpalteF(), FillEmptyCells() all have unqualified references to Excel objects! (Range, Columns, ActiveSheet)

Where is the Excel application object that they use declared?

 

[silentwolf]

those methods are in a seperate class modules and are just as they are declared so no seperate Excel Application is set..
I hope you understand what I mean?

Be right back just need to do something ..)

 

[cheekybuddha]

I hope you understand what I mean?

Yes, but this is the root of the problem.

Pass the appropriate Excel object to each of those methods:

Sub CreditorUpdate(strDatei As String, ws As Excel.Worksheet)
    Dim Buchung() As Variant
    Dim i As Integer

    Buchung = ws.Range("J2", ws.Range("J1").End(xlDown))

    For i = LBound(Buchung, 1) To UBound(Buchung, 1)
        Buchung(i, 1) = ReplaceCreditor(Buchung(i, 1))
    Next i

    ws.Range("J2").Resize(UBound(Buchung, 1), 1) = Buchung

End Sub

Sub DeleteBlanks(strDatei As String, ws As Excel.Worksheet)
    Dim Buchung() As Variant
    Dim i As Integer

    Buchung = ws.Range("J2", ws.Range("J1").End(xlDown))

    For i = LBound(Buchung, 1) To UBound(Buchung, 1)
        Buchung(i, 1) = KillBlanks(Buchung(i, 1))
    Next i

    ws.Range("J2").Resize(UBound(Buchung, 1), 1) = Buchung

End Sub

Sub ExtraLength(strDatei As String, ws As Excel.Worksheet)
    Dim Umsatztext() As Variant
    Dim i As Integer

    Umsatztext = ws.Range("J2", ws.Range("J1").End(xlDown))
    For i = LBound(Umsatztext, 1) To UBound(Umsatztext, 1)
        Umsatztext(i, 1) = AddExtraSpaces(Umsatztext(i, 1))
    Next i
    ws.Range("J2").Resize(UBound(Umsatztext, 1), 1) = Umsatztext

End Sub

Sub FormatSpalteF(strDatei As String, ws As Excel.Worksheet)
    ws.Columns("F").NumberFormat = "@"
End Sub

Sub FillEmptyCells(strDatei As String, ws As Excel.Worksheet)
    Dim Zeile As Integer
    Dim ZeileMax As Long

    With ws
        ZeileMax = .Cells(rows.Count, 1).End(xlUp).Row

        For Zeile = 2 To ZeileMax
            Select Case True '<<<<< Hier wird's wichtig>>>>>>

            Case .Cells(Zeile, 10).Value = ""
            .Cells(Zeile, 10).Value = .Cells(Zeile, 9).Value

            End Select
        Next Zeile
    End With
End Sub

Then, pass the worksheet when you call the subs:

' ...
        With clsVB
            .FillEmptyCells (strFileName)   ' <-- does this method need it too?
            .FormatSpalteF (strFileName, wksExcel)
            .CreditorUpdate (strFileName, wksExcel)
            .DeleteBlanks (strFileName, wksExcel)
            .ExtraLength (strFileName, wksExcel)

            UpdatedFileName = strFileName       'here starts the update of the file
        End With
' ...

Need to find all occurrences of these unqualified references.

 

[silentwolf]

Hi,

thanks for your reply!
Yes the FillEmptyCells also is needet.

Sub FillEmptyCells(strDatei As String)
    Dim Zeile As Integer
    Dim ZeileMax As Long

    With ActiveSheet
    ZeileMax = .Cells(rows.Count, 1).End(xlUp).Row

        For Zeile = 2 To ZeileMax
            Select Case True '<<<<< Hier wird's wichtig>>>>>>

            Case .Cells(Zeile, 10).Value = ""
            .Cells(Zeile, 10).Value = .Cells(Zeile, 9).Value

            End Select
        Next Zeile
    End With
End Sub

again this is in the clsVBUpdate

I not quite understand how to reference it properly as you mentioned.
This is part of my problem as there are many tutorials but once it goes into an application and when I need to put it all together there is still some issues to understand it all.
Sorry about that..

 

[cheekybuddha]

I not quite understand how to reference it properly as you mentioned.

So adjust FillEmptyCells() in the same way:

Sub FillEmptyCells(strDatei As String, ws As Excel.Worksheet)
    Dim Zeile As Integer
    Dim ZeileMax As Long

    With ws.ActiveSheet
    ZeileMax = .Cells(rows.Count, 1).End(xlUp).Row

        For Zeile = 2 To ZeileMax
            Select Case True '<<<<< Hier wird's wichtig>>>>>>

            Case .Cells(Zeile, 10).Value = ""
            .Cells(Zeile, 10).Value = .Cells(Zeile, 9).Value

            End Select
        Next Zeile
    End With
End Sub

and adjust its call too:

' ...
        With clsVB
            .FillEmptyCells (strFileName, wksExcel)
            .FormatSpalteF (strFileName, wksExcel)
' ...