Trying to get better with Arrays...
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
"Tom Ogilvy" wrote:
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.
--
Regards,
Tom Ogilvy
"Arturo" wrote:
The named range €˜FileList houses file names in a current directory Im
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
|