Reference string array

Question

I inherited a macro and need to make updates, please bear with me.

Public Const strSourceFolder1 As String = "\\File_Path\Sub_Folder2"
Public Const strSourceFolder2 As String = "\\File_Path\Sub_Folder1"

I have 8 strings such as those above. I'd like make these into an array that I can reference throughout my procedures. Is this possible? What would forming the array look like? The "File_Path" is the location of a folder containing documents to be manipulated by the macro, my goal here is only to update my source folder to a set of folders.

I tried the FoldersArray, but I got a type mismatch when I reference it in this part of my macro:

Private Sub GetFileName()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim FileName As String
    Dim Path As String
    Dim lngRow As Long

    Set wb = ActiveWorkbook
    Set ws = ActiveSheet
    lngRow = 2
    Path = FolderArray & "*.*"
    FileName = Dir(Path, vbNormal)

    Do Until FileName = ""
        Application.DisplayAlerts = False
        Sheet1.Cells(lngRow, 1).Select
        Sheet1.Cells(lngRow, 1) = FileName

        Call MainExtractData(FileName, lngRow)

        lngRow = lngRow + 1
        FileName = Dir()
    Loop
End Sub

Why wouldn't it be possible? You can create arrays from strings, or am I overlooking something? — BruceWayne
– BruceWayne, Commented Aug 6, 2018 at 21:18
I'm sure it is, but I'm not sure how to put it together so that one variable (the name of the array) loops through the folders. — degeest12
– degeest12, Commented Aug 6, 2018 at 21:23
Just to clarify, are you asking for help setting up the array? Or, how to load them in to an array and then use that array? — BruceWayne
– BruceWayne, Commented Aug 6, 2018 at 21:51
Both I suppose. I need to replace all my callouts that lead to a single file path with a callout that loops through the 8 folders. — degeest12
– degeest12, Commented Aug 7, 2018 at 13:58

Tim Williams · Accepted Answer · 2018-08-06 21:18:33Z

1

Create a function to return your set of folders:

Function FolderArray()
    Dim folders(1 to 8) As String
    folders(1) = "\\File_Path\Sub_Folder2"
    '....
    folders(8) = "\\File_Path\Sub_Folder9"
    FolderArray = folders
End Function

answered Aug 6, 2018 at 21:18

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BruceWayne Over a year ago

Oh! Then OP can just do for each folder in FolderArray yeah?

Collectives™ on Stack Overflow

Reference string array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related