Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printout
I'm trying to print out three sheets from a series of different workbooks.
I'm just using the standard printout method looping on a list in a sheet. When I debug and step through it the code works. But if I run it it crashes printing the first one. Any ideas? Here is the code segment and all the variables are valid etc and properly set. Workbooks.Open ws.Range(cell_airport_dir) & _ ws.Range(aprt_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sht_apt_1, sht_apt_2, _ sht_apt_3)).PrintOut Copies:=1, Collate:=True wb.Close False TIA Martin. Here is the complete code: -------CODE------------------ Option Explicit '---Constants--- Const cell_message As String = "D9" Const cell_costvars_dir As String = "B5" Const cell_airport_dir As String = "B7" Const cell_month_mmm As String = "B10" Const sht_apt_1 As String = "ACTUAL USD" Const sht_apt_2 As String = "YTD USD" Const sht_apt_3 As String = "ACTUAL USD R" Const sht_var_2 As String = "CUM" Const sht_var_3 As String = "Costs uscg" Const vars_col As String = "A" Const aprt_col As String = "B" Const start_row As Integer = 13 '---Code--- Private Sub delaytime() Dim i As Long Dim a As Long a = 0 For i = 1 To 10000 a = a + i a = a - i Next i End Sub Sub Print_Cost_Vars_And_Airport_Files() ' ' Print_Monthly_Airport_and_Cost_Vars_file Macro ' Dim l As Long Dim sz_month As String Dim sz_ans As String Dim wb As Workbook Dim wbpath As String Dim r As Long Dim ws As Worksheet On Error GoTo end_cleanup Set ws = ActiveSheet sz_month = Application.WorksheetFunction.Proper(ws.Range(cell _month_mmm)) If (sz_month = "Error") Then MsgBox "Please enter a month between 1 and 12.", vbOKOnly, "Error...", vbOKOnly GoTo end_cleanup ElseIf MsgBox("Are you sure you want to print files for " & sz_month & "?", vbYesNo) = vbNo Then GoTo end_cleanup End If If Right(ws.Range(cell_costvars_dir), 1) < "\" Then ws.Range(cell_costvars_dir) = ws.Range(cell_costvars_dir) + "\" End If If Right(ws.Range(cell_airport_dir), 1) < "\" Then ws.Range(cell_airport_dir) = ws.Range(cell_airport_dir) + "\" End If r = start_row While (ws.Range(vars_col & r) < "" Or ws.Range(aprt_col & r) < "") If ws.Range("B" & r) < "" Then ' print airport Workbooks.Open ws.Range(cell_airport_dir) & _ ws.Range(aprt_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sht_apt_1, sht_apt_2, _ sht_apt_3)).PrintOut Copies:=1, Collate:=True delaytime wb.Close False End If If ws.Range(vars_col & r) < "" Then ' print cost vars Workbooks.Open ws.Range(cell_costvars_dir) & _ ws.Range(vars_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sz_month, sht_var_2, _ sht_var_3)).PrintOut Copies:=1, Collate:=True delaytime wb.Close False End If r = r + 1 Wend end_cleanup: ws.Activate MsgBox "Done it..." Set ws = Nothing Set wb = Nothing End Sub ------CODE END------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printout
You may need to set wb = nothing at the end of each loop. I don't know if
the first loop is letting go of the first active workbook. "Martin Fishlock" wrote: I'm trying to print out three sheets from a series of different workbooks. I'm just using the standard printout method looping on a list in a sheet. When I debug and step through it the code works. But if I run it it crashes printing the first one. Any ideas? Here is the code segment and all the variables are valid etc and properly set. Workbooks.Open ws.Range(cell_airport_dir) & _ ws.Range(aprt_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sht_apt_1, sht_apt_2, _ sht_apt_3)).PrintOut Copies:=1, Collate:=True wb.Close False TIA Martin. Here is the complete code: -------CODE------------------ Option Explicit '---Constants--- Const cell_message As String = "D9" Const cell_costvars_dir As String = "B5" Const cell_airport_dir As String = "B7" Const cell_month_mmm As String = "B10" Const sht_apt_1 As String = "ACTUAL USD" Const sht_apt_2 As String = "YTD USD" Const sht_apt_3 As String = "ACTUAL USD R" Const sht_var_2 As String = "CUM" Const sht_var_3 As String = "Costs uscg" Const vars_col As String = "A" Const aprt_col As String = "B" Const start_row As Integer = 13 '---Code--- Private Sub delaytime() Dim i As Long Dim a As Long a = 0 For i = 1 To 10000 a = a + i a = a - i Next i End Sub Sub Print_Cost_Vars_And_Airport_Files() ' ' Print_Monthly_Airport_and_Cost_Vars_file Macro ' Dim l As Long Dim sz_month As String Dim sz_ans As String Dim wb As Workbook Dim wbpath As String Dim r As Long Dim ws As Worksheet On Error GoTo end_cleanup Set ws = ActiveSheet sz_month = Application.WorksheetFunction.Proper(ws.Range(cell _month_mmm)) If (sz_month = "Error") Then MsgBox "Please enter a month between 1 and 12.", vbOKOnly, "Error...", vbOKOnly GoTo end_cleanup ElseIf MsgBox("Are you sure you want to print files for " & sz_month & "?", vbYesNo) = vbNo Then GoTo end_cleanup End If If Right(ws.Range(cell_costvars_dir), 1) < "\" Then ws.Range(cell_costvars_dir) = ws.Range(cell_costvars_dir) + "\" End If If Right(ws.Range(cell_airport_dir), 1) < "\" Then ws.Range(cell_airport_dir) = ws.Range(cell_airport_dir) + "\" End If r = start_row While (ws.Range(vars_col & r) < "" Or ws.Range(aprt_col & r) < "") If ws.Range("B" & r) < "" Then ' print airport Workbooks.Open ws.Range(cell_airport_dir) & _ ws.Range(aprt_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sht_apt_1, sht_apt_2, _ sht_apt_3)).PrintOut Copies:=1, Collate:=True delaytime wb.Close False End If If ws.Range(vars_col & r) < "" Then ' print cost vars Workbooks.Open ws.Range(cell_costvars_dir) & _ ws.Range(vars_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sz_month, sht_var_2, _ sht_var_3)).PrintOut Copies:=1, Collate:=True delaytime wb.Close False End If r = r + 1 Wend end_cleanup: ws.Activate MsgBox "Done it..." Set ws = Nothing Set wb = Nothing End Sub ------CODE END------------- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printout
Setting wb = nothing does not change a thing.
It still crashes. -- HTHs Martin "JNW" wrote: You may need to set wb = nothing at the end of each loop. I don't know if the first loop is letting go of the first active workbook. "Martin Fishlock" wrote: I'm trying to print out three sheets from a series of different workbooks. I'm just using the standard printout method looping on a list in a sheet. When I debug and step through it the code works. But if I run it it crashes printing the first one. Any ideas? Here is the code segment and all the variables are valid etc and properly set. Workbooks.Open ws.Range(cell_airport_dir) & _ ws.Range(aprt_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sht_apt_1, sht_apt_2, _ sht_apt_3)).PrintOut Copies:=1, Collate:=True wb.Close False TIA Martin. Here is the complete code: -------CODE------------------ Option Explicit '---Constants--- Const cell_message As String = "D9" Const cell_costvars_dir As String = "B5" Const cell_airport_dir As String = "B7" Const cell_month_mmm As String = "B10" Const sht_apt_1 As String = "ACTUAL USD" Const sht_apt_2 As String = "YTD USD" Const sht_apt_3 As String = "ACTUAL USD R" Const sht_var_2 As String = "CUM" Const sht_var_3 As String = "Costs uscg" Const vars_col As String = "A" Const aprt_col As String = "B" Const start_row As Integer = 13 '---Code--- Private Sub delaytime() Dim i As Long Dim a As Long a = 0 For i = 1 To 10000 a = a + i a = a - i Next i End Sub Sub Print_Cost_Vars_And_Airport_Files() ' ' Print_Monthly_Airport_and_Cost_Vars_file Macro ' Dim l As Long Dim sz_month As String Dim sz_ans As String Dim wb As Workbook Dim wbpath As String Dim r As Long Dim ws As Worksheet On Error GoTo end_cleanup Set ws = ActiveSheet sz_month = Application.WorksheetFunction.Proper(ws.Range(cell _month_mmm)) If (sz_month = "Error") Then MsgBox "Please enter a month between 1 and 12.", vbOKOnly, "Error...", vbOKOnly GoTo end_cleanup ElseIf MsgBox("Are you sure you want to print files for " & sz_month & "?", vbYesNo) = vbNo Then GoTo end_cleanup End If If Right(ws.Range(cell_costvars_dir), 1) < "\" Then ws.Range(cell_costvars_dir) = ws.Range(cell_costvars_dir) + "\" End If If Right(ws.Range(cell_airport_dir), 1) < "\" Then ws.Range(cell_airport_dir) = ws.Range(cell_airport_dir) + "\" End If r = start_row While (ws.Range(vars_col & r) < "" Or ws.Range(aprt_col & r) < "") If ws.Range("B" & r) < "" Then ' print airport Workbooks.Open ws.Range(cell_airport_dir) & _ ws.Range(aprt_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sht_apt_1, sht_apt_2, _ sht_apt_3)).PrintOut Copies:=1, Collate:=True delaytime wb.Close False End If If ws.Range(vars_col & r) < "" Then ' print cost vars Workbooks.Open ws.Range(cell_costvars_dir) & _ ws.Range(vars_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sz_month, sht_var_2, _ sht_var_3)).PrintOut Copies:=1, Collate:=True delaytime wb.Close False End If r = r + 1 Wend end_cleanup: ws.Activate MsgBox "Done it..." Set ws = Nothing Set wb = Nothing End Sub ------CODE END------------- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printout
What happens when it crashes? What error do you see?
And if it crashes on the .printout statement, I'd double check that activeworkbook to see if really had worksheets named those strings. Martin Fishlock wrote: I'm trying to print out three sheets from a series of different workbooks. I'm just using the standard printout method looping on a list in a sheet. When I debug and step through it the code works. But if I run it it crashes printing the first one. Any ideas? Here is the code segment and all the variables are valid etc and properly set. Workbooks.Open ws.Range(cell_airport_dir) & _ ws.Range(aprt_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sht_apt_1, sht_apt_2, _ sht_apt_3)).PrintOut Copies:=1, Collate:=True wb.Close False TIA Martin. Here is the complete code: -------CODE------------------ Option Explicit '---Constants--- Const cell_message As String = "D9" Const cell_costvars_dir As String = "B5" Const cell_airport_dir As String = "B7" Const cell_month_mmm As String = "B10" Const sht_apt_1 As String = "ACTUAL USD" Const sht_apt_2 As String = "YTD USD" Const sht_apt_3 As String = "ACTUAL USD R" Const sht_var_2 As String = "CUM" Const sht_var_3 As String = "Costs uscg" Const vars_col As String = "A" Const aprt_col As String = "B" Const start_row As Integer = 13 '---Code--- Private Sub delaytime() Dim i As Long Dim a As Long a = 0 For i = 1 To 10000 a = a + i a = a - i Next i End Sub Sub Print_Cost_Vars_And_Airport_Files() ' ' Print_Monthly_Airport_and_Cost_Vars_file Macro ' Dim l As Long Dim sz_month As String Dim sz_ans As String Dim wb As Workbook Dim wbpath As String Dim r As Long Dim ws As Worksheet On Error GoTo end_cleanup Set ws = ActiveSheet sz_month = Application.WorksheetFunction.Proper(ws.Range(cell _month_mmm)) If (sz_month = "Error") Then MsgBox "Please enter a month between 1 and 12.", vbOKOnly, "Error...", vbOKOnly GoTo end_cleanup ElseIf MsgBox("Are you sure you want to print files for " & sz_month & "?", vbYesNo) = vbNo Then GoTo end_cleanup End If If Right(ws.Range(cell_costvars_dir), 1) < "\" Then ws.Range(cell_costvars_dir) = ws.Range(cell_costvars_dir) + "\" End If If Right(ws.Range(cell_airport_dir), 1) < "\" Then ws.Range(cell_airport_dir) = ws.Range(cell_airport_dir) + "\" End If r = start_row While (ws.Range(vars_col & r) < "" Or ws.Range(aprt_col & r) < "") If ws.Range("B" & r) < "" Then ' print airport Workbooks.Open ws.Range(cell_airport_dir) & _ ws.Range(aprt_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sht_apt_1, sht_apt_2, _ sht_apt_3)).PrintOut Copies:=1, Collate:=True delaytime wb.Close False End If If ws.Range(vars_col & r) < "" Then ' print cost vars Workbooks.Open ws.Range(cell_costvars_dir) & _ ws.Range(vars_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sz_month, sht_var_2, _ sht_var_3)).PrintOut Copies:=1, Collate:=True delaytime wb.Close False End If r = r + 1 Wend end_cleanup: ws.Activate MsgBox "Done it..." Set ws = Nothing Set wb = Nothing End Sub ------CODE END------------- -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printout
Dave,
If I step through the macro it will work and I can print many sheets. If I run it it it crashes instantly. I have found a reference on the peach excel list that says you need to pause between oprning and printing. I kind of works this but I need to also include a msgbos after printing. I just think am I doing something wrong... -- HTHs Martin "Dave Peterson" wrote: What happens when it crashes? What error do you see? And if it crashes on the .printout statement, I'd double check that activeworkbook to see if really had worksheets named those strings. Martin Fishlock wrote: I'm trying to print out three sheets from a series of different workbooks. I'm just using the standard printout method looping on a list in a sheet. When I debug and step through it the code works. But if I run it it crashes printing the first one. Any ideas? Here is the code segment and all the variables are valid etc and properly set. Workbooks.Open ws.Range(cell_airport_dir) & _ ws.Range(aprt_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sht_apt_1, sht_apt_2, _ sht_apt_3)).PrintOut Copies:=1, Collate:=True wb.Close False TIA Martin. Here is the complete code: -------CODE------------------ Option Explicit '---Constants--- Const cell_message As String = "D9" Const cell_costvars_dir As String = "B5" Const cell_airport_dir As String = "B7" Const cell_month_mmm As String = "B10" Const sht_apt_1 As String = "ACTUAL USD" Const sht_apt_2 As String = "YTD USD" Const sht_apt_3 As String = "ACTUAL USD R" Const sht_var_2 As String = "CUM" Const sht_var_3 As String = "Costs uscg" Const vars_col As String = "A" Const aprt_col As String = "B" Const start_row As Integer = 13 '---Code--- Private Sub delaytime() Dim i As Long Dim a As Long a = 0 For i = 1 To 10000 a = a + i a = a - i Next i End Sub Sub Print_Cost_Vars_And_Airport_Files() ' ' Print_Monthly_Airport_and_Cost_Vars_file Macro ' Dim l As Long Dim sz_month As String Dim sz_ans As String Dim wb As Workbook Dim wbpath As String Dim r As Long Dim ws As Worksheet On Error GoTo end_cleanup Set ws = ActiveSheet sz_month = Application.WorksheetFunction.Proper(ws.Range(cell _month_mmm)) If (sz_month = "Error") Then MsgBox "Please enter a month between 1 and 12.", vbOKOnly, "Error...", vbOKOnly GoTo end_cleanup ElseIf MsgBox("Are you sure you want to print files for " & sz_month & "?", vbYesNo) = vbNo Then GoTo end_cleanup End If If Right(ws.Range(cell_costvars_dir), 1) < "\" Then ws.Range(cell_costvars_dir) = ws.Range(cell_costvars_dir) + "\" End If If Right(ws.Range(cell_airport_dir), 1) < "\" Then ws.Range(cell_airport_dir) = ws.Range(cell_airport_dir) + "\" End If r = start_row While (ws.Range(vars_col & r) < "" Or ws.Range(aprt_col & r) < "") If ws.Range("B" & r) < "" Then ' print airport Workbooks.Open ws.Range(cell_airport_dir) & _ ws.Range(aprt_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sht_apt_1, sht_apt_2, _ sht_apt_3)).PrintOut Copies:=1, Collate:=True delaytime wb.Close False End If If ws.Range(vars_col & r) < "" Then ' print cost vars Workbooks.Open ws.Range(cell_costvars_dir) & _ ws.Range(vars_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sz_month, sht_var_2, _ sht_var_3)).PrintOut Copies:=1, Collate:=True delaytime wb.Close False End If r = r + 1 Wend end_cleanup: ws.Activate MsgBox "Done it..." Set ws = Nothing Set wb = Nothing End Sub ------CODE END------------- -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printout
Excel crashes or you code crashes?
If it's your code, what line crashes? (I don't have any guess...) Martin Fishlock wrote: Dave, If I step through the macro it will work and I can print many sheets. If I run it it it crashes instantly. I have found a reference on the peach excel list that says you need to pause between oprning and printing. I kind of works this but I need to also include a msgbos after printing. I just think am I doing something wrong... -- HTHs Martin "Dave Peterson" wrote: What happens when it crashes? What error do you see? And if it crashes on the .printout statement, I'd double check that activeworkbook to see if really had worksheets named those strings. Martin Fishlock wrote: I'm trying to print out three sheets from a series of different workbooks. I'm just using the standard printout method looping on a list in a sheet. When I debug and step through it the code works. But if I run it it crashes printing the first one. Any ideas? Here is the code segment and all the variables are valid etc and properly set. Workbooks.Open ws.Range(cell_airport_dir) & _ ws.Range(aprt_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sht_apt_1, sht_apt_2, _ sht_apt_3)).PrintOut Copies:=1, Collate:=True wb.Close False TIA Martin. Here is the complete code: -------CODE------------------ Option Explicit '---Constants--- Const cell_message As String = "D9" Const cell_costvars_dir As String = "B5" Const cell_airport_dir As String = "B7" Const cell_month_mmm As String = "B10" Const sht_apt_1 As String = "ACTUAL USD" Const sht_apt_2 As String = "YTD USD" Const sht_apt_3 As String = "ACTUAL USD R" Const sht_var_2 As String = "CUM" Const sht_var_3 As String = "Costs uscg" Const vars_col As String = "A" Const aprt_col As String = "B" Const start_row As Integer = 13 '---Code--- Private Sub delaytime() Dim i As Long Dim a As Long a = 0 For i = 1 To 10000 a = a + i a = a - i Next i End Sub Sub Print_Cost_Vars_And_Airport_Files() ' ' Print_Monthly_Airport_and_Cost_Vars_file Macro ' Dim l As Long Dim sz_month As String Dim sz_ans As String Dim wb As Workbook Dim wbpath As String Dim r As Long Dim ws As Worksheet On Error GoTo end_cleanup Set ws = ActiveSheet sz_month = Application.WorksheetFunction.Proper(ws.Range(cell _month_mmm)) If (sz_month = "Error") Then MsgBox "Please enter a month between 1 and 12.", vbOKOnly, "Error...", vbOKOnly GoTo end_cleanup ElseIf MsgBox("Are you sure you want to print files for " & sz_month & "?", vbYesNo) = vbNo Then GoTo end_cleanup End If If Right(ws.Range(cell_costvars_dir), 1) < "\" Then ws.Range(cell_costvars_dir) = ws.Range(cell_costvars_dir) + "\" End If If Right(ws.Range(cell_airport_dir), 1) < "\" Then ws.Range(cell_airport_dir) = ws.Range(cell_airport_dir) + "\" End If r = start_row While (ws.Range(vars_col & r) < "" Or ws.Range(aprt_col & r) < "") If ws.Range("B" & r) < "" Then ' print airport Workbooks.Open ws.Range(cell_airport_dir) & _ ws.Range(aprt_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sht_apt_1, sht_apt_2, _ sht_apt_3)).PrintOut Copies:=1, Collate:=True delaytime wb.Close False End If If ws.Range(vars_col & r) < "" Then ' print cost vars Workbooks.Open ws.Range(cell_costvars_dir) & _ ws.Range(vars_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sz_month, sht_var_2, _ sht_var_3)).PrintOut Copies:=1, Collate:=True delaytime wb.Close False End If r = r + 1 Wend end_cleanup: ws.Activate MsgBox "Done it..." Set ws = Nothing Set wb = Nothing End Sub ------CODE END------------- -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printout
Excel crashes.
It seems to work when you insert a sleeper after opening the file but before printing and then a msgbox after printing. -- HTHs Martin "Dave Peterson" wrote: Excel crashes or you code crashes? If it's your code, what line crashes? (I don't have any guess...) Martin Fishlock wrote: Dave, If I step through the macro it will work and I can print many sheets. If I run it it it crashes instantly. I have found a reference on the peach excel list that says you need to pause between oprning and printing. I kind of works this but I need to also include a msgbos after printing. I just think am I doing something wrong... -- HTHs Martin "Dave Peterson" wrote: What happens when it crashes? What error do you see? And if it crashes on the .printout statement, I'd double check that activeworkbook to see if really had worksheets named those strings. Martin Fishlock wrote: I'm trying to print out three sheets from a series of different workbooks. I'm just using the standard printout method looping on a list in a sheet. When I debug and step through it the code works. But if I run it it crashes printing the first one. Any ideas? Here is the code segment and all the variables are valid etc and properly set. Workbooks.Open ws.Range(cell_airport_dir) & _ ws.Range(aprt_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sht_apt_1, sht_apt_2, _ sht_apt_3)).PrintOut Copies:=1, Collate:=True wb.Close False TIA Martin. Here is the complete code: -------CODE------------------ Option Explicit '---Constants--- Const cell_message As String = "D9" Const cell_costvars_dir As String = "B5" Const cell_airport_dir As String = "B7" Const cell_month_mmm As String = "B10" Const sht_apt_1 As String = "ACTUAL USD" Const sht_apt_2 As String = "YTD USD" Const sht_apt_3 As String = "ACTUAL USD R" Const sht_var_2 As String = "CUM" Const sht_var_3 As String = "Costs uscg" Const vars_col As String = "A" Const aprt_col As String = "B" Const start_row As Integer = 13 '---Code--- Private Sub delaytime() Dim i As Long Dim a As Long a = 0 For i = 1 To 10000 a = a + i a = a - i Next i End Sub Sub Print_Cost_Vars_And_Airport_Files() ' ' Print_Monthly_Airport_and_Cost_Vars_file Macro ' Dim l As Long Dim sz_month As String Dim sz_ans As String Dim wb As Workbook Dim wbpath As String Dim r As Long Dim ws As Worksheet On Error GoTo end_cleanup Set ws = ActiveSheet sz_month = Application.WorksheetFunction.Proper(ws.Range(cell _month_mmm)) If (sz_month = "Error") Then MsgBox "Please enter a month between 1 and 12.", vbOKOnly, "Error...", vbOKOnly GoTo end_cleanup ElseIf MsgBox("Are you sure you want to print files for " & sz_month & "?", vbYesNo) = vbNo Then GoTo end_cleanup End If If Right(ws.Range(cell_costvars_dir), 1) < "\" Then ws.Range(cell_costvars_dir) = ws.Range(cell_costvars_dir) + "\" End If If Right(ws.Range(cell_airport_dir), 1) < "\" Then ws.Range(cell_airport_dir) = ws.Range(cell_airport_dir) + "\" End If r = start_row While (ws.Range(vars_col & r) < "" Or ws.Range(aprt_col & r) < "") If ws.Range("B" & r) < "" Then ' print airport Workbooks.Open ws.Range(cell_airport_dir) & _ ws.Range(aprt_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sht_apt_1, sht_apt_2, _ sht_apt_3)).PrintOut Copies:=1, Collate:=True delaytime wb.Close False End If If ws.Range(vars_col & r) < "" Then ' print cost vars Workbooks.Open ws.Range(cell_costvars_dir) & _ ws.Range(vars_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sz_month, sht_var_2, _ sht_var_3)).PrintOut Copies:=1, Collate:=True delaytime wb.Close False End If r = r + 1 Wend end_cleanup: ws.Activate MsgBox "Done it..." Set ws = Nothing Set wb = Nothing End Sub ------CODE END------------- -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printout
I still don't have a reasonable guess, but how about an unreasonable one.
Try a different printer. Or even a new printer driver from the manufacturer's site. (but keep your workaround when/if this doesn't help.) Martin Fishlock wrote: Excel crashes. It seems to work when you insert a sleeper after opening the file but before printing and then a msgbox after printing. -- HTHs Martin "Dave Peterson" wrote: Excel crashes or you code crashes? If it's your code, what line crashes? (I don't have any guess...) Martin Fishlock wrote: Dave, If I step through the macro it will work and I can print many sheets. If I run it it it crashes instantly. I have found a reference on the peach excel list that says you need to pause between oprning and printing. I kind of works this but I need to also include a msgbos after printing. I just think am I doing something wrong... -- HTHs Martin "Dave Peterson" wrote: What happens when it crashes? What error do you see? And if it crashes on the .printout statement, I'd double check that activeworkbook to see if really had worksheets named those strings. Martin Fishlock wrote: I'm trying to print out three sheets from a series of different workbooks. I'm just using the standard printout method looping on a list in a sheet. When I debug and step through it the code works. But if I run it it crashes printing the first one. Any ideas? Here is the code segment and all the variables are valid etc and properly set. Workbooks.Open ws.Range(cell_airport_dir) & _ ws.Range(aprt_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sht_apt_1, sht_apt_2, _ sht_apt_3)).PrintOut Copies:=1, Collate:=True wb.Close False TIA Martin. Here is the complete code: -------CODE------------------ Option Explicit '---Constants--- Const cell_message As String = "D9" Const cell_costvars_dir As String = "B5" Const cell_airport_dir As String = "B7" Const cell_month_mmm As String = "B10" Const sht_apt_1 As String = "ACTUAL USD" Const sht_apt_2 As String = "YTD USD" Const sht_apt_3 As String = "ACTUAL USD R" Const sht_var_2 As String = "CUM" Const sht_var_3 As String = "Costs uscg" Const vars_col As String = "A" Const aprt_col As String = "B" Const start_row As Integer = 13 '---Code--- Private Sub delaytime() Dim i As Long Dim a As Long a = 0 For i = 1 To 10000 a = a + i a = a - i Next i End Sub Sub Print_Cost_Vars_And_Airport_Files() ' ' Print_Monthly_Airport_and_Cost_Vars_file Macro ' Dim l As Long Dim sz_month As String Dim sz_ans As String Dim wb As Workbook Dim wbpath As String Dim r As Long Dim ws As Worksheet On Error GoTo end_cleanup Set ws = ActiveSheet sz_month = Application.WorksheetFunction.Proper(ws.Range(cell _month_mmm)) If (sz_month = "Error") Then MsgBox "Please enter a month between 1 and 12.", vbOKOnly, "Error...", vbOKOnly GoTo end_cleanup ElseIf MsgBox("Are you sure you want to print files for " & sz_month & "?", vbYesNo) = vbNo Then GoTo end_cleanup End If If Right(ws.Range(cell_costvars_dir), 1) < "\" Then ws.Range(cell_costvars_dir) = ws.Range(cell_costvars_dir) + "\" End If If Right(ws.Range(cell_airport_dir), 1) < "\" Then ws.Range(cell_airport_dir) = ws.Range(cell_airport_dir) + "\" End If r = start_row While (ws.Range(vars_col & r) < "" Or ws.Range(aprt_col & r) < "") If ws.Range("B" & r) < "" Then ' print airport Workbooks.Open ws.Range(cell_airport_dir) & _ ws.Range(aprt_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sht_apt_1, sht_apt_2, _ sht_apt_3)).PrintOut Copies:=1, Collate:=True delaytime wb.Close False End If If ws.Range(vars_col & r) < "" Then ' print cost vars Workbooks.Open ws.Range(cell_costvars_dir) & _ ws.Range(vars_col & r), False, True Set wb = ActiveWorkbook wb.Sheets(Array(sz_month, sht_var_2, _ sht_var_3)).PrintOut Copies:=1, Collate:=True delaytime wb.Close False End If r = r + 1 Wend end_cleanup: ws.Activate MsgBox "Done it..." Set ws = Nothing Set wb = Nothing End Sub ------CODE END------------- -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printout using MFC | Excel Worksheet Functions | |||
no #VALUE! to printout | Excel Worksheet Functions | |||
no #VALUE! to printout | Excel Worksheet Functions | |||
Vba printout | Excel Programming | |||
PRINTOUT | Excel Programming |