Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Premature termination (VBA5 XL97)

    I'm running an AutoClose macro in Excel which saves the current file to another drive. The code snippet follows:

    On Error Resume Next
    Kill "E:Time Sheet Backup.xls"
    On Error GoTo 0
    ActiveWorkbook.SaveAs Filename:="E:Time Sheet Backup.xls", FileFormat:=xlNormal, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
    End Sub

    After the macro has finished running I frequently find that the new file "E:Time Sheet Backup.xls" exists as an empty workbook. I'm certain that there is a line of code which will cause the macro to wait until the SaveAs activity is complete before continuing; but I can't remember it.
    Help! & thanks in advance
    Don
    Regards
    Don

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Premature termination (VBA5 XL97)

    Until recently, I did not use Excel VBA that much, but over the years, in every version of Office, there has been a "bug"/"feature" that caues a Kill-ed file to not be killed in a timely manner. Even if you put in a loop to wait, you may be unnsuccessful.

    Your problem is exhibiting the same systems.

    In Word, the error code was 55, might be the same for Excel.

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

    Re: Premature termination (VBA5 XL97)

    First a comment. I think that you should be using the SaveCopyAs method rather than the SaveAs method (if I understand what you are trying to do).

    The SaveAs method should not return before the file has been written. It sound more like the "ActiveWorkbook" is not the one which you expected it to be when the code was run. This brings up a few questions.

    1- Where is this code located? Is it in the workbook that is to be saved, or is it in another workbook? If it is in the same workbook, then using the "ThisWorkbook" object instead of the "ActiveWorkbook" would be safer. If it is in another workbook, then you should use the WorkBooks("MyWorkbook") to insure you get the correct one saved.

    2- How is this code being executed? Is it being run in an event routine, and if so which one?

    The code below might also make a difference:

    <pre> On Error Resume Next
    Name "E:Time Sheet Backup.xls" As "E:Time Sheet Backup.old"
    On Error GoTo 0
    ActiveWorkbook.SaveCopyAs Filename:="E:Time Sheet Backup.xls"
    ' Or preferably
    ' ThisWorkbook.SaveCopyAs Filename:="E:Time Sheet Backup.xls"
    On Error Resume Next
    Kill "E:Time Sheet Backup.old"
    On Error GoTo 0
    </pre>

    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Premature termination (VBA5 XL97)

    Thanks for the insight. Your recommendation cured my problem.

    Don
    Regards
    Don

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Premature termination (VBA5 XL97)

    Thanks for the thoughts. Just this morning I came across some old notes; the command I was hunting for is DoEvents

    Don
    Regards
    Don

Posting Permissions

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