Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    File Save (97: SR2)

    I am having a challenge saving a file with this code. The code will try to save the file but will eventually crash Excel.

    Sub test()
    Dim oMyWB As Workbook
    Set oMyWB = Workbooks(ActiveWorkbook.Name)
    ActiveWorkbook.Sheets("Sheet1").Cells.Copy
    With ActiveWorkbook.Sheets("Sheet1").Range("A1")
    .PasteSpecial Paste:=xlPasteValues
    End With

    oMyWB.Save 'Issue is here

    End Sub

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

    Re: File Save (97: SR2)

    I don't lnow why it crashes, but why do you use oMyWB? You set it in a very roundabout way to ActiveWorkbook, so you might as well use ActiveWorkbook.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Save (97: SR2)

    Hans,

    I get the same result ie Excel crashes

    John

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

    Re: File Save (97: SR2)

    That must be dependent on the contents of the workbook. It doesn't happen with all workbooks (I just tested it).

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Save (97: SR2)

    Hans,

    I tried copy/pastevalued the cell in the worksheet and saved the file manually. It save manually.

    I looked at the directory where the file is saved and noticed a temporary file assigned when I tried saving it by code. Not sure why it did not release the file.

    John

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Save (97: SR2)

    Hans,

    After rebuilding the workbook I have isolated the cause of crashing. It relates to a custom function. I am not sure why it would crash. I rewrote the workbook with only one cell formula using the custom function and it crashed as expected. Once I removed it there was no problem.

    Function Convert(Amount)
    Application.Volatile
    Convert = Amount * 1000
    End Function


    Any ideas as to why?

    Regards,
    John

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

    Re: File Save (97: SR2)

    I cannot reproduce the error, but I'm using a later version than 97. What happens if you remove the line Application.Volatile from the function?

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

    Re: File Save (97: SR2)

    Since it is a UDF causing trouble, maybe setting Calculation to manual and setting calculateBeforeSave to false helps?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Save (97: SR2)

    Hans,

    I tested that theory prior to posting. That too failed.

    Regards,
    John

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: File Save (97: SR2)

    I don't know if this is relevant, but CONVERT is the name of a function in the Analysis Toolpak add-in, so does it help if you rename the function (e.g. as MyConvert)?
    Also, why have you created a UDF to do such a simple thing? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Save (97: SR2)

    Rory,

    <<Also,why have you created a UDF to do such a simple thing?>>

    It's a long story..to be brief : a database stores scaled numbers. For example 100,000 is scaled to 100.000 therefore if you want to see the true amount you have to multiply it by 1,000.

    Don't ask me why ... it just is <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>
    John

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

    Re: File Save (97: SR2)

    But you could use the simple formula =1000*A1 instead of =CONVERT(A1). That way you wouldn't need the UDF at all.

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Save (97: SR2)

    Hans,

    I'm one step ahead of you. When I rewrote the file I used the multiplication method. That's how I caught the UDF challenge.

    Regards,
    John

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Save (97: SR2)

    Rory,

    It tested your suggestion by renaming the function to MyConvert. That too failed.

    No need to take this any further as I mentioned in an earlier thread that I have got it to work by changing the formula.

    Thanks for everyones help.
    John

Posting Permissions

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