vba 保存、关闭并重新打开“本工作簿”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22379546/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Save, Close and Re-Open "ThisWorkbook"
提问by Trenera
I need to Save, Close and Re-Open "ThisWorkbook". The code should be something like this:
我需要保存、关闭并重新打开“ThisWorkbook”。代码应该是这样的：
Sub reopen()
Dim wb As Excel.Workbook
Set wb = ThisWorkbook
wb.Save
wb.Close
wb.Open
End Sub
Unfortunately, there is no such command "wb.Open", and once I "wb.Close", the code stops :)
不幸的是，没有这样的命令“wb.Open”，一旦我“wb.Close”，代码就会停止:)
P.S. It should be a part of a bigger one, which gives an error "91" if the workbook isn't saved-closed and reopened...
PS它应该是更大的一部分，如果工作簿没有保存关闭并重新打开，则会出现错误“91”......
回答by Trenera
Sub reopen()
Dim wb As Excel.Workbook
Set wb = ThisWorkbook
Dim pth As String
pth = wb.FullName
Application.OnTime Now + TimeValue("00:00:01"), Application.Workbooks.Open(pth)
wb.Close (True)
End Sub
回答by B Hart
Depending on your goals there are two possible options that I know of right off hand.
根据您的目标，我知道有两种可能的选择。
First: Save, Close and ReOpen from the same routine:
第一：从同一个例程中保存、关闭和重新打开：
Sub SaveCloseReOpen()
Dim strCMD As String
strCMD = "CMD /C PING 10.0.0.0 -n 1 -w 5000 >NUL & Excel.exe " & Chr(34) & ThisWorkbook.FullName & Chr(34)
ThisWorkbook.Save
Shell strCMD, vbNormalFocus
If Application.Workbooks.Count = 1 Then
Application.Quit
Else
ThisWorkbook.Close SaveChanges:=False
End If
End Sub
Basically the workbook will save itself then run a Shell CMD to Ping a Non-Routable IP waiting for 5 seconds (You can use Timeout or something else if you prefer) then it will Execute Excel.exe and reopen the the workbook. Depending on how many workbooks are open it will Close the Excel Application or just the workbook... Though this method will open the Excel Application in a Separate Instance. If you want the same instance, then I would use the Application.OnTime Method.
基本上，工作簿将自动保存，然后运行 Shell CMD 以 Ping 一个不可路由的 IP 等待 5 秒（如果您愿意，可以使用超时或其他方式），然后它将执行 Excel.exe 并重新打开工作簿。根据打开的工作簿数量，它将关闭 Excel 应用程序或仅关闭工作簿...虽然此方法将在单独的实例中打开 Excel 应用程序。如果您想要相同的实例，那么我将使用 Application.OnTime 方法。
Second: Save, Close from one routine But ReOpen through the Workbook Deactivate Event:
第二：保存，从一个例程关闭但通过工作簿停用事件重新打开：
In the Workbook Module:
在工作簿模块中：
Option Explicit
Private Sub Workbook_Deactivate()
If bClose_ReOpen Then
Shell "CMD /C PING 10.0.0.0 -n 1 -w 5000 >NUL & Excel " & Chr(34) & ThisWorkbook.FullName & Chr(34), vbNormalFocus
If Application.Workbooks.Count = 1 Then
Application.Quit
End If
End If
End Sub
Then in a Regular Module:
然后在常规模块中：
Option Explicit
Public bClose_ReOpen As Boolean
Sub SaveCloseReOpen()
bClose_ReOpen = True
ThisWorkbook.Close SaveChanges:=True
End Sub
Similar to the First Routine; however, this routine will execute the Shell Run command (via Public Boolean Variable) after the Workbook has mostly closed... If you have any Before_Save or Before_Close Events, they will run first as the Workbook_Deactivate Routine is the last one processed before the workbook actually closes.
类似于第一套路；但是，此例程将在工作簿大部分关闭后执行 Shell Run 命令（通过公共布尔变量）...如果您有任何 Before_Save 或 Before_Close 事件，它们将首先运行，因为 Workbook_Deactivate 例程是工作簿之前处理的最后一个实际上关闭。
You can always change the vbNormalFocus for the Shell CMD to vbHide if you prefer.
如果您愿意，您可以随时将 Shell CMD 的 vbNormalFocus 更改为 vbHide。
