Trying to get better with Arrays...
 

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

Trying to get better with Arrays...
 

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

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