1

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

Sami
  • 13
  • 4
  • 1
    If that newly created workbook was open in a second Excel existing session, this will fail. – ALeXceL Aug 31 '22 at 17:14
  • 4
    See here for how to get all open Excel instances - https://stackoverflow.com/questions/30363748/having-multiple-excel-instances-launched-how-can-i-get-the-application-object-f Once you have those instances you can enumerate all open workbooks. – Tim Williams Aug 31 '22 at 17:25
  • @TimWilliams Thanks for that great link. This is why I love coming on Stack Overflow, there are always nuggets of gold to find among the many great answers on this site. I wouldve never been able to come up with those functions on my own. +1 for you and Florent B. – Toddleson Aug 31 '22 at 18:38

2 Answers2

0

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
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
0

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
Toddleson
  • 4,321
  • 1
  • 6
  • 26
  • Thanks a lot @Toddleson. Now I can get the name of the workbook that I need. I added this statement to display the names of open workbooks in a list box `Sheet1.ListBox1.AddItem WB.Name` . and then the selected workbook is assigned to a public variable using this line `Sel_Rpt = Sheet1.TextBox1.Value` . But then I get error when I run a sub that contains this line `Set Rpt = Workbooks(Sel_Rpt).Worksheets("Report")` for defining the workbook that will be used within the sub. – Sami Sep 02 '22 at 18:06
  • @Sami You're running into the same issue. The workbook is not in your default `Workbooks` collection because its open in another instance of Excel. When you're looping through the applications, you need to build a custom collection of workbooks, so you have every workbook from every application accessible to you, like the custom Dictionary object created by my `GetAllWorkbooks` function. – Toddleson Sep 02 '22 at 18:27
  • @Sami This will work `Set Rpt = GetAllWorkbooks(Sel_Rpt).Worksheets("Report")` – Toddleson Sep 02 '22 at 18:28
  • Thanks again @Toddleson. I don't know much about Dictionary, so I don' t know how to use it properly. But let me explain more how it should work. A List Box shows all the workbooks (including the ones open in another instance) , then after the required workbook get selected , the contents (sheets, ranges, cells,...,etc.) can be manipulated using different Subs and Functions. Do you have any idea how to do this? Thanks again – Sami Sep 03 '22 at 19:16
  • @Sami Yes. You need to first set up your subs and functions to not have any reliance on `ActiveSheet` or `ActiveWorkbook`. Pass arguments to them so they have everything they need. Then you can use `GetAllWorkbooks` to have a dictionary of all open workbook objects. You can retrieve them from the dictionary using their name or using their index. Pass the workbook object to your subs and functions, so they can operate on it. If you have more troubles, post it as a new question so that it can get a full answer. – Toddleson Sep 06 '22 at 13:45
  • I have just posted a new question about this problem. thanks in advance. @Toddleson – Sami Sep 10 '22 at 22:30