Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Workbook open crash (Excel2003)

    I have a workbook with VBA code.
    If I open the workbook via Windows Explorer (without Excel running anywhere), it always crashes Excel on startup.
    If I load Excel first, then select the file by double-clicking it in a Windows Explorer file browser window, it mostly crashes Excel (but once or twice it was OK)
    If I create a desktop shortcut to the file and then double click the shortcut, it always crashes Excel.

    If I load Excel first, then select the file from Excel's File-Open last used file list, it opens OK.

    I ran a search on Woody's and found that Waggers (Re:603383) reported something similar.
    Jan Karel Pieterse replied (603471) with some guidance on using an OnTime event with a ContinueOpen routine.
    I put that in, but my problem is still there.

    The workbook is to be distributed to many Users. I would prefer them to be able to create and use a desktop shortcut.

    (I am using the latest Windows XP Media Center Edition with Service Pack2)

    If anyone has any insight I would be very grateful.

    zeddy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Workbook open crash (Excel2003)

    Can you provide some information on the code that runs automatically when the workbook is opened?

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Workbook open crash (Excel2003)

    Hi Hans

    I trimmed down to the following startup code and still have the problem..

    Private Sub Workbook_Open()
    Application.OnTime Now, "continueStartup"
    End Sub

    Sub continueStartup()
    [thisFilename] = ThisWorkbook.Name
    End Sub


    Sub auto_open()

    Worksheets("Main").Select 'switch to main startup sheet
    [a1].Select 'put cellpointer in tidy location

    End Sub


    Curiously, if I start Excel first and then select the file from the Excel2003 right-hand task pane list, Excel will always crash on open.
    If I start Excel first, then select File Open and browse directly to the file it will open OK.
    If I start Excel first, then select File from the top panel command bar, then select the file from the 'recently used' list, the file will open OK.


    zeddy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Workbook open crash (Excel2003)

    Try putting all code to be executed in either continueStartup (and remove auto_open) or in auto_open (and remove Workbook_Open).

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook open crash (Excel2003)

    Hi Zeddy,

    I'd combine these two:

    Sub continueStartup()
    [thisFilename] = ThisWorkbook.Name
    End Sub


    Sub auto_open()

    Worksheets("Main").Select 'switch to main startup sheet
    [a1].Select 'put cellpointer in tidy location

    End Sub

    into one:

    Sub continueStartup()
    Thisworkbook.Names("thisFilename").RefersToRange.V alue = ThisWorkbook.Name
    Worksheets("Main").Select 'switch to main startup sheet
    Range("a1").Select 'put cellpointer in tidy location
    End Sub

    There is no pointin first postponing code that should be run at workbook_open and at the same time have code that is in Auto_Open. Auto_Open suffers comparable problems: Excel might not be ready to do what you want to do in Auto_Open.

    Furthermore, sounds like your file has gained a corruption.

    Try exporting (save as) the file as web page (html), closing Excel and opening the html file. Then save-as to normal workbook format.
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Workbook open crash (Excel2003)

    Hi Jan

    I agree.
    Originally, I just had the Auto_Open.
    I added the Workbook_Open and continue routines based on your reply to Waggers.

    I did as you suggested and got rid of the Auto_Open.
    Same result - crash on open unless you start Excel and then select from recently used file list.

    I tried saving as html and back to Excel normal format.
    Same result.

    It's just a puzzle to me.
    If you open the file one way it crashes - open it a different way, it doesn't.
    jeeez!

    zeddy

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Workbook open crash (Excel2003)

    Could you post a stripped down copy of the workbook? You can delete the contents of all cells, and remove all code except the startup code.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook open crash (Excel2003)

    What happens if you hold down the shift key whilst opening the file (in all those ways you described)? (You might have to OK a couple of messages about cannot edit addins, keep holding down that key until your file is loaded)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Workbook open crash (Excel2003)

    Hi Jan

    Holding down the shift key has no apparent effect in all the ways.
    It only opens OK via the Excel File menu

    zeddy

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Workbook open crash (Excel2003)

    Hi Hans

    So far I have removed ALL code from the file and it still crashes on open (unless I select it from the Excel File menu item).
    So it seemed to point to a problem on one of the sheets.

    I thought I'd start deleting sheets in turn to track it down.
    I deleted the last sheet, saved the file.
    Voila!
    It now opens directly from a windows file browser without crashing.
    So I assume my problem is related to this particular sheet.

    Unfortunately, it is my Parameters sheet where I have plenty of global range names assigned for the workbook.
    I could list all the named ranges and perhaps remove them one at a time until (hopefully) I find the source of my problem.
    What do you think is the best way to approach this?

    zeddy

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook open crash (Excel2003)

    Do you have any Worksheet_Activate event code for any sheets in the workbook, particularly for the sheet that displays when the workbook opens? Does the workbook contain any class modules?
    Legare Coleman

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Workbook open crash (Excel2003)

    However unpleasant it is, it's probably best to copy the used range of the Parameters sheet to a new sheet, update formulas and named ranges, then delete the Parameters sheet.

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook open crash (Excel2003)

    Get a hold of my name manager.

    Click the list names button (to the left of the delete button) to create a worksheet with all your range names
    Then insert a new sheet, copy all info from the old parameter sheet to that sheet (might be best to do copy/paste special values) and remove the param sheet.
    Save the file.
    NOw rename your new sheet to the same name as the old param sheet and open name manager again.
    Click the pickup button (to the right of the list button) and select your names you want inserted/as they are listed on the names sheet.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Workbook open crash (Excel2003)

    Hi Legare

    No - I removed all code from worksheets, workbook and all modules. No class modules.
    Problem still existed.
    Looks like something funny on my Parameters sheet.

    I'm going to try and track it down bit by bit.

    zeddy

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook open crash (Excel2003)

    My past experience says that tracking it down is a waste of time. I spent a lot of time tracking down a corrupt cell on a worksheet once, only to find out that once I found out which cell was causing the problem, there was no way to fix it. I couldn't delete the cell, or the row or column containing it and nothing I did to the cell fixed the problem. I ended up recreating the worksheet.
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •