ExcelVBAでブックの一覧を取得

ExcelVBA 
Sub Sample1()
    Dim i As Long, tmp As String
    For i = 1 To Workbooks.Count
        tmp = tmp & Workbooks(i).Name & vbCrLf
    Next i
    MsgBox "現在開いているブックは、" & vbCrLf & tmp & "です"
End Sub

もしくは

Sub Sample2()
    Dim wb As Workbook, tmp As String
    For Each wb In Workbooks
        tmp = tmp & wb.Name & vbCrLf
    Next wb
    MsgBox "現在開いているブックは、" & vbCrLf & tmp & "です"
End Sub

ネタ元