Loop through a range Open a file

[Thanks]

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

 

[Chip Pearson]

Try code like the following

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


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

 

[Mike H]

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]

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.

 

[Thanks]

Thanks Chip
I tried yours too and it does the same thing.

 

[Chip Pearson]

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

 

[Thanks]

Dim MyPath As String
Dim mybook As Workbook
Dim sfolder As String
Dim sMonth As String
Dim sYear As String
Dim R As Range
Dim sPeriod As String






With Sheets("DATA")
sMonth = .Range("B1").Value
sYear = .Range("A1").Value
End With
sPeriod = sYear & "-" & sMonth

sfolder = ThisWorkbook.Path

MyPath = "\\shares\groupdirs\0535\Portfolio Management\PM's " & sYear &
"\" & sPeriod & "\"

On Error GoTo CleanUp
Application.ScreenUpdating = False
For Each R In Range("AA3:AA33").Cells
If StrComp(R.Text, "yes", vbTextCompare) = 0 Then
Set mybook = Workbooks.Open(Filename:=MyPath & "\" & _
R.EntireRow.Cells(1, "A").Text)




Range("A1").Select
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
On Error Resume Next
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0

Columns("A:J").Select
With Selection
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A3").Select



End If
Next


Exit Sub

Im getting an excel message that says the file cannot be found. THe path
appears to be correct but there is no extension on the file. It should be
csv.




CleanUp:
Application.ScreenUpdating = True
End Sub

 

[Chip Pearson]

Do you have the Windows "Hide Extensions Of Known File Types?" option
enabled? You should not enable this. Open any folder window, go to the
Tools menu, choose Folder Options and uncheck that setting.

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