Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004

    Unanswered: Identify extension ".xlsm:2"

    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.

  2. #2
    Join Date
    Sep 2008
    London, UK
    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.
    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.
    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:
    Sub foo()
        Dim wdw As Window
        For Each wdw In ThisWorkbook.Windows
            Debug.Print wdw.Caption
        Next wdw
    End Sub
    Hope that helps?

  3. #3
    Join Date
    Feb 2004
    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!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts