View original (French)

List open Excel files

jo2214 -  
Hello,
does anyone know how to list all open Excel files (even unsaved ones) in VBA?

I’m testing this code which works well with the current Excel instance (the one with this workbook)
but I can't see the open files from other Excel instances! (on the same Windows session)

For Each Workbook In Application.Workbooks
CreationDate = Workbook.BuiltinDocumentProperties("Creation Date")
debug.print Workbook.Name, CreationDate
Next

(Excel 2000 and later)
thank you for your help
Configuration: Windows XP Firefox 1.5.0.1

5 answers

  1. RailBird
    Sub Test_Class_Ouvert()

    For i = 1 To Workbooks.Count

    ActiveSheet.Cells(i, 1) = Workbooks(i).Name

    Next i

    End Sub

    This allows you to retrieve the names of the open workbooks and copy them into the cell, however, it does not display "unsaved" workbooks, I don't know if a condition needs to be added.
    10
    1. michel_m Contributor
      Hello,

      this function returns a boolean indicating whether the workbook is open (true) 

      <code>Function FichOuvert(F As String) As Boolean 'According to Didier_mdf Dim Wk As Workbook On Error Resume Next Set Wk = Workbooks(F) On Error GoTo 0 FichOuvert = Not Wk Is Nothing End Function
      </code>
      --
      Michel
      0
      1. jo2214
        Thank you,
        but the goal is to select the last opened workbook from all instances of Excel:
        it seems that each instance only detects its own workbooks
        and the code below works very well for that 
         For Each Workbook In Application.Workbooks DateWorkbook = Workbook.BuiltinDocumentProperties("Creation Date") debug.print Workbook.Name, DateWorkbook Next

        perhaps an object like "excel.application" exists under another name?
        0
        1. michel_m Contributor
          Yet you asked:

          ..."does anyone know how to list all open Excel files in VBA" ...

          Have a good weekend
          --
          Michel
          0
          2. xpulse
            Hello,

            I'm making a big follow-up after more than 6 months because I'm encountering the same problem:

            looping through all the open workbooks from all the Excel applications opened on the PC

            basically something like:

            Dim Exapp As Excel.Application
            Dim WB As Workbook

            For Each Exapp In (Excel.applicationS)

            For Each WB In Workbooks
            If WB.Name = NomFichier Then
            OuvertureFichier = True
            End If
            Next

            Next

            is it possible this way and if so, what is the exact syntax or should the problem be approached differently?
            0