Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through a range Open a file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through a range Open a file
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 On Thu, 18 Mar 2010 08:11:02 -0700, 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through a range Open a file
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through a range Open a file
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through a range Open a file
Thanks Chip
I tried yours too and it does the same thing. "Chip Pearson" wrote: 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 On Thu, 18 Mar 2010 08:11:02 -0700, 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through a range Open a file
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through a range Open a file
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" 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. 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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through a range Open a file
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 On Thu, 18 Mar 2010 12:00:03 -0700, Thanks wrote: 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" 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. 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Application.Vlookup access a range in a file which is not open? | Excel Programming | |||
file won't open, endless loop, autorecover | Excel Discussion (Misc queries) | |||
Loop through range and open files | Excel Programming | |||
For Each loop leaves file open after .close | Excel Programming | |||
Loop through, open, and modify all workbooks in file | Excel Programming |