hello

[Gabrysia]

I have a problem. How to get the names of sheets from closed workbook using VBA? I can't use Power Query or Microsoft Query... Id like to use VBA.

Thanks in advance.

 

[dotchiejack]

Hi,
Put this in a normal module.

Option Explicit
Public Sub DemoGetSheetNames()
    Dim lNumEntries As Long
    Dim szFullName As String
    Dim szFileSpec As String
    Dim aszSheetList() As String
    Sheet1.UsedRange.Clear
    szFileSpec = "Excel Files (*.xl*),*.xl*"
    szFullName = CStr(Application.GetOpenFilename(szFileSpec, , "Select an Excel File"))
    If szFullName <> CStr(False) Then
        GetSheetNames szFullName, aszSheetList()
        lNumEntries = UBound(aszSheetList) - LBound(aszSheetList) + 1
        Sheet1.Range("A1").Resize(lNumEntries).Value = Application.WorksheetFunction.Transpose(aszSheetList())
        Sheet1.Range("A1").EntireColumn.AutoFit
    End If
End Sub
Private Sub GetSheetNames(ByRef szFullName As String, ByRef aszSheetList() As String)
    Dim bIsWorksheet As Boolean
    Dim objConnection As ADODB.Connection
    Dim rsData As ADODB.Recordset
    Dim lIndex As Long
    Dim szConnect As String
    Dim szSheetName As String
    Erase aszSheetList()
    If Application.Version < 12 Then
        szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & szFullName & ";Extended Properties=Excel 8.0;"
    Else
        szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & szFullName & ";Extended Properties=Excel 8.0;"
    End If
    Set objConnection = New ADODB.Connection
    objConnection.Open szConnect
    Set rsData = objConnection.OpenSchema(adSchemaTables)
    Do While Not rsData.EOF
        bIsWorksheet = False
        szSheetName = rsData.Fields("TABLE_NAME").Value
        If Right$(szSheetName, 1) = "$" Then
            szSheetName = Left$(szSheetName, Len(szSheetName) - 1)
            bIsWorksheet = True
        ElseIf Right$(szSheetName, 2) = "$'" Then
            szSheetName = Left$(szSheetName, Len(szSheetName) - 2)
            szSheetName = Right$(szSheetName, Len(szSheetName) - 1)
            bIsWorksheet = True
        End If
        If bIsWorksheet Then
            szSheetName = Replace$(szSheetName, "''", "'")
            ReDim Preserve aszSheetList(0 To lIndex)
            aszSheetList(lIndex) = szSheetName
            lIndex = lIndex + 1
        End If
        rsData.MoveNext
    Loop
    rsData.Close
    Set rsData = Nothing
    objConnection.Close
    Set objConnection = Nothing
End Sub

 

[JustynaMK]

Hi Gabrysia,

There is a very useful thread here on MrExcel forum:

https://www.mrexcel.com/forum/excel-questions/47074-get-worksheet-names-closed-workbook.html

Hope you will find it useful. Take care.

 

[Gabrysia]

thank You very much

 

[Gabrysia]

I found another solution of my problem:

Sub names_of_sheets()
Dim sh As Object
n = "C:\Users\USER\Desktop\n.xlsx"
Set sh = GetObject.Worksheets


For Each Item In sh
MsgBox Item.Name


Next Item
End Sub