A new instance of Excel opens Book, Book1, Book2, etc. however with Book.xlsx in XLSTART I get 'locked for editing' (no network sharing)

[cpcp]

I've searched the forum on this issue and can only find instances of this error where network sharing is enabled; in my case there is no network sharing (i.e. the Server service is never started). You will be familiar with the behaviour on opening multiple instances of Excel 2010 where the new Blank Workbook shown changes automatically from Book.xlsx to Book2.xlsx then Book3.xlsx and so on. I want to customize the default Blank Workbook (lets call it Zbook.xlsx) and every time I open an instance of Excel have it load the same way as above i.e. Zbook.xlsx, Zbook1.xlsx, Zbook2.xlsx. etc. To achieve this I have tried saving Zbook.xlsx in XLSTART however on the second and subsequent instance of Excel I get the error "Zbook.xlsx is locked for editing". Clearly, on this occasion, Excel can't create a Zbook2.xlsx. Without having to click through some sequence (like File>New>MyTemplates) how can I have Excel automatically open a sequentially numbered Workbook? For example is there a way to permanently change the makeup of the default Blank Workbook? Thank You!

 

[Jaafar Tribak]

Hi,

You should be able to solve this via the use of an AddIn.

1- Create a new blank workbook and place the following code in the ThisWorkbook Module:

Option Explicit

Private WithEvents AppEvents As Application

Private Sub Workbook_Open()
    Set AppEvents = Application
    If Len(Dir(Application.TemplatesPath & "Zbook.xlt")) = 0 Then
        FileCopy Application.TemplatesPath & "Book1.xlt", Application.TemplatesPath & "Zbook.xlt"
    End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not Me.ReadOnly Then Kill Application.TemplatesPath & "Zbook.xlt"
End Sub

Private Sub AppEvents_NewWorkbook(ByVal Wb As Workbook)
    Wb.Close
    Workbooks.Add Application.TemplatesPath & "Zbook.xlt"
End Sub

2- Now set the IsAddin Property of the workbook to TRUE via the VBE Properties Window and then save the workbook as an AddIn *.xla in the default addins folder

3- Close the application.

Next time you launch excel or open a new workbook, the workbook name will default to Zbook1,Zbook2,Zbook3 etc

This should work when working with multiple excel instances as well.