Workbook.name property does not return the name of an excel file-CodePudding

I use the code below to write the names of the workbooks open in Microsoft Excel in an array, but it does not return the name of a freshly open excel file that was generated from SQL Server using template file (.xltm), knowing that the user account is a domain user. So, is it because of template or the domain user account? and how can I solve this problem and get the name of such file?

For Each AWB In Application.Workbooks
If AWB.Name <> ThisWorkbook.Name Then
    WB_Array(i) = AWB.Name
    i = i   1
End If
Next AWB

Thanks

CodePudding user response：

Open Workbooks to Array

Option Explicit

Sub ListWorkbookNames()

    Dim wbCount As Long: wbCount = Workbooks.Count
    
    If wbCount = 1 Then
        MsgBox "Only the workbook containing this code is open.", vbExclamation
        Exit Sub
    End If
    
    Dim WorkbookNames() As String: ReDim WorkbookNames(1 To wbCount - 1)

    Dim wb As Workbook
    Dim n As Long
    
    For Each wb In Workbooks
        If Not wb Is ThisWorkbook Then
            n = n   1
            WorkbookNames(n) = wb.Name
        End If
    Next wb
    
    MsgBox "Found the following open workbooks:" & vbLf _
        & Join(WorkbookNames, vbLf), vbInformation
    
End Sub

CodePudding user response：

Extending the great answer in the link that Tim Williams gave in the comments, if the issue is that the other workbook is open in a different instance of Excel, you won't be able to find it by just looking in Application.Workbooks. You will need to get all the open Excel.Application Objects and then check each of their Application.Workbooks collections.

Credit to Florent B. for their code. Add their code to your project. Then use the following function to collect each workbook into a Dictionary. I have included an example of how to use that function to collect all the workbook names into an array.

Sub Example()
    Dim AllWorkbooks As Object
    Set AllWorkbooks = GetAllWorkbooks
    
    'AllWorkbooks.Keys() is now an array containing the names of all open workbooks
    'AllWorkbooks.Items() is now an array of all open workbook objects
    
End Sub

Function GetAllWorkbooks() As Object
    Dim xlWorkbooks As Object
    Set xlWorkbooks = CreateObject("Scripting.Dictionary")

    Dim xl As Application
    For Each xl In GetExcelInstances()
        Dim WB As Workbook
        For Each WB In xl.Workbooks
            If Not xlWorkbooks.Exists(WB.Name) Then xlWorkbooks.Add WB.Name, WB
        Next
    Next
    
    Set GetAllWorkbooks = xlWorkbooks
End Function