VBA in Excel 2013, Loop through files where the wildcard applies to the subdirectory

Question

I have a MATLAB script that analyzes experimental results on a number of widgets my company makes, and finally exports the summarized data into a CSV file. I want to import all of these csv files into an Excel workbook so that I can compare the summarized results using a pivot table.

Each widget currently has its own directory, with a Datasets subdirectory that contains a pivotData.csv file. The directory structure looks something like the following:

1. C:\Path\To\Widgets
   • Widget_1\Datasets\pivotData.csv
   • Widget_2\Datasets\pivotData.csv
   • Widget_3\Datasets\pivotData.csv
   • Widget_3b\Datasets\pivotData.csv
   • Widget_4\Datasets\pivotData.csv

The master workbook, pivotMaster.xlsm, is in the main directory: C:\Path\To\Widgets\pivotMaster.xlsm

I am creating a macro in the master workbook that loops through each widget directory, and appends the data in the master workbook.

So far I have been able to put this code together, which was inspired by someone looping over various csv files in the same directory. I'm currently having trouble on using a wildcard for a directory name. The error is when I try to initialize Filename by concatenating the Path and subdirectory wildcard strings.

Sub test()
'DECLARE AND SET VARIABLES
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Path = "C:\Path\To\Widgets\"
Filename = Dir(Path & "Widget_*\Datasets\pivotData.csv")  %%% <-- I get an error on this line &&&
'--------------------------------------------
'OPEN EXCEL FILES

'Clear all the previous contents
Application.Run ("clearContents")

'Loop over all "pivotData.csv" files in Filename
 Do While Len(Filename) > 0  'IF NEXT FILE EXISTS THEN
    Set wbk = Workbooks.Open(Path & Filename)

'Notify User that Filename has opened
    MsgBox Filename & " has opened"  'OPTIONAL- CAN COMMENT OUT

'Move to first empty row below "A1"
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.FormulaR1C1 = "=R[1]C[0]"

'Import Data from Text file "Filename"
    With ActiveSheet.QueryTables.Add(Connection:= _
        wbk, Destination:= _
        ActiveCell)
        .CommandType = 0
        .Name = "pivotData_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 4, 4, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("A1").Select
    wbk.Close True
    Filename = Dir
Loop
End Sub

Sub clearContents()
'
' clearContents Macro clears everything below the headers
'

'
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.clearContents
End Sub

Can anyone give me an idea of what I'm doing wrong here?

Answer 1

This will get you folders in a folder, just keep drilling down until you find your workbooks.

Sub DrillDown()

Dim path As String
path = "C:\path\to"

Dim FileSystem As Object
Set FileSystem = CreateObject("Scripting.FileSystemObject")

Dim SubFolder
For Each SubFolder In FileSystem.GetFolder(path).subfolders
 Debug.Print SubFolder

Next

End Sub

To go through looking for files -

Sub LookForFiles()
Dim filename As Variant
filename = Dir("C:\path\to\" & "*.csv")
    Do While filename <> ""
        Debug.Print filename
        filename = Dir
    Loop
End Sub

So you could go through the directories until you find the directory name you expect, then look for the csv files and do what you want.