Here is my current process:
1) Run a Mainframe SAS job to extract data from DB2 for a list of books.
2) Run a PC SAS job to download all the created data files to my PC.
3) Run another PC SAS job to process the downloaded data, calculate and format new data files and export into .XLS format (7 data files per book).
4) For each book in the list used in step 1:
Open all 7 Excel data files for that book, then open the summary .xls doc for that book. (The Summary docs have literally thousands of index= statements linking to the other 7 data docs for that book. ) Save the Summary document with the updated data obtained from the 7 data files to which it is linked. (I believe just having the documents all open in the same instance of Excel will force the links to update in the summary document with the new data found in the 7 data docs). Close the Summary doc and the 7 data files. Run again for the next book. (If I try to update the summary document without the linked 7 docs being physically open and in the same instance of Excel, I exceed the memory buffer for Excel and it crashes.) I need to run this for 10+ books on a weekly basis.

I want to automate step 4 above. I started by creating a new workbook with a button assigned to a macro. The macro reads the downloads book file containing the list of books to process and loops through them. For each record read, I open the 7 data files associated with that book by building my open filenames with ("c:/report/" & IssueId & IssueYear & ".xls") to dynamically open the files specific to the last book read in the loop. Then I open the summary doc for that book. Next I close the summary doc with save and then close the 7 data docs and then read the next book record in the loop and process it the same way until I reach EOF.

I have a post asking for help to get my workbook close statements to work, but thought I should ask if there is a better way for me to accomplish this task then the solution I've outlined?

I'm really new to VB Macros with Excel and welcome any suggestions.
Thanks.