Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error 1004
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error 1004
Brad,
It looks like it should be "cell.value" or "cell.text", not "cell.name" hth, Doug "Brad" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error 1004
Try
Set bk = Workbooks.Open(cell.Value) Jan "Brad" skrev i en meddelelse ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error 1004
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 "Brad" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error 1004
I got caught nappin... Cell.value... Otherwise the code is good... :-)
"Jim Thomlinson" wrote: 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 "Brad" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error 1004
Thanks all, I'll give this a try tomorrow when I'm in the office.
"Brad" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error 1004
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. "Jan Kronsell" wrote: Try Set bk = Workbooks.Open(cell.Value) Jan "Brad" skrev i en meddelelse ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run time error '1004' | Excel Worksheet Functions | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
Run time error '1004': Generaol ODBC error | Excel Programming | |||
Run time error 1004 General ODCB Error | Excel Programming | |||
Application Run Time Error 1004 and Stack Error | Excel Programming |