printing all workbooks names in one shot

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,565
Hi
The code below is to print each workbook's name. If I have 5 then it will msgbox these 5 files names. I want to improve this code so it can print all the name in one shot (not 5 msgboxs or whatever the number of the files). Is that possible? Thank you so much.

Code:
Sub wb_names()
    'to find workbook name
    Dim x As Integer
    Dim y As Integer
    x = Workbooks.Count
    For y = 1 To x
        MsgBox Workbooks(y).Name
    Next y
End Sub
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
Code:
Sub lezawang()
  Dim asName()      As String
  Dim i             As Long

  ReDim asName(1 To Workbooks.Count)
  For i = 1 To Workbooks.Count
    asName(i) = Workbooks(i).Name
  Next i
  
  MsgBox Join(asName, vbLf)
End Sub
 

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,565
Thank you very much. The code is working perfect. I have 1 question please

Join(asName, vbLf)

Does that tell vba to join "each member" of the array with line feed? So I do not need to put that line of code in a loop. Thanks again
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
Look at VBA Help for the Join function and post back if you still have questions.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,046
Messages
5,466,243
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top