Identify extension ".xlsm:2"

jpgalasso · #1 (**permalink**) 01-11-12, 15:03

I am running Excel vba and sometimes an Excel Workbook has a new tab so the Workbook is named "NameWkBk.xlsm:2"; VBA apparently sees this as "NameWkBk.xlsm", ignores the ":2" part of the extension and gives me an error when I try to select the Workbook. Is there anyway to identify the ":2" when running VBA?

Thank you for any help.
Jim

Colin Legg · #2 (**permalink**) 01-12-12, 11:02

Hi Jim,

The :2 has nothing to do with a new tab (worksheet) nor new workbook being added. It refers to the Windows collection.

For example, if you open a new workbook in Excel, it is (probably) called Book1. If you then go to the view tab on the ribbon and click on the "new window" button, you will see in your taskbar that you get Book1:1 and Book1:2. The name of the file has not changed - it is still Book1 - but the :1 and :2 are used to distinguish the fact that you are viewing the same file twice through different windows.

So, in your VBA code, you would continue to use just the filename in the workbooks collection, ie.

Code:

Debug.Print Workbooks("NameWkBk.xlsm").Name

99% of the time you're not going to be interested in the windows themselves but, if you needed to distinguish between them for some reason, you would do so in the Windows collection, ie.

Code:

Debug.Print Windows("NameWkBk.xlsm:1").Caption
Debug.Print Windows("NameWkBk.xlsm:2").Caption

Similarly, this will list the name of each open window pertaining to the workbook the code is in:

Code:

Sub foo()
    Dim wdw As Window
    
    For Each wdw In ThisWorkbook.Windows
        Debug.Print wdw.Caption
    Next wdw
    
End Sub

Hope that helps?

jpgalasso · #3 (**permalink**) 01-12-12, 12:14

Thank you very much, Colin. Yes, "Caption" did the trick for me. I was using "Name", which I guess only works for "Workbooks" and not "Windows".

Thanks so much!!
Jim

Internet Services

Web Development

Digital Marketing

Consumer Tech