Extracting cell data from numerous files in multiple folders????

J

JoeJoe

Hello - let me preface this post by stating that I am a novice in
regards to Macro programming. That being stated, here's my issue.

Every month, we receive hundreds of Excel files from various business
units. The filenames are always different and they change
month-to-month, but the template format they use to input their data is
the same across the board. Thus, for example, cell A1 always contains
an account number within all these files, cell A2 contains a name, etc.

Each workbook includes two separate tabs. What I need to be able to do
is automatically extract data within specific cells for all these
workbooks. Currently the files are in four separate folders, but they
could be moved into one if that was an issue.

Thanks for your suggestions.
 
J

Joseph in Atlanta

Hello JoeJoe, this is Joseph in Atlanta

You COULD go ahead and write some VBA code to open hundreds of Excel
spreadsheets, in different directories, and collect together your data from
ALL
of them... but you've mentioned that you are not a VBA programmer.

I might suggest some other options:

The "Perl" language is capable of reading and writing Excel Spreadsheet files.
It could chew through hundreds of dirs/folders, reading all of the Excel
found,
and gathering data into another summary file. The summary could be an almost
any format you wanted, from another Excel.XLS to flat file, to dBase, to
CSV, etc.

Look here for examples:
http://homepage.eircom.net/~jmcnamara/perl/WriteExcel.html
http://search.cpan.org/~isterin/XML-Excel-0.02/Excel.pm
http://www-128.ibm.com/developerworks/linux/library/l-pexcel/
http://www.annocpan.org/~KWITKNR/DBD-Excel-0.06/Excel.pm
http://wiki.tcl.tk/3441
http://mike.kruckenberg.com/archives/2004/10/perl_and_spread.html

http://groups.google.com/group/perl...d2ca4e61f671d/57c0f991a94cbc9#57c0f991a94cbc9

The Perl scripting language wil run on Wondows, Unix, and almost any system
you
can find. It's very powerfull, but may take a bit of time to get used to.
( though probably less than Visual Basic, for this sort of task )

You can find other possibilities here:

http://en.wikipedia.org/wiki/Microsoft_Excel#APIs_and_tools

including the use of Java with Excel data:
http://andykhan.com/jexcelapi/index.html
or other tools, for teh "C" language: http://chicago.sourceforge.net/xlhtml/
even web-site PhP modules:
http://sourceforge.net/projects/phpexcelreader/
http://nslog.com/archives/2004/03/24/scripting_excel_via_php.php

I hope this gives you something to work with...

Joseph in Atlanta
 
J

JMB

Maybe this will help provide some framework to get you started. Assuming all
files are in C:\Excel, you wanted cells A1, B1, and C1 from the first
worksheet of each workbook, and you wanted to put this data into the first
worksheet of your destination workbook (beginning in cell A1, B1, and C1) then

Sub test()
Const Path = "C:\Excel"
Dim WkBk As Workbook
Dim i As Long

On Error Resume Next
Application.EnableEvents = False

With Application.FileSearch
.NewSearch
.LookIn = Path
.Filename = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count
Set WkBk = Workbooks.Open(.FoundFiles(i))
With ThisWorkbook.Worksheets(1)
.Cells(i, 1).Value = _
WkBk.Worksheets(1).Range("A1").Value
.Cells(i, 2).Value = _
WkBk.Worksheets(1).Range("B1").Value
.Cells(i, 3).Value = _
WkBk.Worksheets(1).Range("C1").Value
.Cells(i, 4).Value = _
WkBk.FullName
End With
WkBk.Close savechanges:=False
Next i
End With

End Sub



This is the section you may need to modify. This snippet is instructing
Excel to copy cell A1 of the first worksheet of your source workbook into the
first worksheet of your destination workbook (which is the workbook that
contains the macro). " i " is a counter variable that starts at 1. It
increments each time a workbook is opened and is used to determine in what
row to put the incoming data. Therefore, when the first book is opened,
..Cells(i, 1) specifies the first row and first column of Worksheets(1) - the
first worksheet. If it is possible the clients may have moved the worksheets
around, you can change Worksheets(1) to Worksheets("SheetNameHere")

With ThisWorkbook.Worksheets(1)
.Cells(i, 1).Value = _
WkBk.Worksheets(1).Range("A1").Value
 
H

Harlan Grove

Joseph in Atlanta wrote...
....
of them... but you've mentioned that you are not a VBA programmer.

I might suggest some other options:

The "Perl" language is capable of reading and writing Excel Spreadsheet files.
It could chew through hundreds of dirs/folders, reading all of the Excel
found, and gathering data into another summary file. The summary could be an
almost any format you wanted, from another Excel.XLS to flat file, to dBase, to
CSV, etc.
Click to expand...
....

If the OP can't program in VBA, is it reasonable to assume he could
program in Perl? If the OP had to learn a language from scratch, VBA is
easier to learn than Perl.

There are relatively simple techniques for pulling information from
multiple spreadsheets in batch *WITHOUT* VBA, just using formulas. One
possibility is alternative 1 in the following archived article.

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075

(or http://makeashorterlink.com/?B34B15DCC ).
 
J

JMB

The filenames are always different and they change
Wouldn't it be a hassle to use formulae if the workbook names are not
constant?
 
H

Harlan Grove

JMB wrote...
Wouldn't it be a hassle to use formulae if the workbook names are not
constant?
Click to expand...
....

Depends. Some people, like me, would consider it fairly simple and
painless to use a console prompt to run a command like

dir <x>\*.xls /b /s /a:-d > xlsfilelist.txt

to create a text file containing all .XLS filenames in the directory
<x> (a placeholder) and all its subdirectories. Relatively simple to
import text files into Excel template workbooks, and given reasonable
formula techniques, relatively simple to generate a dynamic table of
external reference formulas from a variable size table of filenames.

That said, it'd be A LOT easier and a lot more flexible to correct cell
referrences in text formulas than hardcoded into VBA macros.
 
Top