Solved Excel in Task Manager not closing

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.
 
Where is strDatei ever used in any of those methods?

Why are you passing the filename?
 
Hi,

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

Code: 
        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?
 
Code: 
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
 
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!

Code: 
' ...
        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):
Code: 
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
 
cheekybuddha said:
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!

Code: 
' ...
        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):
Code: 
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
Click to expand...
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!
 
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!
 
cheekybuddha said:
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!
Click to expand...
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!!!
 
silentwolf said:
I still got it in the task manager showing
Click to expand...
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)
 
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 )
 
silentwolf said:
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 )
Click to expand...
So please say how you fixed it. It might help someone else. After all, this is what these sites are for?
 
Hi,
So please say how you fixed it. It might help someone else. After all, this is what these sites are for?
Click to expand...
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!
 
You must log in or register to reply here.

Similar threads

N
Solved Form Record Cloning In MS Access
Replies
8
Views
360
Ken Sheridan
Ken Sheridan
N
How to ensure that the current stock control is update when the data appears on the subform in access
Replies
5
Views
714
Minty
Minty
A
Automation Error
Replies
10
Views
290
The_Doc_Man
The_Doc_Man
C
Access instance remains in memory once closed
2 3
Replies
57
Views
4,413
CJ_London
C
Gasman
Solved Open Excel workbook, but have WorkBook_Open code run
Replies
19
Views
1,514
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom