Summaraizing many files

Posted by Alexia on December 31, 2001 11:16 AM

Is there a way to write a macro in a summary workbook that would pull in information from all the NEW files in given directories. An example would be for purchase orders. I want to pull in the field for PO number, vendor, amount and then add a link to the file. Then I want to be able to run a macro once a week or so, that will add a row for each new file, pulling in this same info. Perhaps the link could be the determining factor. Sounds complex, I know.

Or, if I manually entered the PO numbers (they're sequential numbers) in the summary, could I then setup a macro to look for any new files whose name contains this PO number and either fill in the info I want or if there is none, leave the cells blank.

Any direction would be much appreciated. Thanks :-)

Posted by Damon Ostrander on December 31, 2001 11:52 PM

Hi Alexia,

Actually, what you describe is not at all hard to do in VBA. I assume that when you refer to NEW files that you mean files that have been created or modified since some date. In VBA you can find files that have been created or modified since a particular date in specific folders using the Excel application's FileSearch object. Look for this in the VBA helps--there you will find a description of its usage and some examples. Once you find each file, the macro will need to use the Open method to open each one and extract the information to write to the summary worksheet. I suspect you will want to check the PO number against numbers already on the sheet to make sure it hasn't already been added.

This may not be enough information if you have not previously worked with Excel objects and VBA programming, but hopefully will point you in the right direction.

Happy computing.

Damon

Posted by Alexia on January 01, 2002 8:25 AM

Thanks Damon!! This definetely gives me some direction!!

Thanks again :-)

Posted by Alexia on January 01, 2002 8:27 AM

Thanks Damon!! This certainly should get me started!!

Thanks again :-)