Updating summary spreadsheet with data from files
Posted by Alexia on January 03, 2002 7:11 PM
I am writing a macro that will look for all files in a group of given directories and then extract certain data from that workbook.
What code would work in between the Open and the Next Statement. For example, for each of these workbooks, I want to take the value in cell B4 and put in in column D of the Row which is being populated for that record. The row would not be hardcoded since it would simply be the next available row.
Thanks :-)
Posted by Scott on January 03, 2002 7:33 PM
Posted by Alexia on January 03, 2002 7:46 PM
Hi Scott - Dank's response was outstanding and helped me tremndously. This question actually goes to the portion which Dank refers to at the end as "'Process the workbook". I wasn't sure if he would see a followup since I posted the orignal message much earlier today and this board seems to be forever receiving new posts, so I posted a new message. Sorry for the confusion.
Posted by Scott on January 03, 2002 8:05 PM
No Problem, but.....
No problem on the new thread.
I tried out Dank's code on my end, and it seemed to work. I was able to open every Excel file in a specified folder within a certain date range. As for the process part, I'm not sure if I'm telling you something you already know, but are you looking for something like this:
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
This selects the top cell, and goes down to the next available cell in the column.
Posted by Tom Dickinson on January 03, 2002 10:09 PM
Re: No Problem, but.....
If I am reading the problem correctly, I think you can combine the parts of Scott's programming into 1 line between the Open and Next statements:
range("'[destination file name]sheet name'!D1").end (xldown).offset(1) = range("B4")
I have not tested it, but these are the elements. No problem on the new thread. I tried out Dank's code on my end, and it seemed to work. I was able to open every Excel file in a specified folder within a certain date range. As for the process part, I'm not sure if I'm telling you something you already know, but are you looking for something like this: Range("A1").Select
: Hi Scott - Dank's response was outstanding and helped me tremndously. This question actually goes to the portion which Dank refers to at the end as "'Process the workbook". I wasn't sure if he would see a followup since I posted the orignal message much earlier today and this board seems to be forever receiving new posts, so I posted a new message. Sorry for the confusion.
