多人共用活頁簿如何自動存檔(64位元)

如果開啟Excel共用活頁簿的功能

會鎖住巨集或增益集中有關存檔的功能

也就是執行workbook.save的時侯會出現錯誤訊息

但我意外發現workbook.saveas不會被擋

所以可以寫成以下


Function workbook_index(wb As Workbook) As Integer '自定函數以抓出workbook的index(編碼)，因為有可能一次開啟多個excel
Dim i As Integer
    For i = 1 To Workbooks.Count
        If Workbooks(i).Name = wb.Name Then
        workbook_index = i
        Exit For
        End If
    Next i
End Function

Sub AutoSave()  '開始執行存檔過程
  Dim myWb As Workbook
  Dim myFileName As String
  Dim Myfile2
  Name = ActiveWorkbook.Name
  myFileName = ThisWorkbook.Path & "\" & Name
  Set myWb = Workbooks(workbook_index(ActiveWorkbook)) '抓出目前執行的workbook
  Application.DisplayAlerts = False '不要跳出警告視窗
  myWb.SaveAs Filename:=myFileName '將檔案另存到自已
  Application.DisplayAlerts = True '再將警告視窗功能開啟
  Myfile2 = FileDateTime(ThisWorkbook.Path & "\" & Name)
  Application.DisplayStatusBar = True '開啟狀態列功能
  Application.StatusBar = oldName & "上次存檔的時間為：" & Myfile2  '狀態列可以顯示上次存檔時間
  Set myWb = Nothing '釋放內存
  Call BTimer
End Sub

Sub BTimer() '設定時間，每 55秒存檔一次
    Application.OnTime Now() + TimeValue("00:00:55"), "ThisWorkbook.autosave"
End Sub

PS. 32位元的office可以直接裝autosave.xla這個增益集就可以自動存檔