Conflict between different excel instances using VBA from Word

Question

From a MS Word file, I have a VBA Macro that have to open an excel in invisible mode to get data from it. The problem is when I launch the macro it closes any other running excel instances and display an '1004' runtime error stating that << The rows method of the _global object failed >>.

Sub getDataFromExcel()
        Dim List
        Dim xlApp As Object
        Dim xlBook As Object
        Dim xlSheet As Object
        Dim xlSheetNum As Integer
        Dim iStarted As Boolean
        Dim lRow As Long
        Const strWorkBookName As String = "P:\Data_Source.xlsx"
        If Dir(strWorkBookName) = "" Then
          MsgBox "Cannot find the designated workbook: " & strWorkBookName, vbExclamation
          Exit Sub
        End If

        ' Test whether Excel is already running.
        On Error Resume Next
        iStarted = False ' Flag to record if we start Excel, so we can close it later.
        Set xlApp = GetObject(, "Excel.Application")
        'Start Excel if it isn't running
        If xlApp Is Nothing Then
          Set xlApp = CreateObject("Excel.Application")
          If xlApp Is Nothing Then
            MsgBox "Can't start Excel.", vbExclamation
            Exit Sub
          End If
          ' Record that we've started Excel.
          iStarted = True
        End If
        On Error GoTo 0

        Set xlBook = xlApp.Workbooks.Open(FileName:=strWorkBookName, ReadOnly:=True)
        xlApp.Visible = False


        'Find the last non-blank cell in column A
         lRow = xlBook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row  '<<< this line causes the error

         List = xlApp.Transpose(xlBook.Worksheets(1).Range("A2:A" & lRow).Value)
         ' Do something with List

Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub

How can I avoid this type of conflict?

Answer 1

qualify your Rows reference....

lRow = xlBook.Worksheets(1).Cells(xlBook.Worksheets(1).Rows.Count, 1).End(xlUp).Row