Run time error 1004

[Guest]

Hello, I am getting a run time error on the following line of my code (entire
code below). Basically I am trying to print files that are listed in the
cells. Any help is appreciated. Thanks.

*** Line erroring out***
Set bk = Workbooks.Open(cell.Name)

***Entire code***

Sub printfromqueue()
Dim usrid As String
Dim sh As Worksheet
Dim rng As Range
Dim bk As Workbook
Dim cell As Range
usrid = Environ("Username")
Set sh = Workbooks(usrid & ".xls").Worksheets("Sheet1")

Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, 1).End(xlDown))

For Each cell In rng

Set bk = Workbooks.Open(cell.Name)

Finalize 'print macro
bk.Close Savechanges:=False
Next


End Sub

 

[Doug Glancy]

Brad,

It looks like it should be "cell.value" or "cell.text", not "cell.name"

hth,

Doug

 

[Jan Kronsell]

Try

Set bk = Workbooks.Open(cell.Value)

Jan

 

[Guest]

If it can not open the spreadsheet then you will get this error. Here is an
easy way to handle it

Sub printfromqueue()
Dim usrid As String
Dim sh As Worksheet
Dim rng As Range
Dim bk As Workbook
Dim cell As Range
usrid = Environ("Username")
Set sh = Workbooks(usrid & ".xls").Worksheets("Sheet1")

Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, 1).End(xlDown))

For Each cell In rng
on error resume next
Set bk = Workbooks.Open(cell.Name)
on error goto 0

if bk is nothng then
msgbox "Sheet " & cell.Name & " could not be opened."
else
Finalize 'print macro
bk.Close Savechanges:=False
end if
Next
End Sub

otherwise very nice code...

HTH

 

[Guest]

I got caught nappin... Cell.value... Otherwise the code is good...

 

[Guest]

Thanks all, I'll give this a try tomorrow when I'm in the office.

 

[Guest]

Alright, now I have changed it to (cell.value) and it saying file can not be
found. for example, in cell "A2" of the usrid sheet, it lists the entire file
name (programmed from a different module):

G:\my folder\Contract Quotes\Brad's Quotes\Renewals\test template print
queue.xls

But it is saying the file can not be found. Any ideas?

P.S. I've got to give credit to this code to Tom O, he created the whole
thing.