OK that works Thanks! I should have included it in the original post but I
would like to do some stuff in the file once it is open.

In that case, you need to set a Workbook type variable to the open
file. For example

Dim R As Range
Dim WB As Workbook

For Each R In Range("AA3:AA33").Cells
If StrComp(R.Text, "yes", vbTextCompare) = 0 Then
Set WB = Workbooks.Open(Filename:=MyPath & "\" & _
R.EntireRow.Cells(1, "A").Text)
End If
Next R


In this code, the variable WB of type Workbook is set to each workbook
as it is opend or created. Once you have the WB reference, you can do
with it what you want.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com






On Thu, 18 Mar 2010 09:31:01 -0700, Thanks
wrote:

OK that works Thanks! I should have included it in the original post but I
would like to do some stuff in the file once it is open.
The first line I have after the last line you suggested is
Range("A:AI").Select
The stops and exits without selecting the range.

"Mike H" wrote:

Hi,

Try this

Sub Marine()
Dim MyRange As Range
MyPath = "C:\"
Set MyRange = Range("AA3:AA33")
For Each c In MyRange
If UCase(c.Value) = "YES" Then
Workbooks.Open Filename:=MyPath & c.Offset(, -26).Value
End If
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Thanks" wrote:

Hello
I have a range AA3:AA:33 that contains either "yes" or "no". and a list of
files (myfile.csv) in A3:A33 I would like to loop through the range AA3:AA33
and if the value is yes open the coresponding file in column A. The path to
the file is previously determined by code to be MyPath.
Thanks