• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 592
  • Last Modified:

excel crash

hi

I have an excel VBA sheet that opens a workbook, does some calculations, creates a new workbook, copied over data from the first workbook, closes both workbooks and then quits.

"C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft Office\Microsoft Office Excel 2007.lnk" "mysheet.xlsm"

mysheet.xlsm does the following:

Private Sub Auto_Open()
    Call MySubWhichDoesEveryThing
End Sub

When the Subroutine is done, it does the following:


Application.DisplayAlerts = False
 Application.Quit
 Application.ActiveWorkbook.Close
 SrcBook.Close

Upon closing, I get the following error:
Microsoft Office Excel has encountered a problem and needs to close.
Recover my work and restart Microsoft Office Excel

What is causing this to happen every time? The excel file does the calculations and creates the new workbook and saves it properly. So all the internal code is working. However, it is crashing with the error every time it is done.

thanks

0
LuckyLucks
Asked:
LuckyLucks
  • 3
  • 2
  • 2
  • +2
1 Solution
 
jppintoCommented:
Instead of this:

Application.DisplayAlerts = False
Application.Quit
Application.ActiveWorkbook.Close
SrcBook.Close

try like this:

Application.DisplayAlerts = False
SrcBook.Close
Application.Quit
Application.ActiveWorkbook.Close

jppinto
 


0
 
jppintoCommented:
try like this instead:

Application.DisplayAlerts = False
SrcBook.Close
Application.ActiveWorkbook.Close
Application.Quit
0
 
broro183Commented:
hi everyone,

Jppinto, I agree with your final change in sequencing, but I'm not sure if "application.quit" would allow excel to return the apologetic error that the Op is mentioning.

LuckyLucks,
- What significance does the below quote ending in ".Ink" have?
"C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft Office\Microsoft Office Excel 2007.lnk" "mysheet.xlsm"


- How do you save the two files (ie what is the line of code)?
- Is the "FileFormat" argument explicitly included in the line that saves the file?

-It's very unlikely to be the cause for your current issue but...
My understanding is that "Private Sub Auto_Open()" is only provided in newer (perhaps from excel 2003 onwards) versions of excel to ensure backward compatibility and that it has been superseded by "Private Sub Workbook_Open()". I suggest changing your code to the "Private Sub Workbook_Open()" version.

-try releasing your objects once you have finished with them, an example from your posted code is...
Application.DisplayAlerts = False
SrcBook.Close
[b]set SrcBook = nothing[/b]
Application.ActiveWorkbook.Close
Application.DisplayAlerts = true 'added by Rob
Application.Quit

Open in new window


- It's a longshot but...
I don't suppose that your code you've shown us here, happens to be in the "activeworkbook" when the below line of code is reached?
Application.ActiveWorkbook.Close

Open in new window

I'm not sure what would happen, but it may be a crash, because how can code keep running when the file is closed?

hth
Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Rory ArchibaldCommented:
In addition to jppinto's revised order, I'd also suggest not quitting from the auto_open directly. Instead I would use

Private Sub Auto_Open()
    Application.OnTime now() + time(0,0,1), "MySubWhichDoesEveryThing"
End sub
0
 
LuckyLucksAuthor Commented:
- What significance does the below quote ending in ".Ink" have?
"C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft Office\Microsoft Office Excel 2007.lnk" "mysheet.xlsm"
this is just a full path to excel . When  i set up my scheduled tasks, I call my workbook as
"C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft Office\Microsoft Office Excel 2007.lnk" "mysheet.xlsm"  "mysheet.xlsm"

- How do you save the two files (ie what is the line of code)?
- Is the "FileFormat" argument explicitly included in the line that saves the file?
BookName= "N:\myNewBook.xls"
NewBook.SaveAs Filename:=BookName

0
 
broro183Commented:
Ahh, okay, thanks, I hadn't realised it was a scheduled task - I'd say it's unlikely the links have anything to do with it.

Also, it seems unlikely that stating the file format will overcome the crashing but you never know...
NewBook.SaveAs Filename:=BookName, FileFormat:=56

Open in new window

See http://www.rondebruin.nl/saveas.htm for some more explanation of "fileformat".

Do the crashes still occur after making the changes we have suggested, ie: changing the order of the code, setting the variables to nothing, & calling the sub?

Rob
0
 
LuckyLucksAuthor Commented:
yes the crashes are still there... the order of the code only looks weird because someone suggested on a forum to make it that way (they referenced some microsoft bug in excel). Knowing excel, I decided to try their advice only to find that it makes no difference in my case.

By the way, Workbook_Open does not do what Auto_Open does - if you browse to the path  where mysheet.xlsm lives and click on mysheet.xlsm, Workbook_Open does not cause MySubWhichDoesEveryThing to run....so  Workbook_Open seems deficient.

setting the variables to nothing didn't help either
set SrcBook = nothing

setting FileFormat:=56 doesn't help either

0
 
broro183Commented:
hi,

Can you please post an example file with your latest version containing all your code (but no confidential data)?


"...the order of the code only looks weird because someone suggested on a forum to make it that way (they referenced some microsoft bug in excel). Knowing excel, I decided to try their advice only to find that it makes no difference in my case."
Hmmm..., can you please provide a link to this comment?
I'm fascinated in how this comment could be worded because I find it very doubtful that your original code (quoted below) could work as intended. I believe the line stating "Application.Quit" means that no further lines of code will be processed after this line is run because the application Quits.
 
Application.DisplayAlerts = False
 Application.Quit
 Application.ActiveWorkbook.Close
 SrcBook.Close

Open in new window


To test how far the code gets before excel crashes you can use the below code to write to a log file...
'Add the three below lines inside the Sub which is meant to be running.
Dim LogFile_FullName As String
Dim TempLog_Str As String
LogFile_FullName = ThisWorkbook.Path & Application.PathSeparator & "Crash Finder (" & Format(Now, "yyyymmdd HHMM") & ").LOG"

'then scatter lines like this throughout the rest of the code in your specific Sub (below the above code)
TempLog_Str = "excel made it up to...'state place in code eg, srcbook saved'... before crashing"
Call WriteLogFile(LogFile_FullName, TempLog_Str)

'then below your other code, or preferably (IMHO), in a normal module of the same workbook, add the below Sub:

Sub WriteLogFile(FileNameStr As String, DataStr As String, Optional AlsoPrintToImmediatePane As Boolean)
'6/08/2010, RB: sourced (& then modified) from: http://www.asap-utilities.com/excel-tips-detail.php?categorie=9&m=82
'can also refer to: http://www.bygsoftware.com/Excel/VBA/LogFile.htm, _
  'http://www.exceltip.com/st/Log_files_using_VBA_in_Microsoft_Excel/493.html, _
  'http://en.allexperts.com/q/Visual-Basic-1048/create-log-file.htm
' writes data to a text file
' new entries will be added at the bottom of the file.
' if the file does not exists, it will be created.
Dim fnum As Long
    fnum = FreeFile()
    Open FileNameStr For Append Shared As #fnum
    Print #fnum, DataStr
    Close #fnum
    '    If AlsoPrintToImmediatePane Then
    '%%%Debug.Print DataStr
    '%%%Stop
    '    End If
End Sub

Open in new window



After the code has tried to run you can locate where the code gets to (before excel crashes) by reviewing the Log file which will be placed in the same folder as the existing file.

---------
Did you put the "Private Sub Workbook_Open()" sub within the "ThisWorkbook" module?
Again, an example file may help us understand why it seems deficient.
---------
Of course, even when we see an example file we may not be able to provide a solution, but we will try...

hth
Rob
0
 
bromy2004Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now