Looping With Files

[GyroMight]

I have a column of data (paths to files) this column can have 5 to 200 filled cells depending on the project. What I am wanting to do is to write a loop so that it goes through this column and opens each file listed until it gets to the end of the column. I found what I thought I needed on this board from 2002 from user zzydhf.

Sub FileOpenLoop()
Dim ThisFileName, FileToOpen As String

ThisFileName = ActiveWorkbook.Name
Range("a1").Select
Do While ActiveCell <> ""

FileToOpen = ActiveCell
Workbooks.Open(Filename:=FileToOpen).RunAutoMacros _
Which:=xlAutoOpen

'run your macros and close file

Windows(ThisFileName).Activate

ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub

But I am having issues getting this loop to work, it will open the first file but then the macro just quits, i've tried adjusting certain parts of the code to get it to work but I was hoping someone could point me in the right direction.

Thanks.

 

[rollis13]

Just change:

ActiveCell.Offset(1, 0).Range("A1").Select

to:

ActiveCell.Offset(1, 0).Select

 

[mjbeam]

Try this:

Sub FileOpenLoop()

Dim tRow

tRow=1

Do 
  Workbooks.Open  Cells(tRow,1)
  ThisWorkbook.Activate
  tRow = tRow + 1
  DoEvents
Loop Until Cells(tRow, 1)=""

End Sub

 

[dermie_72]

Instead of using the activecell, use a range, and then for next.

Set your range by finding the height of the page.

Rownum = ActiveSheet.UsedRange.Rows.Count

the set the range to a value (Dim the value as a range)

Set FilestoOpen = Range("A1:A" & Rownum)

Then use For to work through the list.

For Each Cell in FilestoOpen

So in total the new code could look like this.

Sub FileOpenLoop()
[COLOR=#333333]Dim ThisFileName, FileToOpen As String[/COLOR]
Dim FilestoOpen as Range
[COLOR=#333333]
ThisFileName = ActiveWorkbook.Name[/COLOR]
[COLOR=#333333]Rownum = ActiveSheet.UsedRange.Rows.Count
Set FilestoOpen = Range("A1:A" & Rownum)
For Each Cell in FilestoOpen[/COLOR]
[COLOR=#333333] FileToOpen = Cell
[/COLOR][COLOR=#333333] Workbooks.Open(Filename:=FileToOpen).RunAutoMacros _[/COLOR]
[COLOR=#333333] Which:=xlAutoOpen[/COLOR]

[COLOR=#333333]'run your macros and close file[/COLOR]

[COLOR=#333333] Windows(ThisFileName).Activate[/COLOR]
Next

[COLOR=#333333]End Sub [/COLOR]

 

[GyroMight]

Instead of using the activecell, use a range, and then for next.

Set your range by finding the height of the page.

Rownum = ActiveSheet.UsedRange.Rows.Count

the set the range to a value (Dim the value as a range)

Set FilestoOpen = Range("A1:A" & Rownum)

Then use For to work through the list.

For Each Cell in FilestoOpen

So in total the new code could look like this.

Sub FileOpenLoop()
[COLOR=#333333]Dim ThisFileName, FileToOpen As String[/COLOR]
Dim FilestoOpen as Range
[COLOR=#333333]
ThisFileName = ActiveWorkbook.Name[/COLOR]
[COLOR=#333333]Rownum = ActiveSheet.UsedRange.Rows.Count
Set FilestoOpen = Range("A1:A" & Rownum)
For Each Cell in FilestoOpen[/COLOR]
[COLOR=#333333] FileToOpen = Cell
[/COLOR][COLOR=#333333] Workbooks.Open(Filename:=FileToOpen).RunAutoMacros _[/COLOR]
[COLOR=#333333] Which:=xlAutoOpen[/COLOR]

[COLOR=#333333]'run your macros and close file[/COLOR]

[COLOR=#333333] Windows(ThisFileName).Activate[/COLOR]
Next

[COLOR=#333333]End Sub [/COLOR]

I'm getting a run-time error '9' Subscript out of range error when running this code

 

[GyroMight]

In my current example I am using, the file names start in B2 and go down to B17, if I change the part of the code

Set FilestoOpen = Range("A1:A" & Rownum)

to

Set FilestoOpen = Range("B2:B5" & Rownum)

It does open the files but then after it reaches a blank cell I end up with a run time error '1004' Application-defined or object-defined error. Its strange because I can only get it to run when I use "B2:B4" or "B2:B5" everything else gives me the previously mentioned run-time error '9'.

 

[GyroMight]

Try this:

Sub FileOpenLoop()

Dim tRow

tRow=1

Do 
  Workbooks.Open  Cells(tRow,1)
  ThisWorkbook.Activate
  tRow = tRow + 1
  DoEvents
Loop Until Cells(tRow, 1)=""

End Sub

Since my files start in B2 through B17 I changed the code to

Sub FileOpenLoop2()


Dim tRow


tRow = 2


Do
Workbooks.Open Cells(tRow, 2)
ThisWorkbook.Activate
tRow = tRow + 1
DoEvents
Loop Until Cells(tRow, 2) = ""


End Sub

This actually opened all my files and gave me no run time error, thank you very much for your help!

Thanks to everyone in the thread for giving me your time and effort!