VBA Variable Question

[EdE]

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!!

 

[Jay Petrulis]

Hi,

Try something like the following:

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

 

[EdE]

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

 

[Jay Petrulis]

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