I'm sure there's a way to do the query (number first), but I'm not sure
exactly what you're getting at. With numbers first in the filename, Windows
still doesn't get the order right (both 10 and 100 would come before 1, 200
before 2, and so on).
If it helps, you can pull the information you want into a summary
spreadsheet. Open a new workbook in Excel, go into the visual basic editor
(Tools/Macro/Visual Basic Editor). You should see the Project Explorer
Window, which will show all of your open workbooks as "Project(Book1.xls)"
(if it's not there click View/Project Explorer). Insert a module
(Insert/Module). Expand the subfolder "Modules" under your project in the
project explorer. Click on any module under this folder and a new window
should pop up (code window). Paste the following into the code window.
Sub ImportInformation()
Const Path As String = "C:\Temp\Test"
Dim fs, Folder, FileCollection, Temp
Dim Summary As Worksheet
application.screenupdating = false
Set fs = CreateObject("Scripting.FileSystemObject")
Set Folder = fs.GetFolder(Path)
Set FileCollection = Folder.Files
Set Summary = Worksheets.Add
Count = 0
For Each x In FileCollection
Workbooks.Open FileName:=x.Path
Count = Count + 1
With Summary
.Cells(Count, 1).Value = x.Name
.Cells(Count, 2).Value = ActiveSheet.Range("B6").Value
.Cells(Count, 3).Value = ActiveSheet.Range("B7").Value
.Cells(Count, 4).Value = ActiveSheet.Range("I8").Value
.Cells(Count, 5).Value = Application.Sum(ActiveSheet.Range("I14").Value, _
ActiveSheet.Range("I49").Value)
End With
Workbooks(x.Name).Close savechanges:=False
Next x
application.screenupdating=true
End Sub
You will need to edit the following line to identify the folder where all of
your files are
Const Path As String = "C:\Temp\Test"
Also, as I read your email, you wanted to get the sum of cells I14 and I49.
If you meant I14 through I49, you will need to change the following line from
..Cells(Count, 5).Value = Application.Sum(ActiveSheet.Range("I14").Value, _
ActiveSheet.Range("I49").Value)
To:
..Cells(Count, 5).Value = Application.Sum(ActiveSheet.Range("I14:I49"))
Now close visual basic window and go back to Excel. Click on
Tools/Macros/Macros and you should see this macro on the list
(ImportInformation). Run the macro and
you should get a summary that has
Column 1 - source file name
Column 2 - first name (cell B6)
Column 3 - last name (cell B7)
Column 4 - date (cell I8)
Column 5 - Sum of cell (I14), cell (I49)
You can sort this summary by the number in the filename by going to column
A, inserting a row and inputting the following formula.
=TRIM(MID(B1,SEARCH("#",B1,1)+1,SEARCH(".",B1,1)-(SEARCH("#",B1,1)+1)))
copy this formula down, then highlight column A, click on copy, then go to
Edit/PasteSpecial, Select Values and click OK. Now you can sort this table
as you normally would through data/sort.
Hope this helps.