View Single Post
  #1 (permalink)  
Old June 13th, 2006, 09:27 AM
am6019a am6019a is offline
Registered User
 
Join Date: Jun 2006
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to am6019a
Default Using VBA in excel to move information

Hello All. I have string of tasks I need to automate so that they can repeat hundreds, maybe more than a thousand times. I have no experience with VBA, but I was looking through the VBA/Excel book published by this site and have been creating and adjusting enough macros for the last few days trying to figure this out. I have figured out the basic language and can follow simple instructions (big words scare me). Here are the tasks I am trying to automate:

1. I need to take a six digit string of numbers from one cell in what I have named The Primary work book.

2. Take those numbers and tack it on to the end of a file name (i.e. c:\File\OtherStuff\ThisIsWhereOurNumberGoes).

3. Open the file, open the excel file within the file (which will also have a name using the same six digit number as before and maybe one other word/phrase, like ‘rating’ or ‘rating benefits’).

4. Then in that workbook I need to go to the second page and take the result of one formula from a cell which will probably be the same cells (i.e. E8) in every file and paste that information in the appropriate columns in The Primary work book.

5. Repeat Step 4 in the same file, on the same page of the same work book, except one cell down (i.e. E9), which is also the result of a formula.

6. Back in the Primary Work book, we go back to the place where we started and go down one cell and the process repeats and repeats and repeats.

At some point I also need to close any unnecessary work books.

Help on any or all this would be great. In particular, I have not yet found the solutions to come crucial junctures. Where I have having problems are:

(i) In 2 I don’t know how to create the file name. If you put a filename in VBA without quote marks around it, the backslashes get read by VBA and the program crashes. But if you put in “c:\ FileName\Variable” then the program just looks for the file “Variable” nested in “FileName.” So this has me stumped.

(ii) In 3 I need a good way to get around all the variations in the file name. Right now I stringing together a series of IF…THEN statements, so if one file doesn’t exist, then it looks for the next one. However I don’t know home many possible variations there are in the file name and there are, is there a way I could make this more flexible?

Also any help or advice, an any part of it at all, would be much appreciated