Compilation of data from different files.

Posted by krishna on December 22, 2001 7:43 PM

I wanted to know how can we compile data from different excel file. I have data of financial performance of 50 companies in same format. Now I want to Compile a single row from all the files. For eg. Row 10 gives Profit for last ten years in all the files. I want a file with file name as row heading and all the profit figures. How can I do that?

Thanks

Krishna

Posted by Ivan F Moala on December 23, 2001 2:20 AM

This routine will help you get started.
It gets values from a sheet named Sheet1 @
A1 for each file in a specified Dir. You will
have to amend as required......for further help
Repost.....you'll probably need a looping routine
here as well BUT I don't know your requirements
for the cell data placements.

Sub GetValue_ViaFormula()
Dim sDir As String
Dim ShtCellLoc As String
Dim DRg As Range
Dim Files
Dim x As Double

'This is the Dir to search in
sDir = "C:\Excelfiles\"
'This is the Location/cell address
ShtCellLoc = "Sheet1'!$A$1"

Files = Dir(sDir & "*.XLS")

'Clear area Column A to place data in
'Change this as required
Columns("A:A").Clear

'speed things up
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

x = 1
On Error GoTo FileError
Do While Len(Files) > 0
Cells(1, x) = Files
Cells(2, x) = "='" & sDir & "[" & Files & "]" & ShtCellLoc
x = x + 1
Files = Dir()
Loop
Application.Calculate
Set DRg = Range(Range("A1:A2"), Range("A1:A2").End(xlToRight)) '.Copy
DRg.Copy
DRg.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:2").Columns.EntireColumn.AutoFit
Application.CutCopyMode = False

Set DRg = Nothing

Application.Calculation = xlCalculationAutomatic
Application.Calculate
Application.ScreenUpdating = True

MsgBox "Done!"

Exit Sub
FileError:
MsgBox Err.Number & Chr(13) & _
Err.Description & Chr(13) _
, vbCritical + vbMsgBoxHelpButton, _
"File Error", _
Err.HelpFile, _
Err.HelpContext
End Sub

HTH

Ivan