Loop through a list of file names

[abar_on]

I have a procedure that performs certain tasks on multiple Excel files.
At the moment my procedure is set up to select each file name from a
cell, perform the task, move on to next cell for the next file name etc.

Is there any way to create an array of file names and loop through them,
assuming the task performed on each file is identical?

Note that I do not want to loop through all excel file in a subdir. I want
to loop through a selected list of files.

Any help would be greatly appreciated

 

[Richard Schollar]

Hi

Assuming the list of path & filenames is in cells A1:A10 (amend as appropriate) you can plonk these into an array using:

Dim vList As Variant
Dim i As Long, wb As Workbook

vList = Range("A1:A10").Value

'now do stuff:

For i = 1 To UBound(vList,1)  'it is a two dimensional array so must specify first dimension in Ubound
 Set wb = Workbooks.Open(vList(i,1))
  'do stuff to wb 
  wb.Close SaveChanges:=True
Next i

Make sense?

 

[amir_urmila]

Hi

Just try this

Worksheets("sheet1").Visible = True
Worksheets("sheet1").Select
Range("B2").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
For Each sm In Selection
Workbooks.Open (cur_path & "\" & sm.Value & ".xls")

in sheet1 from range B2 you can write the workbook name as many you want & then it will loop only with workbook which you have entered

 

[abar_on]

Fantastic!!! - just ran a quick test and it works.
Do you think this will save a significant amount of processing time (as opposed
to selecting a cell ref. at a time etc.)?

 

[Richard Schollar]

No, not a great deal - most of the time the routine takes will be in opening up the actual files themselves (and then whatever processing you do on them) rather than how you open them.