Taskmanager shows Excel after been closed

[silentwolf]

Hi guys,

got following question regarding the taskmanager and an instant of Ms Excel after running codes.

I got a form in Access where I declared following variables

Option Compare Database
Option Explicit

Private mappExcel As Excel.Application
Private mwkbExcel As Excel.Workbook
Private mwksExcel As Excel.Worksheet
Private mrngExcel As Excel.Range
Private mlastRow As Long
'
'

Below the code I am using..

Private Sub btnAction_Click()
    Select Case btnAction.Caption
        Case "Open"
            If MsgBox("Datei öffnen?", vbQuestion + vbYesNo) = vbYes Then
'                OpenExcelFile (Me!txtFilePath.Value)        ' If I use this code the Taskmanager gets cleared
                
                OpenExcelFileM (Me!txtFilePath.Value)      'With This code the Taskmanager keeps an instant of Excel
            Else
                Exit Sub
            End If
            
        Case "Update"
            If MsgBox("Datei aktualisieren?", vbQuestion + vbYesNo) = vbYes Then
                UpdateCSVFile (Me!txtFilePath.Value)
            Else
                Exit Sub
            End If
        
        End Select
        
End Sub

Public Function OpenExcelFile(strFilePath As String) As Boolean
    Dim appExcel As Excel.Application
    Dim myWorkbook As Excel.Workbook
    Dim myWorkSheet As Excel.Worksheet
    
    Set appExcel = HoleAnwendung("Excel.Application")
    
    If appExcel Is Nothing Then
        MsgBox "Kein Excel gefunden!", vbCritical, p_cstrAppTitel
        Exit Function
    Else
        Set myWorkbook = appExcel.Workbooks.Open(strFilePath, Local:=True)
        Set myWorkSheet = myWorkbook.Worksheets(1)
                
        appExcel.Visible = True
    End If

End Function

Public Function OpenExcelFileM(strFilePath As String) As Boolean
  
    Set mappExcel = HoleAnwendung("Excel.Application")
    
    If mappExcel Is Nothing Then
        MsgBox "Kein Excel gefunden!", vbCritical, p_cstrAppTitel
        Exit Function
    Else
        Set mwkbExcel = mappExcel.Workbooks.Open(strFilePath, Local:=True)
        Set mwksExcel = mwkbExcel.Worksheets(1)
                
        mappExcel.Visible = True
    End If

End Function

As mentioned in bold above the code with OpenExcelFileM keeps Excel running in the Taskmanager the OpenExcelFile does clean it out

So I was wondering if someone could tell me what I would need to change in order to be able to use the globel varibles in the form module

Many thanks for your help

Albert

 

[Micron]

Not clear what you want or what the issue is. You want to use OpenExcelFile procedure but you want to use the module level variables? Then replace those variable names with the ones you have in the declarations section at the top.

You must be altering the button caption somewhere else?

 

[silentwolf]

Not clear what you want or what the issue is. You want to use OpenExcelFile procedure but you want to use the module level variables? Then replace those variable names with the ones you have in the declarations section at the top.

You must be altering the button caption somewhere else?

Hi thanks for your reply!,

yes I do like to use the varibles in the module level.
Also what I am trying to achieve is somthing like following.

I open an CSV File in "Excel" as Standart. But to make sure it is a file I need if I am not certain I like to be able to open it to view that file.
However if I am using the code which is in the procedure level and I close the workbook manually then the instance in the task manager disapears right after closing that workbook.
If I am using the module level varibles and I close also the file the instance stays open.

I will try to figure it out as you mentioned but tried already without any luck.
Also is there a way of set those varibles to nothing when I close the workbook manually?

Maybe there is after all a better way of doing this?

Cheers

Albert

 

[welshgasman]

The local variables will go out of scope once you leave that function, the public ones will not, that is the idea

Set them to Nothing when you do not need hem anymore.

You will also get an Excel instance if the code crashes before closing Excel.

 

[silentwolf]

The local variables will go out of scope once you leave that function, the public ones will not, that is the idea

Set them to Nothing when you do not need hem anymore.

You will also get an Excel instance if the code crashes before closing Excel.

Hi Welshgasman,

hmm so it is better to use that code which goes out of scope I guess. There is no way of setting those to nothing is there if you close the workbook and like to use those module declared varibles ist there?

You will also get an Excel instance if the code crashes before closing Excel.

So is there a way of preventing this kind of problem? Or closing a instance from within access or check whether one is still open?

Cheers

 

[welshgasman]

So is there a way of preventing this kind of problem? Or closing a instance from within access or check whether one is still open?

Not that I know of. That crashed instance becomes disconnected from the Excel app I believe.

 

[silentwolf]

Not that I know of. That crashed instance becomes disconnected from the Excel app I believe.

Ah ok thanks welshgasman )
so I will try to use the local varibles instead and than it should do the job at least for the first part I am working on.

Many thanks for your input!

Cheers

 

[welshgasman]

Well you could always set the public variables to Nothing yourself?

 

[silentwolf]

Well you could always set the public variables to Nothing yourself?

Well if I close the excel workbook by myself so to speak then I am not sure how I could set it to nothing?

For example:
If I am not sure if that file is the one I need to update or saveas or any other work that need to be done. I open the file just to be able to view it and to make sure it is correct.

So therefore at the moment I just close it manually in Excel.

What I like to achive is if that file is correct or need to be worked on than I saveit to new location with new filename and update that new file, the old one I like to keep as reference in the old folder.

Maybe there would be a better way of doing it but I just can't seam to think how the correct workflow could be?

 

[welshgasman]

Well if I close the excel workbook by myself so to speak then I am not sure how I could set it to nothing? For example: If I am not sure if that file is the one I need to update or saveas or any other work that need to be done. I open the file just to be able to view it and to make sure it is correct. So therefore at the moment I just close it manually in Excel. What I like to achive is if that file is correct or need to be worked on than I saveit to new location with new filename and update that new file, the old one I like to keep as reference in the old folder. Maybe there would be a better way of doing it but I just can't seam to think how the correct workflow could be?

Well I see you have continued this thread over on AWF, so I will leave this as it is.