Print Preview issues after running large amount of VBA
I have an Excel workbook in which almost everything is automated. It opens other workbooks, copies data from them, closes them and then loops through the data several times to generate reports that we print and use. Almost every time after running all the VBA and then trying to print, print preview gets stuck either finding the printer or loading page sizes as seen in the picture. Closing Excel and reopening the document restores print preview to normal functionality. The only thing related to printing that the VBA does is change the print area. I have not had this issue with any other documents that run VBA. Is this just a bug or possibly something in the code?
This is the code that causes print preview to fail to load in some way. If I skip this section then it works as intended... I would like to have this code function in some way as it's still needed.
Set wb1 = Workbooks.Open(FileName)
Set wb2 = ThisWorkbook
For i = LBound(sArray) To UBound(sArray) 'Loops through array, copies available data from last report
ShtName = sArray(i, 0)
On Error Resume Next
wb1.Sheets(ShtName).Activate
If Err.Number = 0 Then
wb1.Sheets(ShtName).Activate
Columns("A:U").Copy
wb2.Sheets(ShtName).Activate
Columns("BE:BV").Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
lastrow = Cells(Rows.Count, "BE").End(xlUp).Row
Range("BA2:BC2").Select
Selection.AutoFill Destination:=Range(Cells(2, "BA"), Cells(lastrow, "BC")), Type:=xlFillDefault
End If
On Error GoTo 0
DoEvents
Next i
wb1.Close False
Sheet2.Activate
I've tried commenting out "On Error Resume Next", "On Error Goto 0", "If...", "End If", and "DoEvents". Print Preview still fails with those removed and just doing the copy and paste from the previous sheet.
It seems that removing all instances of DoEvents has fixed the issue...
Print Preview works correctly and Excel does not crash when exiting the workbook anymore.
I believe you're looking for some type of refresh action that can re-sync the preview display with the data on the sheet. You might want to try this at the end:
ActiveSheet.EnableCalculation = False
ActiveSheet.EnableCalculation = True
If that doesn't work, see if manually saving fixes the issue. You can try calling Application.Save at the end.
"Print preview occurs automatically when you choose File --> Print. Another option is to use the Page Layout view (the icon on the right side of the status bar). To get the old-style print preview, you need to use VBA. The following statement displays a print preview of the active sheet: ActiveSheet.PrintPreview "
Quoted from Microsoft Excel 2013 Power Programming with VBA by John Walkenbach, pages 956-957.
That being said, I tested your issue above by recording a relatively complex macro that creates a new sheet, performs 25 automatic actions, and shows a print preview, then closes the print preview when I click OK on a MsgBox. I iterated the program to do this 1000 times. I never had an issue with the PrintPreview.
链接地址: http://www.djcxy.com/p/34252.html下一篇: 运行大量VBA后打印预览问题