Walter has a need to always open a particular Excel workbook in the same location on the screen and with a particular window size. He wonders if there a way to have Excel remember this location and size information upon closing the workbook so it is used when that same workbook is next opened.
One relatively simple solution is to position your workbook (or, often, workbooks) as you want them on the screen and then save the workbooks as a workspace. You do this by displaying the View tab of the ribbon and clicking the Save Workspace tool. It should be noted, though, that this approach works only in Excel 2007 and Excel 2010-the ability to create workspaces was removed from Excel 2013, although you can still open them.
For a solution that works in all modern versions of Excel you’ll need to use a macro. You can set up a macro that automatically runs when you close the workbook and another that runs when you open it. The one that runs when you close can save positioning information, and then the one that runs when you open can retrieve that information and use it to locate where the workbook should be displayed.
The question, though, is where to save the positioning information. One solution is to simply write it out into a text file in the same folder where the workbook is stored. That’s the approach taken in the following pair of macros.
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim fileName As String Dim myWindow As Window Set myWindow = ActiveWindow fileName = "config.txt" Open fileName For Output As #1 With myWindow Write #1, .Top Write #1, .Left Write #1, .Height Write #1, .Width End With Close #1 End Sub
Private Sub Workbook_Open() Dim inputStr As String Dim fileName As String Dim myWindow As Window Set myWindow = ActiveWindow fileName = "config.txt" If Dir(fileName) "" Then Open fileName For Input As #1 With myWindow .WindowState = xlNormal Line Input #1, inputStr .Top = inputStr Line Input #1, inputStr .Left = inputStr Line Input #1, inputStr .Height = inputStr Line Input #1, inputStr .Width = inputStr End With Close #1 End If End Sub
The macro that runs when the workbook is closed saves the four positioning locations (.Top, .Left, .Height, and .Width) into a file called config.txt. This file is then read when the workbook is next opened and the appropriate properties for the workbook window are set. If the config.txt file is missing for some reason, then the workbook opens at its default location.
This approach works great if you have a single workbook in the folder that uses the positioning information. If you have multiple ones, each would be overwriting the config.txt file, and you would likely end up with workbooks not being opened where you wanted them.
For some folks, a better approach may be to store the positioning locations in the workbook itself, in named references. That’s the approach taken in the following macros.
Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next With ThisWorkbook With .Names .Add Name:="WinTop", RefersToR1C1:="=1" .Add Name:="WinLeft", RefersToR1C1:="=1" .Add Name:="WinWidth", RefersToR1C1:="=1" .Add Name:="WinHeight", RefersToR1C1:="=1" End With On Error GoTo 0 .Names("WinTop").RefersTo = ActiveWindow.Top .Names("WinLeft").RefersTo = ActiveWindow.Left .Names("WinWidth").RefersTo = ActiveWindow.Width .Names("WinHeight").RefersTo = ActiveWindow.Height End With End Sub
Private Sub Workbook_Open() With ThisWorkbook ActiveWindow.Top = Val(Mid(.Names("WinTop").RefersTo, 2)) ActiveWindow.Left = Val(Mid(.Names("WinLeft").RefersTo, 2)) ActiveWindow.Width = Val(Mid(.Names("WinWidth").RefersTo, 2)) ActiveWindow.Height = Val(Mid(.Names("WinHeight").RefersTo, 2)) End With End Sub
With this approach you can easily transfer the workbook anywhere you want and the macros take care of saving and reusing the positioning information for the workbook window.