Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SaveAs Filename quits working. (Office 2K SR1)

    During the fund raising Auction at my local PBS station, the following VBA quit working twice. The Workbook is used to track sales during the Auction. There are a total of 39 worksheets, one for each day(9 days) and many reports. I'm using about 200 rows and up to 160 columns to track data and do all the calculations. The computer used was a laptop connected to the network with the workbook on the server. The laptop operating system was Windows 2000.

    It is set to automatically Save every 15 minutes and the Filename is automatically created with date and sequence number added. It worked fine for 8 hrs, then stopped working - data input person had to use manual saves - no problems saving. The second time it quit, it worked for 1 hr. and we were not able to use manual saves. It would start saving and at the end we would get a dialog box with an error message "File not Saved" and OK button. This dialog box is not programmed any where in the VBA. Had to close the Workbook and reopen with the VBA not working. No error message was reported to me for the first failure.

    The first VBA code is for the Timer;

    Public RunWhen As Double
    Public Const cRunIntervalSeconds = 900
    Public Const cRunWhat = "aFileSave"

    The second VBA code is for Saving the File;

    Sub aFileSave()
    '
    ' Is set to Automatically Save every 30 minutes each day of the Auction.
    ' To set or change cRunIntervalSeconds in Declarations at top of this Module.
    ' Value is in Seconds (1800 = 30 min's.).
    ' Filename is automatically created with date and sequence number added.
    ' User is returned to the cell that was being edited.
    '
    'Tell user it is time to Save the file
    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    Msg = "Time to save the file." ' Define message.
    Style = vbOKOnly + vbCritical + vbDefaultButton1 ' Define buttons.
    Title = "Save File" ' Define title.
    Help = "DEMO.HLP" ' Define Help file.
    Ctxt = 1000 ' Define topic
    ' context.
    ' Display message.
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    'Save workbook with generated filename
    ActiveWorkbook.Names.Add Name:="previous", RefersTo:="=" & ActiveCell.Address
    intNum = Range("b2") + 1
    fileSaveName = "Auction" & Year(Now()) & Range("b3") & intNum
    ActiveWorkbook.SaveAs Filename:=fileSaveName
    ActiveSheet.Protect USERINTERFACEONLY:=True
    Range("b2").Select
    ActiveCell.FormulaR1C1 = intNum
    Application.Goto Reference:="previous"
    ActiveWorkbook.Names("previous").Delete
    StartTimer 'Re-Set timer for saving files periodically
    End Sub

    Code for StartTimer;

    Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, schedule:=True
    End Sub

    The only other VBA that would be run as needed by the data entry person is attached to a Toolbar Button, but it doesn't seem to cause problems.

    Sub TIME1_Click()
    '
    ' Macro Inserts current System Time when user clicks Time Open/Close button on
    ' Auction Table Time Toolbar.
    '
    ActiveCell.Activate
    ActiveSheet.Protect USERINTERFACEONLY:=True
    ActiveCell.Value = Format(Now, "HH:MM:SS AM/PM")
    End Sub

    Sorry this is long, but any help would be greatly appricated and Thank You in Advanced.
    Jim

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SaveAs Filename quits working. (Office 2K SR1)

    Haven't spent too much time in trying to take your code apart, but the times I've had this kind of a problem it's been an issue in connection with network-related resources and available memory - not with Excel. You might like to test to see whether the same problem arises when saves are made locally. HTH
    Gre

  3. #3
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SaveAs Filename quits working. (Office 2K SR1)

    By chance, did the problems occur just prior to or on an hour mark?

    I have seen a network save operation crash with a swarm of disk I/O. Sometime this locks the file until the operating system recovers. Check for any scheduled but long forgotten autorun network tasks like defrag, backup, etc. that triggered your problem. On a network, you live and die with server ops.

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SaveAs Filename quits working. (Office 2K SR1)

    Thanks much,
    Both you and John have pointed to network related problems. Both occurrances happened at or near the hour mark. One of them was on Friday night at midnight - possibly backup time for the network. There has been other problems on this network in the past. I am not the IT person at the Station, so I cannot answer or check some of the items you and John mentioned.

    Thanks again,
    Jim

  5. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SaveAs Filename quits working. (Office 2K SR1)

    John, Thank you
    Both you and Unkamunka have pointed to network related problems. Both occurrances happened at or near the hour mark. One of them was on Friday night at midnight - possibly backup time for the network. There has been other problems on this network in the past. I am not the IT person at the Station, so I cannot answer or check some of the items you or Unkamunka mentioned. On nights when all worked properly, I had up to 50+ files - all but the last file would be deleted.

    Thanks,
    Jim

Posting Permissions

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