0

I have some Excel files named by date, let's say from "2022-02-01.xlsx" to "2022-02-28.xlsx" How can I get values from a specific cell (e.g.: A1) from all these files for a worksheet (a master files)? I don't want to open these reports. I just want to update data from those files to the master files.

cuongnv109
  • 1
  • This is one of the few defensible uses of INDIRECT perhaps? – DS_London Feb 26 '22 at 18:42
  • Look at the `GetCellMacro` function here: https://www.mrexcel.com/board/threads/copy-a-cell-value-from-multiple-closed-workbooks-into-a-new-workbook.988774/#post4746272 – Tim Williams Feb 26 '22 at 19:18

1 Answers1

0

I'm not sure if the code below will work to other Excel version.
The code below works with my Excel 2010

Sub test()

p = "D:\test\" 'the path where all the files of the wb
s = "Sheet1" 'the sheet where the cell has the value wanted to know
c = "A1" '---> the cell where the value is wanted to know

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(p)

For Each oFile In oFolder.Files
    f = oFile.Name
    VL = "'" & p & "[" & f & "]" & _
          s & "'!" & Range(c).Address(True, True, -4150)
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = _
    oFile.Name & " cell A1 value is " & ExecuteExcel4Macro(VL)
Next

End Sub

enter image description here

karma
  • 1,999
  • 1
  • 10
  • 14