Run time error 1004

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Brad,

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

hth,

Doug
 
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
 
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.
 
Back
Top