Solved Excel in Task Manager not closing (1 Viewer)

[cheekybuddha]

If there are a lot more methods like these in clsVBUpdate that also require adjustment, then you may want to make the worksheet a property of the class which you set when you instantiate it, and then you can refer to it without having to pass the object to each individual method of the class.

 

[cheekybuddha]

Where is strDatei ever used in any of those methods?

Why are you passing the filename?

 

[silentwolf]

Hi,

I tried but for some reason I get an syntax Error on that line

        With clsVB
            .FillEmptyCells (strFileName)
            .FormatSpalteF (strFileName)
            .CreditorUpdate (strFileName, wksExcel) 'Syntax Error'
            .DeleteBlanks (strFileName)
            .ExtraLength (strFileName)

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

do I declare it wrong?

 

[cheekybuddha]

Can you post the whole of clsVBUpdate

 

[silentwolf]

Option Compare Database
Option Explicit

Sub CreditorUpdate(strDatei As String, ws As Excel.Worksheet)
    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

    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 = Range("J2", 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 = Range("J2", 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)
    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

 

[cheekybuddha]

You haven't changed all the references!
Buchung = Range("J2", Range("J1").End(xlDown))
Umsatztext = Range("J2", Range("J1").End(xlDown))
With ActiveSheet

But that is not the cause of your error!

The error is because the methods are subs and not functions.

You can only use parentheses around a sub's parameter if there is only a single parameter. Sorry!

' ...
        With clsVB
            .FillEmptyCells wksExcel
            .FormatSpalteF wksExcel
            .CreditorUpdate wksExcel
            .DeleteBlanks wksExcel
            .ExtraLength wksExcel

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

Adjust your methods in clsVBUpdate like this (we remove the unused parameter strDatei as well):

Option Compare Database
Option Explicit

Sub CreditorUpdate(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(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(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(ws As Excel.Worksheet)
    ws.Columns("F").NumberFormat = "@"
End Sub

Sub FillEmptyCells(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

 

[silentwolf]

You haven't changed all the references!
Buchung = Range("J2", Range("J1").End(xlDown))
Umsatztext = Range("J2", Range("J1").End(xlDown))
With ActiveSheet

But that is not the cause of your error!

The error is because the methods are subs and not functions.

You can only use parentheses around a sub's parameter if there is only a single parameter. Sorry!

' ...
        With clsVB
            .FillEmptyCells wksExcel
            .FormatSpalteF wksExcel
            .CreditorUpdate wksExcel
            .DeleteBlanks wksExcel
            .ExtraLength wksExcel

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

Adjust your methods in clsVBUpdate like this (we remove the unused parameter strDatei as well):

Option Compare Database
Option Explicit

Sub CreditorUpdate(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(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(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(ws As Excel.Worksheet)
    ws.Columns("F").NumberFormat = "@"
End Sub

Sub FillEmptyCells(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

Hi,
sorry I did not get back to you earlier on this!

I changed it many thanks and learned alot again!

Many thanks for your help!

Cheers!

 

[cheekybuddha]

So have you got it all working now?

I'm not sure of the benefit of using a class here, but if it's working then no harm!

 

[silentwolf]

So have you got it all working now?

I'm not sure of the benefit of using a class here, but if it's working then no harm!

Hi,
well I still got it in the task manager showing but it is not really doing any harm ..
I guess somehow the quit function is not really working ...

A friend of mine who is a programmer had also a look at it and could not find why it is really a problem as we changed all as you mentioned.

But I guess I will need to work with that..

Cheers for your help!!!

 

[cheekybuddha]

I still got it in the task manager showing

Then there is still an unqualified reference somewhere in your code. That is the 99.999% reason why you have left over Excel instances in memory.

Post your db if you want (but if you use a newer version of Access maybe save it as a mdb first, otherwise I might not be able to open it with Acc2K7)

 

[silentwolf]

Hi guys,

sorry been not here for a long time but I have been able to work it out!

Many thanks to all!!

Happy new year )

 

[Gasman]

Hi guys,

sorry been not here for a long time but I have been able to work it out!

Many thanks to all!!

Happy new year )

So please say how you fixed it. It might help someone else. After all, this is what these sites are for?

 

[silentwolf]

Hi,

So please say how you fixed it. It might help someone else. After all, this is what these sites are for?

It is as or was just as cheekybudda said a reference still in the code..
Also it stayed open in the task manager for some short time after before it was eliminated in there.

I do understand that you mentioned to explain what made it work but in this case was just looking through the code and give the
taskmanager a little time to I guess refresh.

Cheers again to all for your help and putting me in the right directions!