方法一:
Private Sub GetSheetName()
  Set VBEXCEL = CreateObject("Excel.Application")
    VBEXCEL.Workbooks.Open CurrentProject.Path & "\表名.xls"
    For i = 1 To VBEXCEL.Worksheets.Count
        Debug.Print VBEXCEL.Worksheets(i).Name
    Next
    VBEXCEL.Quit
    Set VBEXCEL = Nothing
End Sub

方法二:
Sub Main()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
   
    Set cnn = New ADODB.Connection
    cnn.Open "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\Book1.xls;DefaultDir=C:\"
   
    Set rst = cnn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, Empty))
    While Not rst.EOF
        Debug.Print rst!TABLE_NAME
        rst.MoveNext
    Wend
   
    Set rst = Nothing
    Set cnn = Nothing
End Sub