0

so I have developed (read: google -> copy/paste) a macro in Excel for compiling data from numerous standardized excel templates into one master file. Currently the program can loop through any number of files in a single folder, but I want to be able to select one level up from that folder and have it loop through all the subfolders if needed, but still be able to work when a single folder is selected.

The code that starts after opening the files is all done on the opened files. I have some more lines, but all of that is ran on the compile file so it shouldn't need to be encompassed within the file looping section.

I have tried using some of the solutions given in similar questions, but it has never ran correctly for me. Using resulting in a missing End if/with error.

'Selects template folder location for macro
Sub LoopThroughFiles()
    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xls*")
        Do While xFileName <> ""
            With Workbooks.Open(xFdItem & xFileName)

'Transfers data from template to master file
    Worksheets("International").Activate
    Range("A4:L4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Architecture Master Macro_Test.xlsm").Activate
    '"Workbooks.Open "C:\ArchNonMacro.xlsx"
    'Windows("ArchNonMacro.xlsx").Activate

    Range("A1").Select
    Selection.End(xlDown).Select
    Selection.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Application.DisplayAlerts = False
    Workbooks(xFileName).Close

End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
gharold
  • 15
  • 4
  • 2
    If the error is a missing ```end if``` or ```end with``` just add in the ```end if``` or ```end with``` where appropriate? You also have a ```do while``` with no ```loop```. – Warcupine Jul 15 '19 at 19:46
  • 2
    Side note: you want to [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code – cybernetic.nomad Jul 15 '19 at 19:51
  • Use `FSO` not `DIR` `DIR` is on the way out, and `FSO` is more user friendly - better functionality.. Plus a simple search would help yield your anser https://stackoverflow.com/questions/43367671/vba-excel-iterating-through-files-in-folder-via-filesystemobject – alowflyingpig Jul 15 '19 at 20:50
  • Possible duplicate of [VBA (Excel): Iterating through files in folder via FileSystemObject](https://stackoverflow.com/questions/43367671/vba-excel-iterating-through-files-in-folder-via-filesystemobject) – alowflyingpig Jul 15 '19 at 20:50
  • @alowflyingpig While your linked answer does provide how to use FSO, it doesn't address the core question of looping through subfolders. [This answer](https://stackoverflow.com/questions/22645347/loop-through-all-subfolders-using-vba) (and its subsequent linked duplicate answers) all provide excellent information on how to loop through all subfolders of a given directory using FSO. – tigeravatar Jul 15 '19 at 20:57
  • Plenty of code written already for this, search online for 'VBA recursive file and folder loop', the key word being recursive - the sub loops over and over until some condition is met i.e. no more folders to search. Come back if you still need help. – Absinthe Jul 15 '19 at 21:30
  • Thank you, I think the true issue is that I don't know how to apply what I have to the already written code when it comes to the first 9 lines of the program. Do I still need to include selecting the file, or how do I integrate with the given examples? – gharold Jul 16 '19 at 13:41

0 Answers0