VBA error - The object invoked has disconnected from it's clients

[blothian]

As posted in my other recent thread, I'm working on a macro which is being run from a non-office application. Via a form, it will allow the user to browse and select an xlsx file which is then opened without being visible. A list box on the form then displays the worksheet names (a max of 3 will be displayed) and when the user click's on any of those, values of specific cells from the worksheet selected in the first listbox1 will be displayed in listbox2.

Now because this macro isnt hosted in the Excel IDE I have referenced the MicroSoft Excel 16.0 Object Library. I have also made the following declarations:

Public sWorkbookFullName                     As String
Public oXlApp                                As excel.Application
Public oXlWkBk                               As excel.Workbook
Public oXlWkSht                              As excel.Worksheet
Public ShtMain                               As excel.Worksheet
Public ShtRelief                             As excel.Worksheet
Public Shtother                              As excel.Worksheet

In the first listbox click event I have the following code:

Private Sub LB_TrackName_Click()
    Set ShtMain = oXlWkBk.Sheets("Main")
    Set ShtRelief = oXlWkBk.Sheets("Relief")


    FrmPWayLevel.LB_TrackDirection.Clear
    If FrmPWayLevel.LB_TrackName.Value = "Main" Then
        FrmPWayLevel.LB_TrackDirection.AddItem ShtMain.Range("B1").Value
        FrmPWayLevel.LB_TrackDirection.AddItem ShtMain.Range("J1").Value
        ReadValues
    ElseIf LB_TrackName.Text = "Relief" Then
        FrmPWayLevel.LB_TrackDirection.AddItem ShtRelief.Range("B1").Value
        FrmPWayLevel.LB_TrackDirection.AddItem ShtRelief.Range("J1").Value
    Else
        FrmPWayLevel.LB_TrackDirection.AddItem "other"
    End If


End Sub

ReadValues is a function which I intend find the last row with data in Column A on the worksheet that is selected in the first listbox (LB_TrackName). Originally the first 2 lines of the LB_TrackName_Click event procedure read:

Set ShtMain = Sheets("Main")
Set ShtRelief = Sheets("Relief")

This however gave me the error Method of 'Sheets' of object '_Global' so I added the oXlWkBk prefix. Not sure if I needed to but it made sense to add it giving that sheets isnt part of the parent IDE object library and I think its made that error go away but now I have the error listed in the thread title.

Any idea what I need to do to proceed?

 

[V_Malkoti]

If these two sheets are in the workbook in which the macro code is stored, then you can qualify their reference with keyword ThisWorkbook.

Set ShtMain = ThisWorkbook.Sheets("Main")
Set ShtRelief = ThisWorkbook.Sheets("Relief")

 

[blothian]

If these two sheets are in the workbook in which the macro code is stored, then you can qualify their reference with keyword ThisWorkbook.

Set ShtMain = ThisWorkbook.Sheets("Main")
Set ShtRelief = ThisWorkbook.Sheets("Relief")

Unfortunately, the macro code resides in another application's IDE and that addition still gives the Method of 'Sheets' of object '_Global' error. The workbook is an XLSX file not an XLSM file with macro code and is being opened remotely:

Public Sub FindSheets(ByVal file_name As String)

    On Error GoTo err_FindSheets


    If IsExcelRunning = False Then
        Set oXlApp = CreateObject("Excel.Application")
    End If


    Set oXlWkBk = Workbooks.Open(sWorkbookFullName)
    For Each oXlWkSht In oXlApp.Worksheets
        FrmPWayLevel.LB_TrackName.AddItem oXlWkSht.Name
    Next


        oXlWkBk.Close
        oXlApp.Quit
        Set oXlApp = Nothing
    Exit Sub

 

[V_Malkoti]

The object reference oXlWkBk must still be valid to be used:
1. Excel application object assigned to it
2. Workbook is still open.

If this application allows debugging, then can you set a breakpoint in Trackname_Click and see if the reference is still valid? Try getting the name of the workbook to test it.

 

[V_Malkoti]

I just noticed your edit about FindSheets subroutine. In this subroutine you are opening the workbook and then close it at the end. So if you call the TrackName_Click after it, the workbook and excel application are already closed, which is why you get the error about sheets method failing and client being disconnected.

 

[blothian]

Yeah I realised that after reading your previous post that the workbook was being closed. I had thought code was commented out whilst the macro is being developed but obviously it wasn't.

Thanks for the help.