Run time error 1004

B

Brad

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
 
D

Doug Glancy

Brad,

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

hth,

Doug
 
J

Jim Thomlinson

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
 
B

Brad

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top