Setting Open Book based on Cell.Value

[Yorke]

I am trying to consolidate data from some old files, I am starting with 3 files before doing all of them. All of the file names are listed in Column Z in the Data tab, so to begin with I am doing Z2:Z4, and eventually I will extend it.

For the code I have written, Excel returns 'Run-time error '424': Object Required' and highlights the Set OpenBook line.

There is quite a lot in the "other code" section which I have removed, but that section works. I have attached photos of what I can share of the cell values and file names.

Dim Cell2 As Range
Dim OpenBook As Workbook

For Each Cell2 In ThisWorkbook.Worksheets("Data").Range("Z2:Z4")

Set OpenBook = "C:\Users\Yorke\Files\" & Cell2.Value & ".xlsx"

'Other Code

Next Cell2

 

[Alex Blakenburg]

Change that line to:

    Set OpenBook = Workbooks.Open("C:\Users\Yorke\Files\" & Cell2.Value & ".xlsx")