importing.csv files in all the subfolders of a directory with a pop up browser

Question

I'm using the macro below to import and arrange the contents of all of the.csv files from a specific directory (C:\csv) on an excel worksheet. I would like the macro to import all of the .csv files in the subdirectories also. If a browser window could open and let me select the path for all of the .csv files I want to import, that would be fantastic. Thank you in advance!

Dim wbCSV   As Workbook
Dim wsMstr  As Worksheet:   Set wsMstr = Sheet1
Dim fPath   As String:      fPath = "C:\csv\"    'path to CSV files
Dim fCSV    As String

If MsgBox("Clear the existing MasterCSV sheet before importing?", vbYesNo, "Clear?") _
    = vbYes Then wsMstr.UsedRange.Clear

Application.ScreenUpdating = False  'speed up macro

fCSV = Dir(fPath & "*.csv")         'start the CSV file listing

    Do While Len(fCSV) > 0
      'open a CSV file
        Set wbCSV = Workbooks.Open(fPath & fCSV)
      'insert col A and add filename
        Columns(1).insert xlShiftToRight
        Columns(1).insert xlShiftToRight
        Columns(1).insert xlShiftToRight
        Columns(1).insert xlShiftToRight
        Range("E4").Select
        Selection.Copy
        Range("a20:a87").Select
        ActiveSheet.Paste
      'copy date to b column
        Range("E3").Select
        Selection.Copy
        Range("b20:b87").Select
        ActiveSheet.Paste
      'copy sample to c column
        Range("c20:c87").Select
        ActiveCell = "sample"
        Range("c20").Select
        Selection.Copy
        Range("c21:c87").Select
        ActiveSheet.Paste
        'copy 1 to d column
        Range("d20:d87").Select
        ActiveCell = "1"
        Range("d20").Select
        Selection.Copy
        Range("d21:d87").Select
        ActiveSheet.Paste
        'delete header
        Rows("1:20").Select
        Selection.Delete Shift:=xlUp
        'delete un needed columns
        Columns("H:H").Select
        Selection.Delete Shift:=xlToLeft
        Columns("F:F").Select
        Selection.Delete Shift:=xlToLeft
        'copy date into master sheet and close source file
        ActiveSheet.UsedRange.Copy wsMstr.Range("A" & Rows.Count).End(xlUp).Offset(1)
        wbCSV.Close False
      'ready next CSV
        fCSV = Dir
    Loop

Application.ScreenUpdating = True

End Sub

A quick note to start, you can [eliminate the use of `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) which is a really good idea/best practice. You basically "snug up" the line ending with `.Select` and starting with `Selection.` But, what about using `Dim fileStr as String ... fileStr = Application.GetOpenFilename()`? — BruceWayne, Dec 30 '15 at 21:25
BruceWayne. Thanks for the tip and the link to get rid of the .Select — MarkJ, Dec 31 '15 at 20:09

justkrys · Answer 1 · 2015-12-31T20:15:11.247

0

Sub lookInSubfolders()
Dim sourceFolder as Scripting.Folder
Dim subfolder as Folder
  For Each subfolder in sourceFolder.SubFolders
    'Do stuff
  Next subfolder
End Sub

Sub getFiles()
    Dim xRow As Long
    Dim xDirect$, xFname$, InitialFoldr$
        'InitialFoldr$ = "\\path to default directory if you have a main folder"
        'This part opens up the browser window:
        With Application.FileDialog(msoFileDialogFolderPicker)
            .InitialFileName = Application.DefaultFilePath & "\"
            .Title = "Please select a folder to list Files from"
            .InitialFileName = InitialFoldr$
            .Show

        'Optional for if there are no files in the directory
            If .SelectedItems.Count <> 0 Then
                xDirect$ = .SelectedItems(1) & "\"
                xFname$ = Dir(xDirect$, 7)
            If xFname$ = "" Then
                 MsgBox ("The folder is empty! Your work is done!")
                Exit Sub
            End If

        'Optional to list file names in column "A"
            Do While xFname$ <> ""
                ActiveCell.Offset(xRow) = xFname$
                xRow = xRow + 1
                xFname$ = Dir
            Loop
            End If
        End With
    End Sub

edited Dec 31 '15 at 20:15

answered Dec 31 '15 at 15:55

justkrys

300
3
13

Justkrys,Thanks for the answer, It's very close. I have the dialog box opening to the correct directory(c:\csv) and the rest of my macro runs but I would like to get all of the .csv files from the subfiles in the directory also (c:\csv\120415\...any other subfolders) As the code works now I only get .csv files from the directory I choose from the browser window. – MarkJ Dec 31 '15 at 20:07
I added some code above that may help. Just set sourceFolder equal to the variable for the selected folder. – justkrys Dec 31 '15 at 20:17
justkyrs, I couldn't make progress with the additional code, can you elaborate where I should insert the additional code and what to set the sourcefolder as? Thanks – MarkJ Jan 05 '16 at 14:29
Put the code for what you want done to the subfolders where I wrote "Do stuff". As for what you put in source folder, that would be the path to the main folder you're getting the subfolders from. Then you have to figure out where to call the methods and how to use them together. – justkrys Jan 05 '16 at 17:56

importing.csv files in all the subfolders of a directory with a pop up browser

1 Answers1