Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with ranges in arrays... or an introduction to arrays | Excel Programming | |||
Arrays - declaration, adding values to arrays and calculation | Excel Programming | |||
Arrays | Excel Discussion (Misc queries) | |||
Arrays | Excel Programming | |||
Arrays | Excel Programming |