Trying to get better with Arrays...

[Arturo]

The named range ‘FileList’ houses file names in a current directory I’m
trying to loop through that list but am not sure how to get this set up
correctly… I get a blank MsgBox and in turn the first file does not open. I
want to learn how to stay out of the spreadsheet for this issue using arrays
but am a bit stuck at the moment…

Appreciatively,
Arturo


Sub LoopFiles()

Set myRange = Range("A1").CurrentRegion
ActiveWorkbook.Names.Add Name:="FileList", RefersToR1C1:=myRange
Files2Alter = Array(myRange)
While X <= UBound(Files2Alter)
MsgBox Files2Alter(X)
Workbooks.Open FileName:=Files2Alter(X), UpdateLinks:=0
‘Do X, Y & Z
‘Close file and save changes
Wend
End Sub

 

[Tom Ogilvy]

Sub LoopFiles()

Set myRange = Range("A1").CurrentRegion.Columns(1)
ActiveWorkbook.Names.Add Name:="FileList", RefersToR1C1:=myRange
Files2Alter = myRange.Value
While X = lbound(Files2Alter,1) to UBound(Files2Alter,1)
MsgBox Files2Alter(X,1)
Workbooks.Open FileName:=Files2Alter(X,1), UpdateLinks:=0
‘Do X, Y & Z
‘Close file and save changes
Wend
End Sub

picking up a range, even a single column or single row range returns a two
dimensional array.

 

[Arturo]

Sub LoopFiles2()
Dim myRange As Range
Dim rO As Integer
Dim Files2Alter As Variant
Dim X As Variant

Set myRange = Range("A1").CurrentRegion.Columns(1)
ActiveWorkbook.Names.Add Name:="FileList", RefersToR1C1:=myRange
Files2Alter = myRange.Value
For X = LBound(Files2Alter, 1) To UBound(Files2Alter, 1)
MsgBox Files2Alter(X, 1)
Workbooks.Open FileName:=Files2Alter(X, 1), UpdateLinks:=0
Next X
End Sub