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

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

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

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

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

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

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

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