[問題] 關於VBA 自動存檔的問題

作者skymt333 (..............)

看板Office

標題[問題] 關於VBA 自動存檔的問題

時間Wed Mar 30 14:46:18 2022

我在A檔案寫入 Private Sub workbook_open() Call AAAsave End Sub Private Sub AAASsave() Application.OnTime Now + TimeValue("00:00:10"), "ThisWorkBook.AAA" End Sub Private Sub AAA() ThisWorkbook.save Call AAAsave End Sub 我在B檔寫入 Private Sub workbook_open() Call BBBsave End Sub Private Sub BBBsave() Application.OnTime Now + TimeValue("00:00:15"), "ThisWorkBook.BBB" End Sub Private Sub BBB() ThisWorkbook.save Call BBBsave End Sub 但現在發生一個問題，兩個檔案一起打開後，我關掉B檔案後，可是當A檔案自動存檔時， B檔案就會被開啟。我關掉A檔後，當B檔案自動儲存開始時，關掉的A檔就會被開啟。可否有人能指點一下小弟原因，或是這樣寫錯在哪，萬分感謝 -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 1.164.3.34 (臺灣) ※ 文章網址: https://www.ptt.cc/bbs/Office/M.1648622780.A.3B4.html

→ waiter337: Private Sub AAASsave() 03/30 20:48

→ waiter337: Set wb1 = Nothing 03/30 20:49

→ waiter337: On Error Resume Next 03/30 20:49

→ waiter337: Set wb1 = Workbooks("a1.xlsm") 03/30 20:49

→ waiter337: On Error GoTo 0 03/30 20:49

→ waiter337: If Not wb1 Is Nothing Then 03/30 20:49

→ waiter337: Application.OnTime Now + TimeValue("00:00:10"), "Th 03/30 20:49

→ waiter337: isWorkBook.AAA" 03/30 20:50

→ waiter337: End If 03/30 20:50

→ waiter337: End Sub 03/30 20:50

→ waiter337: Private Sub BBBsave() 也仿照改檔名要改 03/30 20:50

推 waiter337: 還是不行的話就改下面這個 03/30 20:59

→ waiter337: Private Sub AAA() 03/30 21:02

→ waiter337: Set wb1 = Nothing 03/30 21:02

→ waiter337: On Error Resume Next 03/30 21:02

→ waiter337: Set wb1 = Workbooks("a1.xlsm") '這個檔案的檔名 03/30 21:02

→ waiter337: On Error GoTo 0 03/30 21:02

→ waiter337: If Not wb1 Is Nothing Then 03/30 21:02

→ waiter337: ThisWorkbook.Save 03/30 21:02

→ waiter337: Call AAAsave 03/30 21:02

→ waiter337: End If 03/30 21:03

→ waiter337: End Sub 03/30 21:03

→ waiter337: 另外補充.... 03/30 21:04

→ waiter337: 你的這個寫法屬於連續觸發的寫法 03/30 21:04

→ waiter337: aaasave() 跟aaa() 原本就是一套被你分開寫了 03/30 21:05

→ waiter337: 原本應該是這樣寫的 03/30 21:05

→ waiter337: Private Sub AAAsave() 03/30 21:06

→ waiter337: ThisWorkbook.save 03/30 21:06

→ waiter337: Application.OnTime Now + TimeValue("00:00:10"), 03/30 21:06

→ waiter337: "ThisWorkBook.AAAsave" ,False 03/30 21:07

→ waiter337: End Sub 03/30 21:07

→ waiter337: 所以最後可以改成 03/30 21:09

→ waiter337: Private Sub AAAsave() 03/30 21:09

→ waiter337: Set wb1 = Nothing: On Error Resume Next 03/30 21:09

→ waiter337: Set wb1 = Workbooks("a1.xlsm"): On Error GoTo 0 03/30 21:09

→ waiter337: If Not wb1 Is Nothing Then 03/30 21:09

→ waiter337: ThisWorkbook.Save 03/30 21:09

→ waiter337: Application.OnTime Now + TimeValue("00:00:10"), _ 03/30 21:09

→ waiter337: "ThisWorkBook.AAAsave", False 03/30 21:09

→ waiter337: End If 03/30 21:09

→ waiter337: End Sub 03/30 21:10

→ waiter337: 總之就是加入了一個判斷工作簿是否開啟才能觸發定時器 03/30 21:14

→ skymt333: 照W大您的方式修改後好像還是停不住 03/30 23:44

→ skymt333: 但還是感謝您指出問題點，我再研究一下如何改感謝 03/30 23:46

→ waiter337: https://youtu.be/yQP_YeoL9xg 03/31 10:03

→ waiter337: https://youtu.be/R3Qp1ltyYvM 03/31 10:11

→ skymt333: 還讓您弄教學影片，真是不好意思，我晚上下班後研究，感 03/31 13:57

→ skymt333: 謝啊 03/31 13:57