Macros to Browse for Files

C

chrisandrews

I am trying to summarise information held in various excel files.

I want to browse for a file by clicking an object 'button' - th
various pieces of information held in the file I've selected are to b
summarised in the sheet.

Ultimately, I am trying to collate information from various excel file
and summarise them in a new file. Can this be done?

E.G I have twenty different excel files. Each of the files is setu
the same way so that the "Bill total" is in the same cell. So, th
twenty files all have $10 as a total - the summary file (which I a
trying to create) would read $200.

Can this be done?
 
B

Bernie Deitrick

Chris,

Of course. There are a number of ways of doing this, and the best depends on
your situation.

1) If the files are all in the same folder (with no other files): you could
run a simple macro to open every file and read the data and write it into
the appropriate summary table.

2)All the files are in the same folder (with other files that you aren't
interested in): you run a similar simple macro that asks you which files to
open, or base it on some aspect of the filename or other file property.

3)You have a list of files that are in a number of different folders: you
write a macro that creates a link to the appropriate cell as a formula, and
do the table creation that way.

Examples of all these types of macros exist in the archives: try googling
first, or post back with more specifics.

HTH,
Bernie
MS Excel MVP
 
C

chrisandrews

Bernie,

Thanks very much for your help. I'm not great with this aspect o
excel and you assistance is greatly appreciated. However, I need t
perform the second option you suggested.

All the files I need are in 1 folder, but with different filetypes
don't need.

The source files have the values in the same cells (e.g cell A2 hold
the cost, cell D6 holds the total metres of material etc).

Can u direct me to the routine I am looking for which will make thi
easier?

Thanks for your help
 
B

Bernie Deitrick

Chris,

IF you need ALLl the Excel files in the folder, you can do something like
the first macro below, which will process ONLY Excel files, but will process
ALL the Excel files in the folder. If you want to select the files to
process, use the second macro below. The first needs to have the folder
changed to reflect which folder you actually want to process, but the second
will allow you to browse to your folder.

Both will call the third macro "MakeSummary", which shows how to copy
specific cell values to create a table, which will be made on the
activesheet of the file that contains the code.

If you need help modifying these, let me know.

HTH,
Bernie
MS Excel MVP

Sub RunMacroOnAllFilesInFolder()
With Application.FileSearch
.NewSearch
.LookIn = "C:\Excel"
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
MakeSummary
ActiveWorkbook.Close False
Next i
End If
End With
End Sub

Sub OpenMultipleUserSelectedFiles()
Dim filearray As Variant
Dim i As Integer

filearray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(filearray) Then
For i = LBound(filearray) To UBound(filearray)
Workbooks.Open filearray(i)
MakeSummary
ActiveWorkbook.Close False
Next i
End If
End Sub

Sub MakeSummary()
ThisWorkbook.Worksheets(1).Range("A65536").End(xlUp)(2).Value = _
ActiveWorkbook.Name
ThisWorkbook.Worksheets(1).Range("B65536").End(xlUp)(2).Value = _
ActiveWorkbook.Worksheets("Sheet1").Range("B2").Value
ThisWorkbook.Worksheets(1).Range("C65536").End(xlUp)(2).Value = _
ActiveWorkbook.Worksheets("Sheet1").Range("C2").Value
End Sub
 
C

chrisandrews

Bernie,

Thank you very, very much for your help! I have been able to do what
wanted and my problems are solved (almost!).

I havent finished the summary template yet, so you may be hearing fro
me in the future.

Thanks again,
Chri
 
Top