Printout

[Martin Fishlock]

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

 

[JNW]

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]

Setting wb = nothing does not change a thing.

It still crashes.

 

[Dave Peterson]

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]

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...

 

[Dave Peterson]

Excel crashes or you code crashes?

If it's your code, what line crashes?

(I don't have any guess...)

 

[Martin Fishlock]

Excel crashes.

It seems to work when you insert a sleeper after opening the file but before
printing and then a msgbox after printing.

 

[Dave Peterson]

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.)

Excel crashes.

It seems to work when you insert a sleeper after opening the file but before
printing and then a msgbox after printing.