Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection tries to open /.xls
The following works perfect until the end it tries to open a blank filename.
I have blank cells in my range. Any help please? Dim bkList As New Collection With Workbooks("1DLSUNDAY.XLS").Worksheets("Master") Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown)) End With On Error Resume Next For Each cell In rng bkList.Add Trim(cell.Text), Trim(cell.Text) Next On Error GoTo 0 For Each itm In bkList Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW BLANK\" & _ itm & ".xls" Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection tries to open /.xls
Maybe just skip the cells that are blank--don't add them to your collection:
On Error Resume Next For Each cell In rng bkList.Add Trim(cell.Text), Trim(cell.Text) Next On Error GoTo 0 becomes On Error Resume Next For Each cell In rng if trim(cell.text) = "" then 'skip it else bkList.Add Trim(cell.Text), Trim(cell.Text) end if Next On Error GoTo 0 Tomkat743 wrote: The following works perfect until the end it tries to open a blank filename. I have blank cells in my range. Any help please? Dim bkList As New Collection With Workbooks("1DLSUNDAY.XLS").Worksheets("Master") Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown)) End With On Error Resume Next For Each cell In rng bkList.Add Trim(cell.Text), Trim(cell.Text) Next On Error GoTo 0 For Each itm In bkList Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW BLANK\" & _ itm & ".xls" Next -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection tries to open /.xls
Dim bkList As New Collection
With Workbooks("1DLSUNDAY.XLS").Worksheets("Master") Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown)) End With On Error Resume Next For Each cell In rng if len(trim(cell)) 0 then bkList.Add Trim(cell.Text), Trim(cell.Text) end if Next On Error GoTo 0 For Each itm In bkList Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW BLANK\" & _ itm & ".xls" Next -- Regards, Tom Ogilvy "Tomkat743" wrote: The following works perfect until the end it tries to open a blank filename. I have blank cells in my range. Any help please? Dim bkList As New Collection With Workbooks("1DLSUNDAY.XLS").Worksheets("Master") Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown)) End With On Error Resume Next For Each cell In rng bkList.Add Trim(cell.Text), Trim(cell.Text) Next On Error GoTo 0 For Each itm In bkList Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW BLANK\" & _ itm & ".xls" Next |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection tries to open /.xls
Thanks again guys.
"Dave Peterson" wrote: Maybe just skip the cells that are blank--don't add them to your collection: On Error Resume Next For Each cell In rng bkList.Add Trim(cell.Text), Trim(cell.Text) Next On Error GoTo 0 becomes On Error Resume Next For Each cell In rng if trim(cell.text) = "" then 'skip it else bkList.Add Trim(cell.Text), Trim(cell.Text) end if Next On Error GoTo 0 Tomkat743 wrote: The following works perfect until the end it tries to open a blank filename. I have blank cells in my range. Any help please? Dim bkList As New Collection With Workbooks("1DLSUNDAY.XLS").Worksheets("Master") Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown)) End With On Error Resume Next For Each cell In rng bkList.Add Trim(cell.Text), Trim(cell.Text) Next On Error GoTo 0 For Each itm In bkList Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW BLANK\" & _ itm & ".xls" Next -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection tries to open /.xls
Sometimes they send bogus numbers above my range how can I add an additonal
<9521 "Tom Ogilvy" wrote: Dim bkList As New Collection With Workbooks("1DLSUNDAY.XLS").Worksheets("Master") Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown)) End With On Error Resume Next For Each cell In rng if len(trim(cell)) 0 then bkList.Add Trim(cell.Text), Trim(cell.Text) end if Next On Error GoTo 0 For Each itm In bkList Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW BLANK\" & _ itm & ".xls" Next -- Regards, Tom Ogilvy "Tomkat743" wrote: The following works perfect until the end it tries to open a blank filename. I have blank cells in my range. Any help please? Dim bkList As New Collection With Workbooks("1DLSUNDAY.XLS").Worksheets("Master") Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown)) End With On Error Resume Next For Each cell In rng bkList.Add Trim(cell.Text), Trim(cell.Text) Next On Error GoTo 0 For Each itm In bkList Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW BLANK\" & _ itm & ".xls" Next |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection tries to open /.xls
On the last line it should say Next itm instead of just Next also in
the middle next should be next cell. You also probably don't need to create a collection in order to do this. I assume that each cell in rng contains a workbook name. So you could just do: With Workbooks("1DLSUNDAY.XLS").Worksheets("Master") Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown)) End with For each cell in rng Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW BLANK\" & cell.value & ".xls" Next cell |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection tries to open /.xls
Thanks, but the list is just a column of Technicians on a jobs spreadsheet so
each Tech appears multiple times and some cells are blank. Your way is the way I started but it didn't work very well. "Kletcho" wrote: On the last line it should say Next itm instead of just Next also in the middle next should be next cell. You also probably don't need to create a collection in order to do this. I assume that each cell in rng contains a workbook name. So you could just do: With Workbooks("1DLSUNDAY.XLS").Worksheets("Master") Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown)) End with For each cell in rng Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW BLANK\" & cell.value & ".xls" Next cell |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection tries to open /.xls
Dim bkList As New Collection
With Workbooks("1DLSUNDAY.XLS").Worksheets("Master") Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown)) End With On Error Resume Next For Each cell In rng if len(trim(cell)) 0 then if cell.Value < 9521 then bkList.Add Trim(cell.Text), Trim(cell.Text) End if end if Next On Error GoTo 0 For Each itm In bkList Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW BLANK\" & _ itm & ".xls" Next -- Regards, Tom Ogilvy "Tomkat743" wrote in message ... Sometimes they send bogus numbers above my range how can I add an additonal <9521 "Tom Ogilvy" wrote: Dim bkList As New Collection With Workbooks("1DLSUNDAY.XLS").Worksheets("Master") Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown)) End With On Error Resume Next For Each cell In rng if len(trim(cell)) 0 then bkList.Add Trim(cell.Text), Trim(cell.Text) end if Next On Error GoTo 0 For Each itm In bkList Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW BLANK\" & _ itm & ".xls" Next -- Regards, Tom Ogilvy "Tomkat743" wrote: The following works perfect until the end it tries to open a blank filename. I have blank cells in my range. Any help please? Dim bkList As New Collection With Workbooks("1DLSUNDAY.XLS").Worksheets("Master") Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown)) End With On Error Resume Next For Each cell In rng bkList.Add Trim(cell.Text), Trim(cell.Text) Next On Error GoTo 0 For Each itm In bkList Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW BLANK\" & _ itm & ".xls" Next |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Collection Key | Excel Programming | |||
Workbooks collection is empty even though the some of the document is open | Excel Programming | |||
Collection Code | Excel Programming | |||
Collection | Excel Programming | |||
Reset New Collection | Excel Programming |