p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   VB How-To (http://p2p.wrox.com/forumdisplay.php?f=78)
-   -   Using VBA in excel to move information (http://p2p.wrox.com/showthread.php?t=43926)

am6019a June 13th, 2006 09:27 AM

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

BrianWren June 13th, 2006 01:45 PM

(i) In VB (which for these purposes is synonymous with VBA), to join information that varies with what is called a "literal string," use the ampersand.

So let's say you had a variable called vbr that you need to use as values from 1 to 10, each time joining it to the end of "This is pass number ". You might do that like this:
Code:

    Dim vbr As Integer
    Dim s  As String

    For vbr = 1 To 10
        s = "This is pass number " & vbr
        ' Do some stuff with s here . . .
    Next vbr


(ii) Please expound.

am6019a June 14th, 2006 08:15 AM

How many times could could I use the ampersand? Could I do something like this:

"s = 'This is pass number' & vbr & vbr & vbr"

or could I prehaps do something like this

"s = 'This is pass number' & vbr
t = s & vbr"

In (ii) basically the whole thing looks like this,

C:\Rates and Forms\Rates\Submissions\HundredsOfFilesFoldersWith SixDigitNames

I will be looking in every file folder under 'Submissions.' Inside each of those hundreds of file folders there could be no XL file, in which case we start over, or there could be just the XL we need, or there could be multiple files and the program will need to pick the correct one from among them.

Thanks for all your help



All times are GMT -4. The time now is 11:44 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.