Get sheetnames

[usl]

Hi Need a bit help,
I have seen several entries here how to pick the sheet new from the table where I actually am or the other sheets in the excelfile.

But I need a macro to show me the sheetnames from another file.
so bascally I need to enter the path (A1), and file name in B1
and in e.g. "C5" and down the sheetnames will then appear.

Have any of got an Idea how to do that ?

Thanks a lot in advance
Uli

 

[stanleydgromjr]

usl,

See this link 'Get the sheetnames from closed workbooks':
[url]http://excelkb.com/article.aspx?id=10098
[/url]

Have a great day,
Stan

 

[Davers]

oops..double post..sorry!

Dave

 

[Davers]

I've used this method and it works for me. It does open the workbook, gets the sheet names, then closes the opened workbook. The code assumes you've entered the directory in cell A2, the workbook name in cell B2, and it will list the Sheets starting in cell C2. It also assumes you have headers in cells A1, B1, and C1. I do have an example I can send to you if you like. Here is the code:

Sub getSheetNames()

Dim i As Integer
Dim c As Integer
Dim mySht As String
Dim myBk As String
Dim myDir As String
Dim ThisBook As String

myBk = ActiveWorkbook.Worksheets("Sheet1").Range("B2").Value
myDir = ActiveWorkbook.Worksheets("Sheet1").Range("A2").Value
ThisBook = ActiveWorkbook.Name

Workbooks(ThisBook).Worksheets("Sheet1").Range("C2:C" & Range("A65536").End(xlUp).Row).ClearContents

Workbooks.Open myDir & myBk
c = Workbooks(myBk).Sheets.Count

For i = 1 To c

NextRow = Application.WorksheetFunction.CountA(Workbooks(ThisBook).Worksheets("Sheet1").Range("C:C")) + 1

mySht = Workbooks(myBk).Sheets(i).Name
    
    If Workbooks(myBk).Sheets(i).Visible = xlSheetVisible Then
        Workbooks(ThisBook).Worksheets("Sheet1").Cells(NextRow, 3) = mySht
    End If
Next

Workbooks(myBk).Close

End Sub

If you'd like a copy of this workbook..feel free to email me!

I hope this helps!

Cheers!

Dave