Workbook.name属性不返回excel文件的名称

Question

我使用下面的代码将在Microsoft Excel中打开的工作簿的名称写入数组，但它不会返回新打开的Excel文件的名称，该文件是使用模板文件(.xltm(从SQL Server生成的，因为我知道用户帐户是域用户。那么，是因为模板还是域用户帐户？我如何解决这个问题并获得这样的文件的名称？

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

感谢

Answer 1

扩展Tim Williams在评论中给出的链接中的好答案，如果问题是另一个工作簿在不同的Excel实例中打开，那么仅在Application.Workbooks中查找将无法找到它。您需要获取所有打开的Excel.Application对象，然后检查它们的每个Application.Workbooks集合。

这要归功于Florent B.的代码。将他们的代码添加到您的项目中。然后使用以下函数将每个工作簿收集到字典中。我已经包含了一个如何使用该函数将所有工作簿名称收集到数组中的示例。

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

Answer 2

将工作簿打开到数组

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