Retrive data from closed workbooks

Hi All,

Hope all of you are doing great..

I have some worksheets at office which falls at certain network shared folder ... users used to daily update the data into those workbook, I was thinking for the ways to get the data into one consolidated sheet daily without opening it ..

so can anyone please tell me the method how to access a closed workbook and get data from it ..

I have a piece of snippet which I got from this forum .. but problem with that is all the master and user sheet must be in same location any how I am pasting down that snippet

Private Sub CommandButton1_Click()

    Dim ClosedWkb As Variant
    Dim xl As New Excel.Application
    Dim xlw1 As Excel.Workbook
    
    
    ' Open the excel file.
    ' replace the "c:\myDir\book1.xls" below with your excel file name.
    ClosedWkb = ThisWorkbook.Path & "\" & "User Book.xls"
    Set xlw1 = xl.Workbooks.Open(ClosedWkb)
    
    ' replace "Sheet1" with the sheet that you want to get the data from it.
    ' Get the value from cell(2,3) of the sheet.
    ThisWorkbook.Worksheets("Sheet1").Range("B15") = xlw1.Sheets("Sheet1").Cells(2, 3)
    
    ' Close worksheet without save changes.
    ' If you want to save changes, replace the False below with True
    xlw1.Close False
    
    ' free memory
    Set xlw1 = Nothing
    Set xl = Nothing

End Sub