Opening a workbook using array values

Question

I'm trying to open workbooks using array values stored.

The only bug I encounter is at Workbooks.Open(StoreList(0)) where I encounter

"run-time error 0 subscript out of range"

but the address is correct.

Sub uniquestores()
    '
    ' uniquestores Macro
    '
    Dim lastrow As Long
    Dim x, y, z, uniquestores As Integer
    Dim StoreList(), paths() As String
    Dim csv, strPath As String
    Dim wbs As Workbook
     
    strPath = "C:\Users\j.tung\Downloads\Excel VBA and Macros\SWIMLANE TAGGING\"
    csv = ".csv"
    'to count how many rows have data using column F

    Sheets("Sheet1").Select
    ActiveSheet.Range("F1").Select
    Range(Selection, Selection.End(xlDown)).Select
    lastrow = Selection.Cells.Count
    
    'add new sheet and get the list of unique stores and count how many there are
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "uniquestorecount"
    Range("A1").Formula2 = "=UNIQUE(Sheet1!F2:F" & lastrow & ")"
    uniquestores = Cells(Rows.Count, "A").End(xlUp).Row
        
    'to check
    Range("D1").Value = uniquestores
    Range("E1").Value = lastrow
    Range("F1").Value = strPath
        
    'to get only the store name. without the "pandamart ()"
    For y = 0 To uniquestores - 1
        Range("B" & y + 1).FormulaR1C1 = "=MID(RC[-1],12,LEN(RC[-1])-12)"
    Next y
    
    'to store the store names in an array. also adds ".csv" to be able to get workbook name
    For x = 0 To uniquestores - 1
        ReDim StoreList(x To uniquestores)
        StoreList(x) = strPath & Range("B" & x + 1) & csv
        Range("C" & x + 1).Value = StoreList(x)
    Next x
    
    'delete the sheet to get the store names.  a pop-up will appear to ask if you really want to delete it.
    'application.display will be triggered not to pop-up and it will automatically say "okay" to delete
    Application.DisplayAlerts = False
    Sheets("uniquestorecount").Delete
    Application.DisplayAlerts = True
    
    'For z = 0 To uniquestores - 1
    'paths(x) = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Chapter " + CStr(x))
    'Application.DisplayAlerts = False
    'paths(0) = Application.GetOpenFilename(FileFilter:="Microsoft Excel Comma Separated Values File (*.CSV), *.CSV")
    Workbooks.Open(StoreList(0))
    'Application.DisplayAlerts = True
    'Next z
    
End Sub

Answer 1

Use a Dictionary Object and avoid creating a temporary sheet.

Option Explict
Sub UniqueStores2()
    ' uniquestores Macro
    Const FOLDER = "C:\Users\j.tung\Downloads\Excel VBA and Macros\SWIMLANE TAGGING\"
    Const EXT = ".csv"
    Const PREFIX = "pandamart ()"
    
    'to count how many rows have data using column F
    Dim ar, lastrow As Long
    With Sheets("Sheet1")
        lastrow = .Cells(.Rows.Count, "F").End(xlUp).Row
        ar = .Range("F2:F" & lastrow).Value2
    End With
    
    ' get unique stores
    Dim dict As Object, key As String, i As Long
    Set dict = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(ar)
        key = Trim(ar(i, 1))
        If key Like PREFIX & "*" Then
            If Not dict.exists(key) Then  
               dict.Add key, FOLDER & Mid(key, Len(PREFIX) + 1) & EXT
           End If
        End If
    Next
    
    ' into array
    Dim StoreList
    StoreList = dict.items
    For i = 0 To UBound(StoreList)
        Debug.Print StoreList(i)
    Next
End Sub