Excel already open

[Jean-Claude]

Hi,

I run a VB6 programm that open Excel.xlsm

I close the VB6 programm

Excel.xlsm is always open

I run a VB6 programm

How can I work on the already ope n Excel.xlsm and not open an other Excel.xlsm?

My OS = Windows 10

Thanks in advance for your help

Jean-Claude

[ddanbe]

Save and Close Excell before you close you're VB6?

[rproffitt]

Are you sure you are on VB6? By using such an old system on W10 I worry you have found a new issue.

That aside, be sure to look in Task Manager that the VB6 app is closed. Also, it's also known that a lock on a file can exceed the lifetime of the app. Also, the app could be mucking up file permissions. If you are a seasoned VB6 developer I need not write more.

[Reverend Jim]

Beware of the double-dot problem. This occurs when using an Excel object from within another application. Instantiate intermediate objects before you use them. For example, use a local variable for the sheet object then operate on stuff in the sheet using that object. If you don't then multiple copies of Excel will be left in memory. This is also known as the one-dot rule.

[palandri]

First, google “excel automation” for more info on this subject.

I’ve done a bit of Excel automation from MS Access using VBA, which is close enough to VB6. It’s easy to leave an instance of the Excel application open if you create it from multiple places in your code. If you lose track of one and don’t destroy it before your VB program exits, you’ll leave the Excel application running after your program exits. Worse, depending on how you created the Excel application object, it may not be visible on the desktop, and the only way to see it (and kill it) is from within task manager.

BTW, this was done with Windows 10, and Office 2010 and later 2016, so I don’t think Windows 10 is a problem.

For me, the cleanest way was to create the Excel application object once, in my case I chose to do that in a form that launched some Excel workbook creation processes. Once you have created the Excel application object, you can pass it around to your VB classes as a property, and use it to manipulate workbooks and worksheets. When finished, destroy the Excel application object when the form closes.

Here’s an example of a button click Sub that creates an Excel Application object, does some Excel automation, and destroys the object.

Private Sub cmdCreate_Click()
    Const PROC_SIG = "Sub_cmdCreate_Click"

    Dim rstBudget                       As ADODB.Recordset
    Dim moBudgetMaster                  As clsBudgetMaster
    Dim strMsg                          As String
    Dim lngRecCnt                       As Long

    On Error GoTo Errorh

    ' Make sure that there are records in the source table.  Probably should check for some number > 0 but this'll do for now.
    Set rstBudget = New ADODB.Recordset
    rstBudget.Open "tblBudgetData", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    If rstBudget.RecordCount = 0 Then
        MsgBox "No budget data has been imported.  Please use the Import Budget Data button to import data before using this function.", vbExclamation, gstrAppName
        GoTo ExitHere
    End If

    ' Make sure we're not already processing
    If gblnProcessing Then
        MsgBox "This process is already executing.", vbExclamation, gstrAppName
        GoTo ExitHere
    End If

    ' Make sure user has selected an output folder for workbook creation
    If Len(Trim(mstrFolderName)) < 1 Then
        MsgBox "Please select a workbook output folder.", vbExclamation, gstrAppName
        GoTo ExitHere
    End If

    ' Show the hourglass and current status
    Screen.MousePointer = vbHourglass
    SysCmd acSysCmdSetStatus, "Preparing to create workbooks.."

    ' Initialize an Excel application object
    ' This is for production, it opens Excel with no interface.
    Set mxlApplication = CreateObject("Excel.Application")

    ' This is for debugging, to see Excel in action. This will barf unless Excel is open on the desktop
    ' Didn't really work anyway.
    'Set mxlApplication = GetObject(, "Excel.Application") 'Bind to existing instance of Excel

    mxlApplication.Visible = False

    ' Instantiate a budget master object
    Set moBudgetMaster = New clsBudgetMaster

    ' Set some properties before launching the creation process
    Set moBudgetMaster.ExcelApplication = mxlApplication

    moBudgetMaster.DestFolder = mstrFolderName

    moBudgetMaster.BudgetYear = Me.lstBudgetYear

    ' This is a essentially batch process, even though a user is running it from an Access form
    ' Lets turn of error messages so they don't pop up in Access.  They will still b e logged in
    ' log file and the database error table.
    moBudgetMaster.ShowErrorMsgs = False

    ' Start the budget workbook creation process
    mxlApplication.Visible = False
    mxlApplication.ScreenUpdating = False

    ' For testing, so we can see the updates on the worksheet happen when stepping through code
    'mxlApplication.Visible = True
    'mxlApplication.ScreenUpdating = True

    Call moBudgetMaster.CreateWorkbooks
    mxlApplication.ScreenUpdating = False
    mxlApplication.Visible = False

    Screen.MousePointer = vbDefault
    If gintcc = ccObjectIsBusy Then
        strMsg = "The workbook creation process failed.  The likely cause is that one of the " & _
                "workbooks it was trying to create already exists, and was open in Excel.  " & _
                vbCr & vbLf & vbCr & vbLf & _
                "Please make sure no one has any of the workbooks in the Workbook outpug folder " & _
                "open.  The easiest way to do that is to delete all workbooks from previous runs " & _
                " from that folder.  If you can't delete one, it's open by a user." & _
                vbCr & vbLf & vbCr & vbLf & _
                "More detailed error information can be found in the run log, which is located in this folder " & _
                currentdbpath() & "txt"
        MsgBox strMsg, vbCritical, "Error"
    Else
        If gintcc <> ccOK Then
            SysCmd acSysCmdSetStatus, "The workbook creation process terminated due to a run time error."

            strMsg = "The workbook creation process failed." & _
                    vbCr & vbLf & vbCr & vbLf & _
                    "More detailed error information can be found in the run log, which is located in this folder " & _
                    currentdbpath() & "txt"
            MsgBox strMsg, vbCritical, "Error"
        Else
            mblnExportComplete = True
            SysCmd acSysCmdSetStatus, "Workbook creation complete."
            MsgBox "Workbook creation complete.", vbOKOnly + vbInformation, "Create Workbooks"
        End If
    End If

ExitHere:
    ' Clean up objects created herein
    If Not (rstBudget Is Nothing) Then
        If (rstBudget.State And adStateOpen) = adStateOpen Then rstBudget.Close
        Set rstBudget = Nothing
    End If

    Set moBudgetMaster = Nothing
    If Not mxlApplication Is Nothing Then
        mxlApplication.Quit
    End If
    Set mxlApplication = Nothing

    Exit Sub
Errorh:
    Call ErrorHandler(MODULE_NAME, PROC_SIG, gstrAppName)
    gintcc = ccError
    Resume ExitHere
End Sub

[Neena_3]

I believe, if you close Excel.xlsm file, you need to release memory space.

Please Try

Do Until _
System.Runtime.InteropServices.Marshal.ReleaseComObject(Reference) <= 0
Loop
Catch ex As Exception