Querying a single cell from multiple workbooks

[pbennall]

I have a large number of workbooks all created from the same template file. They are all formatted in an identical manner, and each worksheet has a timestamp in cell A4 that I'm interested in extracting.

I'm trying to consolidate the filename and timestamp (cell ref A4) from each worksheet into a single table. Ideally the output table would look something like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Filename[/TD]
[TD]Timestamp[/TD]
[/TR]
[TR]
[TD]Example_FileA.xls[/TD]
[TD]01/01/2018 03:45[/TD]
[/TR]
[TR]
[TD]Example_FileB.xls[/TD]
[TD]01/02/2018 14:24[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

Any advice on how to do this with VBA or PowerQuery? My understanding is that I should be able to do this quite easily with Power Query, but it's pretty new to me.

I'm using Office 2010.

Thanks in advance!

 

[ranman256]

paste this code into a module,
set your details :
the folder to get the files: getValInAllFilesInDir "c:\folder"

the run: ScanAllFilesInFolder

  'store all the values in the A column.  Avg at the end.
'-------------
Public Sub ScanAllFilesInFolder()
'-------------

Range("A1").Select
getValInAllFilesInDir "c:\folder\"

End Sub

'-------------
Private Sub getValInAllFilesInDir(ByVal pvDir)
'-------------
Dim FSO, oFolder, oFile, oRX
Dim sTxt As String, sFile As String


Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(pvDir)  'use given folder


For Each oFile In oFolder.Files
  If InStr(oFile.Name, ".xls") > 0 Then
       vFile = pvDir & oFile.Name
       GetValInFile vFile
  End If
Next


Set oFile = Nothing
Set oFolder = Nothing
Set FSO = Nothing
End Sub


'-------------
Private Sub GetValInFile(ByVal pvFile)
'-------------
Dim wb As Workbook

Workbooks.Open pvFile
Set wb = ActiveWorkbook
vVal = wb.Range("A4").Value
wb.Close False

ActiveCell.Offset(0, 0).Value = pvFile
ActiveCell.Offset(0, 1).Value = vVal


ActiveCell.Offset(1, 0).Select   'next row
Set wb = Nothing
End Sub