How to get the list of sheet names of ms-excel?

Question

I have an excel file which has a lot of sheets. How could I get a list of sheet names of ms-excel?

(PS: The office is mac version.)

Answer 1

Try using the following macro:

Sub ListWorkSheetNames()

For i = 1 To Sheets.Count
Range("A" & i) = Sheets(i).Name
Next i

End Sub

For me - works quite well.

(source)

Answer 2

To get the same as above but additionally with hyperlinks taking you to the sheet:

Sub ListWorkSheetNames()

For i = 1 To Sheets.Count
Range("A" & i) = "=HYPERLINK('" + Sheets(i).Name + "'!A1, """ + Sheets(i).Name + " "")"
Next i

End Sub