0

I am working with a Powder Flow Tester at a research company (outputs data in excel format) and need a way to summarize the data from ~2000 files [in one folder] in one excel spreadsheet. I'd like 6 pieces of data from each file. The data is in the same spot in each spreadsheet. I have tried the following, but they are too user intensive to be practical.

1) =SUM('C:\Users\MYUSER\Desktop\PFTData[PSFChoc.XLS]0001'!$K$26) poor because I need to change the file name manually for each file and need to change the cell accessed for each of the six data pieces I need.

2) =INDIRECT("'" & $K$3 & "'[" & A12 & "]" & $K$2 & "'!" & $K$1) poor because each file needs to be open for INDIRECT to work. Computer will not handle that well. Same problem as the original formula too.

3) Tried to use the Index function, but also needed to change the file name manually for each one.

Is there a formula that can create an array of each file name and access the six data pieces from each file? I am open to VBA solutions, but I have zero experience with VBA.

Joe B
  • 912
  • 2
  • 15
  • 36
  • Are the source files all single-sheet workbooks, or do they have multiple sheets/tabs? – Tim Williams Sep 06 '17 at 21:21
  • The answer [ExecuteExcel4Macro to get value from closed workbook](https://stackoverflow.com/questions/9259862/executeexcel4macro-to-get-value-from-closed-workbook) will saolve your problem. –  Sep 06 '17 at 21:23
  • They are all single-sheet workbooks – Joe B Sep 06 '17 at 21:25

1 Answers1

0

This should get you started.

Note there are two approaches to extracting the data:

  • ExecuteExcel4Macro: this is probably best if you know the worksheet names are all the same
  • Formula-based version: as long as the source files only have a single sheet, then this will work regardless of the sheet names.

Which one you use will depend on your exact use case.

Sub ExtractData()

    Const F_PATH As String = "C:\_Stuff\test\files\"
    Dim f, sht As Worksheet, arrRefs, rw As Long, cl As Long, ref

    Set sht = ThisWorkbook.Sheets("Data")

    arrRefs = Array("$A$1", "$B$2", "$C$3") 'cells to extract

    rw = 2 'starting row for data
    f = Dir(F_PATH & "*.xls*")
    Do While f <> ""

        sht.Cells(rw, 1).Value = f 'record the filename

        cl = 2 '<< starting column for extracted data
        For Each ref In arrRefs

            '## use this form if the worksheets all have the same name
            'sht.Cells(rw, cl) = ExecuteExcel4Macro("'" & F_PATH & "[" & f & _
            '            "]Sheet1'!" & Range(ref).Address(True, True, -xlR1C1))

            '## use this form if the worksheet names might vary
            '*** as long as there's only one worksheet in each file**
            With sht.Cells(rw, cl)
                .Formula = "='" & F_PATH & "[" & f & "]blah'!" & ref
                .Value = .Value
            End With
            cl = cl + 1 '<< next column
        Next ref

        rw = rw + 1
        f = Dir() '<< next file
    Loop

End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • On the line "Set sht = ThisWorkbook.Sheets("Data")" I get the error "runtime error 9 vba subscript out of range". Does this mean that I should replace "Data" with the specific number of files in the folder? – Joe B Sep 06 '17 at 22:40
  • No - you should name a sheet "Data" (or whatever you want) in the Excel file which contains the macro. And adjust the path to where the source data files are stored. – Tim Williams Sep 06 '17 at 22:49
  • Thanks! I changed "Data" to "Sheet1". I've also changed the target cells and the folder address. New error on line ".Formula = "='" & F_PATH & "[" & f & "]blah'!" & ref" is "Runtime error 1004: Application defined or object defined error". Should I change blah to something specific? Hovering over the variables shows that the file path and the cell reference is correct. – Joe B Sep 06 '17 at 23:00
  • What's the value of `ref` when it fails? – Tim Williams Sep 06 '17 at 23:33
  • The value is "$K$26" – Joe B Sep 07 '17 at 15:00
  • That's odd: for me (Excel 2013) I can put anything for the sheet name and it still works fine as long as the workbook only has a single sheet. Glad you got it working though. – Tim Williams Sep 07 '17 at 16:22
  • Hmmm...I'm on excel 2010 at work. Thanks for the help! – Joe B Sep 07 '17 at 17:57