Access VBA - Function to create an array with the names of all the worksheets of a selected excel workbook

Question

this is one of my first projects. I am using Access trying to upload all the worksheets of an excel (which I select) as table to the Database. To do so I am creating a function that I recall in my procedure; this function should read the names of the worksheets in my excel file and save them in an array/collection (which one do you suggests? the number of worksheets is not fixed). this is my code for the moment but it doesn't work propelrly because i find in my array only the last worksheet and not all the previous ones:

Function Get_Sheetsname_Array(xlsfile) Dim sheetsLst As Collection Dim lookupWB As Excel.Application Dim txt As String

Set lookupWB = New Excel.Application
lookupWB.Workbooks.Open xlsfile

toIndex = lookupWB.Worksheets.Count

Dim i As Integer

With lookupWB
    For Each wrksheet In .Worksheets
        sheetsLst = Array(.xlSheet.Name)
    Next wrksheet
End With


Get_Sheetsname_Array = sheetLst

End Function

David G David G · Accepted Answer · 2017-05-23T11:07:39

Your code currently overwrites each sheet name, so only the last is saved in the variable. You need to add them to the array without overwriting. Something like this:

Sub SaveSheetsIntoArray()
    Dim shArray, i
    ReDim shArray(1 To Sheets.count)
    For i = 1 To Sheets.count
        shArray(i) = ThisWorkbook.Sheets(i).Name
        Debug.Print shArray(i)
    Next
    'do something with the array "shArray"
End Sub

Also, add an "Option Explicit" as the first line in your VBA code, if not already there.

Access VBA - Function to create an array with the names of all the worksheets of a selected excel workbook

1 Answers