Applying code to active workbook from another workbook error: No such a interface supported

Question

I want below code to open a closed workbook and copy the values from the range StartRow and EndRow to active workbook.

I get

error 1004 "No such interface supported".

on line "xlBook.Sheets(ShName).Range(Cells(StartRow, 1), Cells(EndRow, 1)).Select"

When I run this code directly in the workbook I want to copy the data from, it works.

Sub GetDataFromClosedBook()

'copy data from closed workbook to active workbook

Dim xlApp As Application
Dim xlBook As Workbook
Dim sh As Object

Set xlApp = CreateObject("Excel.Application")

'Path source Wokrbook
Set xlBook = xlApp.Workbooks.Open("C:\Users\name\Desktop\EXCEL USEFUL DOSC\Missing Data Check New Process\Missing Data Reports\" & Sheets("Data Check").Range("C3").Value & ".xlsx")

xlApp.Visible = True

ShName = Sheets("Data Check").Range("C3").Value

With xlBook.Sheets(ShName)

    StartRow = .Range("E:E").Find(what:="January-2020", after:=.Range("E1")).Row
    EndRow = .Range("E:E").Find(what:="January-2020", after:=.Range("E1"), searchdirection:=xlPrevious).Row

    'ThisWorkbook.Activate
    xlBook.Sheets(ShName).Range("A2").Value = ShName

    xlBook.Sheets(ShName).Range(Cells(StartRow, 1), Cells(EndRow, 1)).Select

    'Sheets(ShName).Range(Cells(StartRow, 1), Cells(EndRow, 1)).Select
End With

xlApp.DisplayAlerts = False
xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing
Set xlBook = ActiveWorkbook

Set sh = Sheets("Dealer_ID Check")

sh.Activate
Range("A1").Select
sh.Paste

End Sub

Answer 1

Putting all the comments together, your code so far could be refactoed as

Option Explicit

Sub GetDataFromClosedBook()
    'copy data from closed workbook to active workbook

    Dim wbData As Workbook
    Dim wbDest As Workbook

    Dim wsDataCheck As Worksheet
    Dim wsDealerIDCheck As Worksheet
    Dim wsReports As Worksheet

    Dim ShName As String
    Dim PthName As String
    Dim FlName As String
    Dim rStartRow As Range, rEndRow As Range
    Dim rng As Range

    Set wbDest = ActiveWorkbook ' not prefered, better to be explicit

    Set wsDataCheck = wbDest.Worksheets("Data Check")

    'Path source Wokrbook
    PthName = "C:\Users\name\Desktop\EXCEL USEFUL DOSC\Missing Data Check New Process\Missing Data Reports\"
    FlName = wsDataCheck.Range("C3").Value
    ShName = wsDataCheck.Range("C3").Value

    On Error Resume Next
        Set wbData = Workbooks.Open(PthName & FlName & ".xlsx")
    On Error GoTo 0
    If wbData Is Nothing Then
        ' File didn't open
        Exit Sub
    End If

    Set wsReports = Nothing
    On Error Resume Next
        Set wsReports = wbData.Worksheets(ShName)
    On Error GoTo 0
    If wsReports Is Nothing Then
        ' No such sheet
        GoTo CleanUp
    End If

    With wsReports
        Set rStartRow = .Range("E:E").Find(What:="January-2020", After:=.Range("E1"), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)
        Set rEndRow = .Range("E:E").Find(What:="January-2020", After:=.Range("E1"), SearchDirection:=xlPrevious)

        If rStartRow Is Nothing Or rEndRow Is Nothing Then
            ' Search term not found, What Now?
            GoTo CleanUp
        End If

        .Range("A2").Value = ShName

        Set rng = .Range(rStartRow, rEndRow)
' For debug purposes only
.Activate  ' the worksheet
rng.Select ' the range
    End With

    Application.DisplayAlerts = False
    ' do you want to save the change you made to wbData?
    wbData.Close True ' or wbData.Save False

    Set wsDealerIDCheck = wbDest.Worksheets("Dealer_ID Check")

    '  continue ...

Exit Sub
CleanUp:
    If Not wbData Is Nothing Then wbData.Close False
End Sub

Answer 2

The comments have pointed out the disassociation in your code many times. Your code uses implicit and explicit references to worksheets without performing any of the necessary checks to prevent errors.

The commenters we're being polite and didn't use strong terms, but I am not polite: ActiveSheet is not what you think it is.

What you think ActiveSheet is during design is practically never guaranteed to be ActiveSheet during run time. There are certainly times when they are but such certainties are rare unless you make the effort to code then into reality. All other times you should explicitly reference your ranges. Consider it a life saving skill

Let's assume you set a pointer to a workbook and you open it, whatever sheet it opens to becomes the ActiveSheet. Typically this is the sheet that was last viewed when the workbook was saved, but that is by no means guaranteed.

What is even less guaranteed, is your assumption that it will open to the "Data Check" sheet.

You can read from and write to the "Data Check" sheet all day long without caring if it is the ActiveSheet or not, but you can only Select a cell on it when it is the ActiveSheet.

The worksheet variableShName is set to the "Data Check" worksheet. At no point have you validated ShName as the ActiveSheet, but ShName must be the ActiveSheet to prevent an error on this line:

xlBook.Sheets(ShName).Range(Cells(StartRow, 1), Cells(EndRow, 1)).Select

Answer 3

So I had this error in word but as was pointed out "ActiveDocument" was the issue even though I only had one word application open. By changing to wdApp.ActiveDocument it resolved it. wdApp being my word.application object.