asked on

Excel and VBA Abort Mid-Program with No Error Messages

My workbook has VBA code that runs automatically at a set time each morning (using Windows scheduler to launch Excel and run the script). Excel opens and loads the workbook, and the VBA code starts executing properly. At the same point in the VBA script, the program stops running and Excel closes (not gracefully, just instantly) - no VBA error messages or Excel error messages - it all just stops and returns me to the Windows desktop. However, if I open Excel, load the workbook, and access the VBA code manually (Alt-F11), then run the program using the 'Run' button in the VBA editor, the program executes flawlessly, does what it is coded to do, and exits gracefully.

I have done a Ctrl-Shift-F9 to clear all breakpoints and saved the workbook. I have re-booted the PC. If the code will execute correctly when I manually start it, why will it not do so when running automatically from the scheduler? There are no coding errors I can find and none present themselves if they are there. I'm at a loss as to what to try next. This is all running under Windows 7 and Excel 2007, SP3.

Thanks for any information you might provide!

Norie

Glenn

What is the code doing at the point this happens?

Fabrice Lambert

This kind of behavior is tough to track down, because as soon as you pop-up the VBA editor, you enter "debug mode", and runtime condition arn't exactly the same.

Try to indentify any code prone to failure, write error handlers, and log as many things as you can.

Glenn Stearns

ASKER

Norie...

Here's the code snippet. Note that the '1279 section and the 'Line2 and Misc Quick Ship section are identicalexcept for where the .PDF file is saved and the ActiveSheet.PageSetup --> .PrintArea . The '1279 segment runs fine automatically but the 'Line 2 and Misc Quick Ship section does not. The abrupt end of processing occurs sometime after the 'Line 2 segment starts, but before it finishes. Hope this helps!

Glenn

'1279
Application.StatusBar = "1279 Tab is Processing..."
Windows("Top 20 All Cells Processing Program v1.0.xls").Activate
Sheets("1279").Select
Columns("A:J").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Top 20 DailyNEW.xls").Activate
Sheets("1279").Select
Columns("A:J").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Top 20 All Cells Processing Program v1.0.xls").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("Top 20 DailyNEW.xls").Activate
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Windows("Top 20 All Cells Processing Program v1.0.xls").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("Top 20 DailyNEW.xls").Activate
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
If Weekday(Now()) <> vbSaturday And Weekday(Now()) <> vbSunday Then

Application.StatusBar = "1279 Tab is publishing to PDF..."
With ActiveSheet.PageSetup
.Orientation = xlPortrait
.PrintArea = "$A$1:$I$81"
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="P:\Montgomery Top Movers Reports\1279.pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End If
Range("A1").Select
Windows("Top 20 All Cells Processing Program v1.0.xls").Activate
Sheets("1279").Select
Range("A1").Select

'Line2 and Misc Quick Ship
Application.StatusBar = "Line 2 and Miscellaneous Quick Ship Tab is processing..."
Windows("Top 20 All Cells Processing Program v1.0.xls").Activate
Sheets("Line2 & Misc").Select
Columns("A:I").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Top 20 DailyNEW.xls").Activate
Sheets("Line2 & Misc").Select
Columns("A:I").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Top 20 All Cells Processing Program v1.0.xls").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("Top 20 DailyNEW.xls").Activate
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Windows("Top 20 All Cells Processing Program v1.0.xls").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("Top 20 DailyNEW.xls").Activate
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
If Weekday(Now()) <> vbSaturday And Weekday(Now()) <> vbSunday Then
Application.StatusBar = "Line 2 and Misc. QS Tab is publishing to PDF..."
With ActiveSheet.PageSetup
.Orientation = xlPortrait
.PrintArea = "$A$1:$I$80"
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="P:\Montgomery Top Movers Reports\Line 2 and Misc QS.pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End If

Range("A1").Select
Windows("Top 20 All Cells Processing Program v1.0.xls").Activate
Sheets("Line2 & Misc").Select
Range("A1").Select

Fabrice Lambert

Eww, "Macro recorder, tell me who is the best coder" ….

This code assume many things, and it should not, for instance "Top 20 All Cells Processing Program v1.0.xls", "Top 20 DailyNEW.xls" are supposed to be open prior execution.
It also update the GUI far too often.

Better explain us in details what it is supposed to do, and we'll be able to offer an appririate solution.
As it stand, it is nowhere near understandable, neither maintainable, and 80% of it is useless.

ASKER CERTIFIED SOLUTION

Glenn Stearns

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial