Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB How-To
VB How-To Ask your "How do I do this with VB?" questions in this forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB How-To section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 13th, 2006, 09:27 AM
Registered User
Join Date: Jun 2006
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
Old June 13th, 2006, 01:45 PM
Friend of Wrox
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts

(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:
    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.
Old June 14th, 2006, 08:15 AM
Registered User
Join Date: Jun 2006
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to am6019a

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Retrieve System information by VBA powerz Access VBA 8 August 13th, 2013 03:49 PM
excel rename and move files ahzeri Excel VBA 1 October 28th, 2007 09:27 PM
Code works in Excel VBA but not Access VBA fossx Access VBA 2 May 21st, 2007 08:00 AM
Using VBA Code to Move Data Nanette Access VBA 1 November 3rd, 2006 04:16 PM

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