VBA Express : Excel - Extract summary data from closed files

Excel

Extract summary data from closed files

Ease of Use

Easy

Version tested with

2003

Submitted by:

brettdj

Description:

A new sheet with a date stamp containing a list of file names from the target directory is created. The desired cell values are listed in the same row as the filename.

Discussion:

You have created 20 scenarios in a dirctory and wish to run a report on five KPI's for each scenario. This code can be used to extract the 100 data points from files while they are closed.

Code:

instructions for use

			Option Explicit 
Sub ExtractData() 
    Dim ws As Worksheet 
    Dim Myrange As Range, C As Range 
    Dim MainFolderName As String, myFile As String, sName As String 
    Dim i As Long, v As Long 
    MainFolderName = ThisWorkbook.path 
    Set NewSht = ws.Sheets.Add 
    i = 1 
    myFile = Dir(MainFolderName & "\*.xls", vbNormal) 
    Do While Len(myFile) <> 0 
        Cells(1, 1) = Now() 
        v = 0 
         'Skip this workbook
        If myFile <> ThisWorkbook.Name Then 
            i = i + 1 
             ' Change this sheet name
            sName = "Summary" 
             ' change these cell refs to grab the cells you want
            Set Myrange = Range("D9, I8:I10") 
            Cells(i, 1) = myFile 
            For Each C In Myrange 
                v = v + 1 
                Cells(i, 1 + v) = Application.ExecuteExcel4Macro("'" & MainFolderName & "\[" & myFile & "]" & sName & "'!" & C.Address(, , xlR1C1)) 
            Next 
        Else 
        End If 
        myFile = Dir 
    Loop 
    Columns("A:A").AutoFit 
End Sub

How to use:

Copy the code above.
Open your workbook.
Hit Alt+F11 to open the Visual Basic Editor (VBE).
From the menu, choose Insert-Module.
Paste the code into the code window at right.
Change the desired sheet name and cell references that you wish to return
Close the VBE, and save the file into the directory that contains the file data you want to summarise

Test the code:

Save the attached files to a new directory
Open up the file, Extractor.xls
Run the macro by either pressing the Command Button saying "Run", or go to Tools-Macro-Macros and double-click ExtractData.

Sample File:

Extractor.zip 8.31KB

Approved by mdmackillop

This entry has been viewed 449 times.