Workbooks in a folder and list the worksheets

Re: Workbooks in a folder and list the worksheets

Hi Mech

Welcome to ozgrid. Give the following a try. Change the path to suit and don't forget to include the backslash.

Take care

Smallman

Option ExplicitSub OpenName()
    Dim sPath As String
    Dim sFil As String
    Dim strName As String
    Dim twbk As Workbook
    Dim owbk As Workbook
    Dim ws As Worksheet


    Set twbk = ActiveWorkbook
    sPath = "C:\Users\HYMC\Excel\Test\ZZZ\Now\" 'Change to suit
    sFil = Dir(sPath & "*.xls")


    Do While sFil <> ""
        strName = sPath & sFil
        Set owbk = Workbooks.Open(strName)
        twbk.Sheets("Sheet1").Range("A65536").End(xlUp)(2) = sFil
            For Each ws In Worksheets
               twbk.Sheets("Sheet1").Range("A65536").End(xlUp)(2) = ws.Name
            Next
        owbk.Close False 'Close
        sFil = Dir
    Loop
     
End Sub

Re: Workbooks in a folder and list the worksheets

Open the VB Editor (Alt + F11)
Menu > Insert > Module (not class module)

Copy and paste the code into the VB Editor
Adjust the code for the correct file path

Close the VB Editor (Alt + Q)

Invoke the Macro Dialog to run the code (Alt + F8)

Re: Workbooks in a folder and list the worksheets

hi Smallman,

How can I make the file name to be written in blue and bold font?

Re: Workbooks in a folder and list the worksheets

Hi, Smallman

I made it. Thanks for VBA code.

Re: Workbooks in a folder and list the worksheets

Hi Ingo

If your lines to do what you requested look like the below you are on the right track. Included before the For Statement.

twbk.Sheets("Sheet1").Range("A65536").End(xlUp)(2).Font.Color = vbBlue
    twbk.Sheets("Sheet1").Range("A65536").End(xlUp)(2).Font.Bold = True

Take care

Smallman