Excel VBA: Getting subfolders' names as array of strings

Question

My VBA program code access files inside each subfolder. So I am storing those subfolders' names in an array and using for loop to access. I want to make my program more general. How can we get subfolders' names as an array of strings to a variable? If you know any function, please help me. Thanks in advance.

My code

Sub CSVtoXLSX_Click()
 Dim CSVfolder As String, XlsFolder As String, fname As String
 Dim wBook As Workbook
 Dim vArr, vFile
 vArr = Array("subfolder1", "subfolder2", "subfolder3", "subfolder4", "subfolder5")
 CSVfolder = "C:\Work\"
 XlsFolder = "C:\Work\"
 For Each vFile In vArr
 fname = Dir(CSVfolder & vFile & "\" & "*.csv")
 Do While fname <> ""
    Application.ScreenUpdating = False
    Set wBook = Workbooks.Open(CSVfolder & vFile & "\" & fname, Format:=6, Delimiter:=",")
    wBook.SaveAs XlsFolder & vFile & "\" & Replace(fname, ".csv", ""), xlOpenXMLWorkbook
    Application.CutCopyMode = False
    wBook.Close False
 fname = Dir()
 Loop
 Kill CSVfolder & vFile & "\" & "*.csv"
 Next
End Sub

Tim Williams · Accepted Answer · 2015-06-28 00:52:01Z

2

Try this:

Sub CSVtoXLSX_Click()
 Dim CSVfolder As String, XlsFolder As String, fname As String
 Dim wBook As Workbook
 Dim colSF As Collection, vFile
 Dim bHadFiles As Boolean

 CSVfolder = "C:\Work\"
 XlsFolder = "C:\Work\"

 Set colSF = GetSubFolders(CSVfolder)
 For Each vFile In colSF
    fname = Dir(CSVfolder & vFile & "\" & "*.csv")
    bHadFiles = False
    Do While fname <> ""
        bHadFiles = True '<< at least one file to delete using Kill...
        Application.ScreenUpdating = False
        Set wBook = Workbooks.Open(CSVfolder & vFile & "\" & fname, _
                                   Format:=6, Delimiter:=",")

        wBook.SaveAs XlsFolder & vFile & "\" & Replace(fname, ".csv", ""), _
                      xlOpenXMLWorkbook

        Application.CutCopyMode = False
        wBook.Close False
        fname = Dir()
    Loop
    If bHadFiles Then Kill CSVfolder & vFile & "\" & "*.csv" '<< will error if nothing to delete
 Next
End Sub


'get all subfolders under the provided path
'  return as a Collection
Function GetSubFolders(sPath As String) As Collection

    Dim col As New Collection, f

    f = Dir(sPath, vbDirectory + vbNormal)
    Do While f <> ""
        If GetAttr(sPath & f) And vbDirectory Then
            If f <> "." And f <> ".." Then col.Add f
        End If
        f = Dir()
    Loop
    Set GetSubFolders = col

End Function

answered Jun 28, 2015 at 0:52

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

1 Comment

RubberDuck Over a year ago

Any particular reason you didn't just use the FileSystemObject?

Collectives™ on Stack Overflow

Excel VBA: Getting subfolders' names as array of strings

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