kevinrj0314


In advance, I appreciate any help or suggestions...

I have an Excel workbook called "PXImport" that is very simple, yet I encounter the SaveAs auto-recover dialog box appearing regularly with a generic name like "90FBE46L". This causes the entire workbook to stop executing.

PXImport is linked to a local data source that changes in real-time. PXImport is saved in 10 second intervals in order to save its current state so that several external workbooks can remotely link to PXImport.

The error is in the SaveData() function. This function is called every 10 seconds. The error Msg is: Method 'Save' of object '_Workbook' failed

It runs just fine about 98.5% of the time. Every once in a while everything will stop and I am greeted with the SaveAs box with a name like "90FBE46L". As you can imagine, this causes stability issues. I use ActiveWorkbook.Save inorder to keep the workbook current because I have remote books linked to this one. If I do not use ActiveWorkbook.Save, the book will not stay current for the remote books to link to.

Is there any better way of keeping Excel current for remotely linked books to link to other than using ActiveWorkbook.Save

CODE SNIPPET:

Dim RunWhen As Double
Dim OraSession As Object
Dim OraDatabase As Object
Dim errStr As String


Public Sub WorkbookOpen()
    'Create an Oracle connection stream
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    Set OraDatabase = OraSession.OpenDatabase("domain", "username/password", 0&)
   
    'Initialization begins 1 minute after data update
    Application.OnTime Time + TimeSerial(0, 0, 30), "SaveData"
End Sub

------------------------------------------------------------------------------------------------------

Public Sub SaveData()
    On Error GoTo ErrorHandler
    
    Application.DisplayAlerts = False
   
    'Save data
    With ActiveWorkbook
        .EnableAutoRecover = False
        .Save
    End With
   
    'Continue updating
    UpdateTimer
   
Exit Sub
     
ErrorHandler:
    Debug.Print Err.Description
   
    If Err.Number = 1004 Then
        RowCount = OraDatabase.ExecuteSQL("CALL DW_STAGE.Mail_Groups('sfdev', 'ProphetXImport Error in SaveData', 'A ProphetXImport SaveAs error 1004 has occured. ')")
        Resume Next
    Else
        RowCount = OraDatabase.ExecuteSQL("CALL DW_STAGE.Mail_Groups('sfdev', 'ProphetXImport Error in SaveData', 'Please restart ProphetXImport an error has occured: ' || '" & Err.Description & "')")
        Resume Next
    End If
End Sub

------------------------------------------------------------------------------------------------------

'Name: UpdateTimer
'Purpose: This function is a timer task which calls the function UpdateData every 5 seconds.
Public Sub UpdateTimer()
    On Error GoTo ErrorHandler
   
    RunWhen = Now + TimeSerial(0, 0, 10)
    Application.OnTime earliesttime:=RunWhen, procedure:="SaveData", schedule:=True
    Exit Sub
     
ErrorHandler:
    errStr = Err.Description
    RowCount = OraDatabase.ExecuteSQL("CALL DW_STAGE.Mail_Groups('sfdev', 'ProphetXImport Error in UpdateTImer()', 'A timer error has occured: ' || '" & errStr & "')")
    Resume Next
End Sub

 

Regards...