1

I've designed a VBA enabled workbook that allows the user to select a .csv file (which is a customer export from another system) and then it basically processes it to produce a number of different user cohorts, based upon various user defined criteria etc.

It all works really nicely. However, it can only process one .csv file at a time.

The current approach I've taken basically imports the content of the selected CSV file into a new sheet, and then I simply interrogate that data and do what I need to with it. However, having not used VBA for a very long time, I'm not sure how to build on what I've already coded, to allow for the selection of multiple CSV files.

    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        'We only want to allow CSV files as this is what the ADT comes in
        .Filters.Add "ADT CSV Files", "*.csv", 1
        'Show the dialog box
        .Show

    'Error check in case user cancels dialog box to prevent type-mismatch error

    If (.SelectedItems.Count = 0) Then

        Range("C19").Value = "File selection aborted."
    Else
        'Store in fullpath variable
        Range("C19").Value = "Processing..."
        fullpath = .SelectedItems.Item(1)
    End If
    End With

    'A final check to make sure that the user hasn't done anything odd and somehow selected an invalid file format

    If InStr(fullpath, ".csv") = 0 Then
        Exit Sub
    End If

    Range("J26").Value = "Source File:"
    Range("J27").Value = fullpath

    'Now we grab the data from the file and import it into a new sheet within workbook

    Set Ws = ThisWorkbook.Sheets.Add
    Ws.Name = "ADT Data"

    'The ADT seems to be using fairly standard formatting conditions, so the following should surfice

         With Ws.QueryTables.Add(Connection:= _
        "TEXT;" & fullpath, Destination:=Ws.Range("$A$1"))

        .Name = "ADT Data"
        .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 = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileCommaDelimiter = True
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False

    End With

    'Now we trigger our main triage processes

    Call Extract

I'm assuming I need to add the selected files to an array, and then loop through them, but with my VBA knowledge as it is I'm not sure how to achieve.

Improve this question
2
  • 1
    Set .AllowMultiSelect = True and then iterate over .SelectedItems. See how you have .SelectedItems.Item(1) ? Instead of that, do a For i = 1 to .SelectedItems.Count and then reference each selected .csv with .SelectedItems.Item(i) Commented Apr 16, 2019 at 14:41
  • .allowmultiselect=true, store the names of each file, then loop through those file names like for each item In .SelectedItems, appending to the lastrow+1 in the workbook Commented Apr 16, 2019 at 14:41

1 Answer 1

Reset to default
2

You have this option set to false

.AllowMultiSelect = False

I would create an object to refer to the FileDialog

'Declare a variable as a FileDialog object and set it like so: Dim fd As FileDialog 'Create a FileDialog object as a File Picker dialog box. Set fd = Application.FileDialog(msoFileDialogFilePicker)

You can then iterate over the SelectedItems collection of the fd object. For Each vrtSelectedItem In .SelectedItems

Then do your operation on each file that was selected.

The AllowMultiSelect documentation has some good info.

https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa210129(v%3Doffice.11)

Hope this helps.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.