Excel 2013 – Save Workspace

With the leap up to MS Office 2013 at work I have discovered the “joys” of the ribbon. After a while I have decided that I can live with it, everything just takes more time – where was that; and, where can they have hidden it now.

One thing that did stick out though was that Microsoft had discontinued the Save Workspace function in the Office 2013. Save Workspace would create a file (*.xlw) based on the currently open workbooks and their layout. This is useful if you are in the middle of something and are interrupted. When you get back just click of the workspace file and everything is back to the way it was.

Now the layout of workbooks doesn’t worry me; I tend to have them maximised in the Excel window unless I am doing specific. So how do you get the Save piece back. Well the answer is a couple of bits of VBA, one to Save a list of open files, and another to Open the files in the list.


Sub SaveWorkspace2013()

Dim fso As Object
Set fso = CreateObject(“Scripting.FileSystemObject”)
Dim oFile As Object
Set oFile = fso.CreateTextFile(“D:\resume.txt”)
Dim wb As Workbook

‘lists each OPEN Workbook

For Each wb In Application.Workbooks
If wb.Name <> “PERSONAL.XLS” Then
oFile.WriteLine wb.FullName
End If
Next wb

oFile.Close
Set fso = Nothing
Set oFile = Nothing

End Sub


Sub OpenWorkSpace2013()

Dim objFSO As Object
Dim objWB As Workbook
Dim strFN As String
Dim objTF As Object
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objTF = objFSO.OpenTextFile(“D:\resume.txt”)
On Error Resume Next
Do While Not objTF.AtEndOfStream
strFN = objTF.readline()
Set wb = Workbooks.Open(strFN)
If wb Is Nothing Then
Debug.Print strFN
Else
Set wb = Nothing
End If
Loop
On Error GoTo 0
End Sub

Neither of these are pretty, but they work! The Save function simply creates a text file list of open workbooks (D:\resume.txt). While the Open function looks for that list, reads it and tries to open the listed files.

At the moment having a single saved workspace is enough, but we will see how I go.