I appreciate that, when trying to copy over an open file, File Explorer gives me a chance to close the file and come back to resume the copy. It used to just stop, as I remember, but now it’s very nice:
I’m not sure why the message says that a “folder” is open – I’m only trying to copy one file. That’s okay though, it’s clear enough what the message means (especially if you read it quickly). So, I just go into Excel, close the workbook and then go back to File Explorer and hit the “Try Again” button. I thought it would be helpful, or at least fun, to have a complementary function in Excel – a function that closes the workbook but allows me to immediately re-open it after finishing the copy
This is useful when saving to Excel from another program, like Crystal Reports, that exports to a workbook. Generally, I do the export, look at the output workbook, see something I want to change, go back into Crystal to fiddle, and export again*. Of course I can’t do the export if the workbook is still open, so I close it (if I remember) and then re-open it after the export. This requires using Excel’s Recent Files list, and if you read my last post you know I’m not a big fan.
So my most recent addition to my personal addin is a little routine that closes the active workbook and pops up a message box that “holds its place.” That way when I come back from saving over the just-closed file, I can re-open it with the click of a button:
Here’s the code:
Dim WorkbookToClose As String
If ActiveWorkbook Is Nothing Then
MsgBox "No active workbook."
Exit Sub
End If
If ActiveWorkbook.Path = "" Then
MsgBox "This workbook has no path."
Exit Sub
End If
WorkbookToClose = ActiveWorkbook.FullName
ActiveWorkbook.Close False
If MsgBox("Re-open " & vbCrLf & WorkbookToClose & "?", vbYesNo + vbQuestion, "Re-open?") _
= VbMsgBoxResult.vbYes Then
Workbooks.Open WorkbookToClose
End If
End Sub
Note that the code closes the workbook without prompting to save, since I’m only planning to use it when I’m about to write over the workbook anyways.
And if I decide I want something even simpler, maybe I’ll just go with this:
If Application.RecentFiles.Count > 0 Then
Workbooks.Open Application.RecentFiles(1)
End If
End Sub
* I’m happy to say that we use Crystal Reports mostly as a presentation layer for SQL, so I don’t often have to deal with formulas and the like in it.
Like the idea. How about getting the routine to save the ‘modifiedDate’ of the file in question and then automatically re-open the file after it has been overwritten?
Thanks Adam. That would be interesting. Then it could be an addin and store one or more workbooks.