Tip of the week # 619
24 August 2020


Open several workbooks with a single click (Microsoft Excel) *

When Microsoft released Excel 2013 it dropped one functionality that I really, really miss ... Save Workspace, available in version 2010 in the Window group on the View tab. It allowed you to save several workbooks you needed for a particular task in a special workspace file (*.xlw) so that when it was time to work on the job again, you just opened the workspace file and all files would be restored in the same arrangement you left them.

Like the idea? Why not record a macro that does just that! It might look daunting, but you only have to do this once and you can save yourself a lot of time in the long run.

Here's how (in Excel 2013 and beyond):

  1. If necessary, add the Developer tab to the ribbon by right-clicking anywhere on the Quick Access Toolbar and selecting Customize the Ribbon. Then, on the right hand side of the window, tick the Developer check box under Main Tabs.
  2. On the Developer tab, in the Code group, click Record Macro.
  3. Type a name for the macro, such as workspace or several. (Avoid using the name Open.)
  4. Press TAB and hold down, say, the SHIFT key and type the letter O to assign CTRL + SHIFT + O as a keyboard shortcut to run the macro.
  5. Press TAB and select Personal Macro Workbook to make the macro available whenever you use Excel. (Make sure to click Save when you close down Excel and see a prompt to save the changes you made to the Personal Macro Workbook.)
  6. Press ENTER or click OK.
  7. Open the files you want be able to open in one go.
  8. On the Developer tab, in the Code group, click Stop Recording.

In future, you can use the shortcut key you assigned in step 4 to open the various workbooks.

Bonus tip: why not add the macro as a button on your Quick Access Toolbar?

  1. Right-click anywhere on the Quick Access Toolbar and select Customize Quick Access Toolbar.
  2. In the Excel Options dialog box, under Choose commands from, click on Macros.
  3. In the left list, double-click the newly created macro.
  4. Press ENTER or click OK.

By the way, you can still open a workspace (*.xlw) file that was created in an earlier version of Excel.

Related tips

  1. Automating repetitive tasks - tip_351.php
  2. Customizing the Quick Access Toolbar - tip_227.php
  3. Selecting commands without using your mouse - tip_220.php

.


* Unless stated otherwise, these tips were written for Microsoft Office 2010.