Macro to retrieve data from different spreadsheets

[bobs1]

Hello,

i currently use a spreadsheet that pulls sales data from several annual sales spreadsheets. but for the macro to work i need to have the spreadsheets open, and if i dont they return a #value. I need to modify the macro to pull the data through while the annual spread sheets are closed.

I think i need to use a filepath command or something but i dont really know macros very well. The current macro is:

Function CLOOKUP(lookup) 
    arr = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec") 
    For Each entry In arr 
        With Workbooks("Sales Orders_2009.xls").Sheets("Sales" & entry) 
            For i = 10 To 34 
                If .Range("B" & i).Value = lookup Then 
                    CLOOKUP = .Range("H" & i).Value 
                    GoTo complete 
                End If 
            Next i 
        End With 
    Next entry 
complete: 
End Function  

All the spread sheets are located in the same folder (R:\Market\Operational Reports). I tried putting the file path infront of the spreadsheet name, but that didnt work

any help would be great!