Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: VBA Variable Question

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    A Mile High!!
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is a 3 parter. Sorry but I am kind of new to the VBA aspect.
    I have a macro that automatically opens 21 files to update. How do I make the filenames, passwords, and tab names variables? I want to put them all at the top of the macro so I only have to change once instead of 21 times. Kind of like making a template.

    Thanks!!

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Try something like the following:

    Code:
    Sub test()
    Dim MyArr(1 To 21), x
        
    MyArr(1) = "MyBook1"
    MyArr(2) = "MyBook2"
    
    ' and so on...
    
    For x = 1 To 21
       Workbooks.Open FileName:=MyArr(x)
    Next x
    
    End Sub
    Note that this may require the full name and path of the workbook.

    You can then reference the files by their array index later in the routine.

    HTH,
    Jay

    Edit: Amended the Filename:= reference


    [ This Message was edited by: Jay Petrulis on 2002-05-01 09:01 ]

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    A Mile High!!
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Jay. Is there a way to do the passwords too? Or would it be the same way?
    Thanks!

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can do the same

    Better yet, make your array multidimensional

    Dim MyArray(1 to 21, 1 to 2)

    MyArray(1,1) = "MyBook1"
    MyArray(1,2) = "MyPassword1"

    Then, in the loop Filename:=MyArr(x,1) Password:=MyArray(x,2)

    and the like. You may have to tinker a bit, but I believe this is well worth the effort to learn.

    Bye,
    Jay

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •