Gathering Data from various files.

[tomasjacob]

Hi,
I have been maintaining financial data of various firms. Each firm's data has been saved in a different excel file. There are atleast 100 such files (i.e. firms). I would like to know if there is anway I can pull out data from these files without having to open them.
For Eg. If I want the Sales Revenues for Firms 1 to 50, is there a way by which I can gather this data without having to open every file from 1 to 50. It is necessary that i maintain data for each firm in a different file, hence compiling one file with all the firms data in it is not my preferred choice. I was hoping I could get some help on writing a Macro for this purpose.
Cheers

 

[mjbeam]

Why don't you want to open them? There aren't that many ways to get data out of files without opening them.

 

[tomasjacob]

Opening each of the 50 files to find out just one data (eg: Sales Revenues) is time consuming I reckon. Isn't there a process by which a particular command enables you to collect such data from various files located in the hard drive or the local network also?

 

[mjbeam]

Well, you could link with a cell reference to the other workbooks like this:

='D:[Book4.xls]Sheet1'!$B$3

Is this what you mean?

 

[tomasjacob]

Isn't there a Macro one could write to pull out required information from various files because at the time or preparing the spreadsheet we are not aware what data we may want in a future date.
eg: today i'll need the sales figures for company X but tomorrow it could be the Profit figures for Company Y. There are atleast 60 ratios & figures of this sort in each file, and around 100 files.
Please help.

 

[Legacy 98055]

Hi Tomas.

Yes this is easy to do. There are several ways to do it as well. List some details here by answering some basic questions for a code example.

1. Are all of your files located in the same directory? Answer with folder's path. If not, would it be a problem to do so? If you cannot place them in one folder or at least in a relative path then it will be neccesary to list each full pathname for each file...

2. Are the ranges which refer to your data the same for all files?

3. List all of your range addresses which you want to pull out data. Give them descriptive names which identify the "type" of data being retrieved.

4. The oldest version of Excel you expect to open this workbook containing the the code.

5. If you need help with the manipulation of your data after having retrieved it, please put that on another thread.

tom

 

[tomasjacob]

Hi Tom
Please find my answers in CAPS

1. Are all of your files located in the same directory? Answer with folder's path. If not, would it be a problem to do so? If you cannot place them in one folder or at least in a relative path then it will be neccesary to list each full pathname for each file...
YES, ALL FILES ARE STORED IN SAME FOLDER, PATH BEING
C:AGSMPTFILENAME

2. Are the ranges which refer to your data the same for all files?
YES, ALL RANGES ARE SAME. EG: REVENUE FIGURE IS CELL D3(2002),E3(2001),F3(2000) FOR ALL THE 100 OR SO FILES.

3. List all of your range addresses which you want to pull out data. Give them descriptive names which identify the "type" of data being retrieved.
DO YOU WANT ME TO GROUP SOME DATA AND NAME THEM, I DONT SEEM TO UNDERSTAND.

4. The oldest version of Excel you expect to open this workbook containing the the code.
EXCEL 97

5. If you need help with the manipulation of your data after having retrieved it, please put that on another thread.
SORRY DONT UNDERSTAND THIS EITHER, COULD YOU PLEASE EXPLAIN IN LAYMAN TERMS. IM NOT AN EXCEL GURU AS MUCH AS I'D LIKE TO BE ONE.

THANKS,
G'DAY

 

[Ivan F Moala]

Are the cell ranges on the same sheet name for all files ??

 

[tomasjacob]

Yes, all the files have same sheet names and same cell ranges. Other than different data, everything else is similar throughout files.
Thanks

 

[Ivan F Moala]

On 2002-11-19 22:17, tomasjacob wrote:
Yes, all the files have same sheet names and same cell ranges. Other than different data, everything else is similar throughout files.
Thanks

OK then try something along these lines...change as required....

<PRE><FONT color=blue>Sub </FONT>GetValue_ViaFormula()



<FONT color=blue>Dim </FONT>sDir <FONT color=blue>As</FONT><FONT color=blue> String</FONT>

<FONT color=blue>Dim </FONT>ShtCellLoc <FONT color=#008000>'() As String
</FONT>
<FONT color=blue>Dim </FONT>rngData <FONT color=blue>As</FONT> <FONT color=blue>Range</FONT>

<FONT color=blue>Dim </FONT>strFileLoc <FONT color=blue>As</FONT><FONT color=blue> String</FONT>

<FONT color=blue>Dim </FONT>x <FONT color=blue>As</FONT><FONT color=blue> Double</FONT>



<FONT color=#008000>'This is the Dir to search in
</FONT>
sDir = "C:AGSMPT"

<FONT color=#008000>'This is the Location/cell address
</FONT>
<FONT color=#008000>'REVENUE FIGURE IS CELL D3(2002),E3(2001),F3(2000)
</FONT>
ShtCellLoc = Array("Sheet1'!$D$3", "Sheet1'!$E$3", "Sheet1'!$F$3")



strFileLoc = Dir(sDir & "*.xls")



<FONT color=#008000>'Clear area Column A to place data in
</FONT>
<FONT color=#008000>'Change this as required
</FONT>
Columns("A:A").Clear



Application.ScreenUpdating =<FONT color=blue> False</FONT>

Application.Calculation = xlCalculationManual



<FONT color=#008000>'// Leaqve Room for header info
</FONT>
x = 2



<FONT color=#008000>'// Set your Headers
</FONT>
[A1] = "REVENUE 2002": [B1] = "REVENUE 2001": [C1] = "REVENUE 2000"



<FONT color=blue>On Error</FONT> <FONT color=blue>GoTo</FONT> FileError

<FONT color=blue>Do </FONT>While Len(strFileLoc) > 0

Cells(x, 1) = "='" & sDir & "[" & strFileLoc & "]" & ShtCellLoc(0)

Cells(x, 2) = "='" & sDir & "[" & strFileLoc & "]" & ShtCellLoc(1)

Cells(x, 3) = "='" & sDir & "[" & strFileLoc & "]" & ShtCellLoc(2)

x = x + 1

strFileLoc = Dir()

<FONT color=blue>Loop</FONT>



<FONT color=blue>Set </FONT>rngData = <FONT color=blue>Range</FONT>(<FONT color=blue>Range</FONT>("A1:C1"), <FONT color=blue>Range</FONT>("A1:C1").End(xlDown))

rngData.Copy

rngData.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Columns("A:C").EntireColumn.AutoFit

Application.CutCopyMode =<FONT color=blue> False</FONT>



<FONT color=blue>Set </FONT>rngData =<FONT color=blue> Nothing</FONT>



Application.Calculation = xlCalculationAutomatic

Application.Calculate

Application.ScreenUpdating =<FONT color=blue> True</FONT>



MsgBox "Done!"



<FONT color=blue>Exit Sub</FONT>

FileError:

MsgBox Err.Number & Chr(13) & _

Err.Description & Chr(13) _

, vbCritical + vbMsgBoxHelpButton, _

"File Error", _

Err.HelpFile, _

Err.HelpContext

<FONT color=blue>End Sub</FONT>


</PRE>