Pulling a range of data from multiple sheets in a folder

What!

Are you saying that you have circa 2200 daily workbook files split across 72 folders or 72 monthly files one in each of 72 folders?

Is the range of data you want to grab in the same position in every sheet?
Are the workbooks/sheets named consistently, i.e. do they contain a reference to the day, month & year?

How many sales records in a typical sheet. Going forward I'd urge you to record everything in one workbook and preferably a single sheet to facilitate further analysis.

Ok - I love a challenge....

How much data is on each individual sheet? How many rows and columns? Can you attach a sample of two sheets that are in the same folder? How about March 10 and March 11 in your 2009 folder.

The other question is... what answer are you trying to answer? Total sales? Transactions per day? Inventory control?

Let us see a sheet or two and noodle on the problem awhile... To attach a sample.. click on Go Advanced and then on the Paper Clip Icon above the advanced message area.

Ok Phaedrus, this problem is doable. It will take some VBA code. Do you want the answer in a large Excel spreadsheet or would Access be better for you?

To get a better answer... What are the exact names of the files and folders? Folder of "2012" has in it "Jan", "Feb" and in these are "1", "2" (or are they "01", "02")
Also - are there any other files in those folders with different and other names?

I'd suggest you add to the data accumulation the Year, Month and Day the transactions happened.

Are you Phaedrus because of Zen and the Art? I loved that book!!

Hi,

I'm assumed that you only have excel files in these folder and that the data is in the structure you provided in a sheet called "Daily Sales by Order". The following macro will open each file in each folder consecutively, extract the data, put it in the new master sheet, close the file and move on to the next file.

To be honest, all the opening and closing of files could take some time and so I would advise you to do this in batches until you know how long the whole process will take. There are also other ways of extracting data from closed workbooks but it would take me some time to be proficient enough to help you out.

Hope this helps.

abousetta

Option Explicit
Dim Dic As Object

Sub FilesInFolder()
 Application.DisplayAlerts = False
 Application.ScreenUpdating = False
  Set Dic = CreateObject("Scripting.Dictionary")
  ' Loop through all subfolders
    ListFilesInFolder "C:\Users\MyComputer'sName\Desktop\Test", True     ' << Change to your needs
  Set Dic = Nothing
  Range("A1").Value = "Folder name|File name|Name|Address|Town|Postcode|Phone number|Transaction type|Marketing"
  Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, OtherChar:="|"
 Application.DisplayAlerts = True
End Sub

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
Dim s As String
Dim arr
Dim fso As Object
Dim SourceFolder, FileItem, SubFolder
Dim R As Long, LR As Long
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = fso.GetFolder(SourceFolderName)
    For Each FileItem In SourceFolder.Files
        Dic.RemoveAll
        Workbooks.Open FileItem.Path, False
            LR = Sheets("Daily Sales by Order").Range("A" & Rows.Count).End(xlUp).Row
            For R = 3 To LR
                s = SourceFolder.Name & "|" & ActiveWorkbook.Name & "|" & _
                    Application.IfError(Cells(R, "C").Value, "") & "|" & _
                    Application.IfError(Cells(R, "D").Value, "") & "|" & _
                    Application.IfError(Cells(R, "E").Value, "") & "|" & _
                    Application.IfError(Cells(R, "F").Value, "") & "|" & _
                    Application.IfError(Cells(R, "G").Value, "") & "|" & _
                    Application.IfError(Cells(R, "H").Value, "") & "|" & _
                    Application.IfError(Cells(R, "I").Value, "")
              Dic.Item(s) = 0
            Next
        ActiveWorkbook.Close False
      ' Print results to sheet
        LR = Range("A" & Rows.Count).End(xlUp).Row
        Range("A" & LR + 1 & ":A" & UBound(Dic.Keys) + 2) = Application.Transpose(Dic.Keys)
    Next
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set fso = Nothing
End Sub

Hi Phaedrus,

Is a path like this: "C:\.......\.......\2006\January.xlsx" and the sheets "01","02" etc

or this:"C:\.......\........\2006\January\01.xlsx" and the sheets "Daily Sales by Order"

or something else??

And how do you want to pull the records???