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

    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.
    Jim

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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?

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

Posting Permissions

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