Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

excel crash

Posted on 2011-04-19
10
Medium Priority
?
570 Views
Last Modified: 2012-05-11
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
Comment
Question by:LuckyLucks
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35428592
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
 
LVL 33

Expert Comment

by:jppinto
ID: 35428618
try like this instead:

Application.DisplayAlerts = False
SrcBook.Close
Application.ActiveWorkbook.Close
Application.Quit
0
 
LVL 10

Expert Comment

by:broro183
ID: 35429411
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35430499
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
 

Author Comment

by:LuckyLucks
ID: 35485994
- 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
 
LVL 10

Expert Comment

by:broro183
ID: 35506426
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
 

Author Comment

by:LuckyLucks
ID: 35730431
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
 
LVL 10

Accepted Solution

by:
broro183 earned 2000 total points
ID: 35733698
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
 
LVL 10

Expert Comment

by:bromy2004
ID: 35929406
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question