VBA Variable issue

[Stuart_C]

Hi all,

 

I am attempting to create a loop to perfome the same function to open different spreadsheets. I have 20 to maniuplate so I've declared varaibles and stored the filenames in the code (As the don't change). I'm aslo using the MSG boxes to test and generate the problem/test. When I produce Message Box for SrcWkBk1 it gives me the filename which is fine. However SrcWkBkX returns the the text "SrcWkBk1" and I need it to return the value of that variable, the filename.

 

Any ideas?

 

Dim SrcWkBk1 As String

SrcWkBk1 = "1-11810-24-1015k-nm.xlsx"

 

Dim SrcWkBkX As String

Dim FileLoop As Integer

 

For FileLoop = 1 To 20

SrcWkBkX = "SrcWkBk" & FileLoop

 

MsgBox SrcWkBk1

MsgBox SrcWkBkX

Next Fileloop

[SpreadsheetJockey]

How about this?

 

Dim FileLoop As Integer

Dim SrcWkBkX As Object

 

Set SrcWkBkX = CreateObject("Scripting.Dictionary")

 

SrcWkBkX(1) = "1-11810-24-1015k-nm.xlsx"

SrcWkBkX(2) = "etc."

 

For FileLoop = 1 To 20

 

MsgBox SrcWkBkX(FileLoop)

 

Next FileLoop