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