if statement with reference a closed workbook RRS feed

  • Question

  • Hi Team, 

    Hope you all are doing well. Need some help on a little problem. I have tried to search on-line for a solution, but can't find anything.  I have got two excel spreadhseet's. Once called Data.xls and the other macro.xls. I want to perform a simple if statement as per the below attached code, but it doesn't seem to execute. I got a feeling it might have something to do with the referencing of the different workbooks. 

    Please can you assist? 

    Thanks in advance. 


    Sub check()
    Dim s As Long
    s = 1
    Do While Workbooks("c:\data.xls").Worksheets("Sheet1").Range("A" & s) <> ""
    If Workbooks("c:\data.xls").Worksheets("Sheet1").Range("A" & s) = Workbooks("c:\macro.xls").Worksheets("Sheet1").Range("A1") Then
    MsgBox ("match")
    End If
    s = s + 1
    End Sub

    Sunday, February 23, 2014 12:08 AM

All replies

  • Both workbooks need to be open when you run the code, you can't refer to a cell in a closed workbook.

    Since the workbooks are open, you don't need to specify the path. E.g.

        If Workbooks("data.xls").Worksheets("Sheet1").Range("A" & s) = _
    Workbooks("macro.xls").Worksheets("Sheet1").Range("A1") Then

    Regards, Hans Vogelaar (

    Sunday, February 23, 2014 12:29 AM
  • Hi Van, 

    Thanks for the above. Is there anyway to re-write the code , so that it will still work even though the data.xls is not open??



    Sunday, February 23, 2014 12:33 AM
  • There are 2 methods you could use. In the first method you will open to workbooks problematically and do what ever you want with them.

    Dim wrkbook As Workbook
    Set wrkbook = Workbooks.Open("You file Path")
    While wrkbook.Worksheets.Item("workhseet index").Cells(i, 1) <> ""
    'your code here

    If you really don't want to open the files you could always use links.

    VBA Programming Service
    Sunday, February 23, 2014 8:28 AM
  • Hi Van, 

    I have used the code you suggested below, but it is creating an 'Subscript out of range error' . 

    Please can you advise?



    If Workbooks("data.xls").Worksheets("Sheet1").Range("A" & s) = _
            Workbooks("macro.xls").Worksheets("Sheet1").Range("A1") Then

    Sunday, February 23, 2014 10:33 AM