Sorting and Importing data from multiple Excel workbooks

[Reverend]

I have been using a spreadsheet to log computer users in the computer lab that I work at.

It is time for me to prepare a quarterly report for the organization that finances the computer lab and I would like help automating this process.

I have 3 months of excel files (one file for each day) seperated into three different folders (one folder for each month). Files are named in the following way YYYYMMDD.xls. I would like to create a spreadsheet that can look at all the other spread sheets and sort and pull the following information.

-how many total users? (for the three month period)

- how many users selected the "National Guard" value
- of National Guard users how many were here for TRAINING and how many for EDUCATIONAL.

- how many users selected the "Army" value
- of ARMY users how many were here for TRAINING and how many for EDUCATIONAL.

- how many users selected the "Civilian" value
- of CIVILIAN users how many were here for TRAINING and how many for EDUCATIONAL.

- how many users selected the "Family member" value
- Also how many were for TRAINING and how many for EDUCATIONAL

- how many users selected the "Other" value.
- Also how many were for TRAINING and how many for EDUCATIONAL

I can send a sample of the LOGIN sheet spreadsheet and a copy of the QUARTER report spread sheet if needed. Also I see there are utilities offered that I can post screen shots of the spreadsheets if the would be helpful. Or if anyone can help me come up with some key terms that I can google to find help performing the type of sorting and importing actions that I need. I'm sure it is a simple enough process, but I have no experience working with excel formulas that call on accessing seperate excel documents. Any help will be much appreciated.

 

[joose]

Here is some code that should be very close to what you need for building the really big list.

Public Sub ListFiles()
'Declare Variables
    Dim fs, f, f1
    Dim StartPath As String
    Dim i, LastRow, Nxrw As Integer
    Dim Dest, Source As Excel.Workbook
'PathMaker is a function that calls a file dialog, lets you
'  choose a folder and returns the path for that folder.
    StartPath = PathMaker
    If Len(StartPath) = 0 Then Exit Sub
'Create collection of files in the specified folder.
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(StartPath).Files
'Open a new workbook to paste the lists into and add the
'  column headers. You may want to also add a date column.
    Excel.Application.ScreenUpdating = False
    Set Dest = Excel.Application.Workbooks.Add
    colHead = Array("LAST NAME", "MFO ID", "RANK", "UNIT", _
      "USE", "COMP #", "TIME IN", "TIME OUT", "TOTAL TIME")
    For i = 1 To 9
        Dest.Sheets(1).Cells(1, i).Value = colHead(i - 1)
    Next i
'Cycle through each file in the collection, copying info
'  and pasting it into the new workbook
    For Each f1 In f
'Determine if the file is an excel file. If you will only
' ever have excel files in the folder, this is not necessary.
        If Right(f1.Name, 3) = "xls" Then
 'Assign the current excel file to the 'source' variable.
        Set Source = Excel.Workbooks.Open(f1.Path)
'Find the last row of data in the source workbook.
'I am assuming the data is always on Sheet1 and starts on row 4.
        LastRow = Source.Sheets(1).Cells(65536, 1).End(xlUp).Row
'Copy the data from row 4 to the last entry.
        Source.Sheets(1).Range("A4:J" & LastRow).Copy
        With Dest.Sheets(1)
            Dest.Activate
'Finds the last row on the new workbook and pastes the copied list.
            Nxrw = .Cells(65536, 1).End(xlUp).Row + 1
            .Cells(Nxrw, 1).Select
            ActiveSheet.Paste
        End With
'Clear the clipboard to make room for the next list.
        Application.CutCopyMode = False
'Close the source workbook without saving.
        Source.Close False
        End If
    Next
    Excel.Application.ScreenUpdating = True
End Sub

Public Function PathMaker()
'Calls up the file dialog box to choose what folder is searched.
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    Dim fldr As Variant
    If fd.Show = -1 Then
        For Each fldr In fd.SelectedItems
            PathMaker = fldr
        Next fldr
    End If
    Set fd = Nothing
End Function

I added comments to let you know what the code was doing so it is hopefully easier to change to meet your needs.
It shouldn't be too hard to add code that will run through 3 specified month folders instead of one at a time. But that will depent on where you run this code from. You could put it in a module in your personal workbook so it is available whenever Excel is open. Or you could put it in a module in some excel workbook used only for runnign this code.

I have tried to test the above code, but since you have the actual files you want to look at you will have to change it to work for your files and file structure. Let me know if you have trouble with this. Good Luck.