CODEWITHSUNDEEP
vbaexcel
Trenera
Trenera

Reputation: 1505

Save, Close and Re-Open "ThisWorkbook"

I need to Save, Close and Re-Open "ThisWorkbook". The code should be something like this:

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 :)

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...

Upvotes: 1

Views: 21713

Answers (2)

B Hart
B Hart

Reputation: 1118

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.

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.

You can always change the vbNormalFocus for the Shell CMD to vbHide if you prefer.

Upvotes: 4

Trenera
Trenera

Reputation: 1505

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

Upvotes: 4

Related Questions