Loop through Folder of Excel Workbooks and Append only Workbooks with a Key Word to Master Sheet

Question

I am looking for VBA code that would look through several hundred Workbooks and open only ones that have "cash" in the workbook title. It would then pull the second row of the first worksheet down to the last row and append it to a master worksheet.

Although I see the iteration count reaches all one hundred plus workbooks, the code appends only the first few worksheets and stops. Could anyone provide insight as to why that is happening? Thank you in advance!

Sub Pull_Cash_WB_Names()
Dim filename As Variant
Dim a As Integer
a = 1

Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String
Dim LRow As Long, LCol As Long

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayStatusBar = True


Set wbDst = ThisWorkbook


strFilename = Dir("\\DATA\*Cash*")
Count = 0

Do While strFilename <> ""

Set wbSrc = Workbooks.Open("\\DATA\*Cash*")
        Set wsSrc = wbSrc.Worksheets(1)

       'copy all cells starting from 2nd row to last column
    LRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LCol = ActiveSheet.Cells(7, Columns.Count).End(xlToLeft).Column
    Cells(2, 1).Resize(LRow - 1, LCol).Select
    Selection.Copy

        'paste the data into master file
        wbDst.Sheets(wbDst.Worksheets.Count).Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

   'counts the number of iterations
    Count = Count + 1
    Application.StatusBar = Count

        wbSrc.Close False
        
        strFilename = Dir()

Loop

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

You can use `Dir()` or `Scripting.FilesystemObject` to loop over all files in a folder. Tons of examples here on SO alone or via google. — Tim Williams, Jan 15 '21 at 00:56
So I have this code that locates all the files with 'cash' inside the Excel file and appends just the title name into a master Excel worksheet. Is there a way to open the file and append the content details beginning from row 2 for files that meet the 'cash' criteria? — J.Woe, Jan 15 '21 at 23:01
You can *edit your question* to add the code - not really readable in a comment. — Tim Williams, Jan 15 '21 at 23:02
Example here: https://stackoverflow.com/questions/33400948/open-all-files-in-same-folder-as-active-workbook-except-active-workbook — Tim Williams, Jan 15 '21 at 23:08
I did some edits to the example. I am getting an error on the wbSrc naming convention. — J.Woe, Jan 16 '21 at 01:16
"an error" isn't very useful in helping us figure out the problem — Tim Williams, Jan 16 '21 at 01:25
Sorry Tim Williams. The error was - "Run-time error '1004': Sorry, we couldn't find '/Data/Folder.csv.' Is it possible it was moved, renamed, or deleted?". Did I get the variable naming convention wrong? The error is on the "Set wbSrc = Workbooks.Open(filename:=MyPath & "\" & strFiledName". — J.Woe, Jan 19 '21 at 00:37
Do I need to set a MyPath since I already named the strFileName as the path I want to go and search through. I put 'Set wbSrc = Workbooks.Open(strFileName)', however, it gives me the same error. — J.Woe, Jan 19 '21 at 18:30
`strFilename` is just the file name, not the full path - you should pass the full path like this `Set wbSrc = Workbooks.Open("\\DATA\" & strFilename)` — Tim Williams, Jan 19 '21 at 18:36
I got the code to run after fixing the path and altering some other parts of the code. Would you take a look at my edits please? The code iterates through all the excel files in the folder, but only appends the first 10 or so files. — J.Woe, Jan 31 '21 at 06:25

Tim Williams · Answer 1 · 2021-02-01T00:32:44.673

0

See fixes/suggestions below

Sub Pull_Cash_WB_Names()

    Const PTH As string = "\\DATA\" 'folder path goes here

    Dim wbDst As Workbook
    Dim wbSrc As Workbook
    Dim strFilename As String
    Dim rngCopy AsRange, rngDest as range
    
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = True
    
    Set wbDst = ThisWorkbook
    Set rngDest = wbDst.Sheets(wbDst.Worksheets.Count).Range("A1") 'start pasting here  

    strFilename = Dir(PTH & "*Daily*Cash*.csv") '#EDIT#
    Count = 0
    
    Do While strFilename <> ""
    
        Set wbSrc = Workbooks.Open(PTH & strFilename)                   'full path+name
        Set rngCopy = wbSrc.Worksheets(1).Range("A1").CurrentRegion     'whole table
        Set rngCopy = rngCopy.Offset(1, 0).resize(rngcopy.rows.count-1) 'exclude headers 
        rngCopy.Copy
            
        'paste the data into master file
        rngDest.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        Set rngDest = rngDest.offset(rngCopy.rows.count) 'next paste goes here...
    
        Count = Count + 1
        Application.StatusBar = Count
        wbSrc.Close False    
        strFilename = Dir()
    
    Loop
    
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

edited Feb 01 '21 at 00:32

answered Jan 31 '21 at 07:14

Tim Williams

154,628
8
97
125

The code didn't pull any data from the folder. It was not registering the strFilename. The files are all '.csv' and have 'daily cash' in their name. So I put strFilename = Dir(PTH & "*Daily Cash*.csv*") – J.Woe Jan 31 '21 at 23:49
The variable gets drawn when I go back to the previous naming of "\\DATA\*Cash*". Do I need to make the code more specific – J.Woe Jan 31 '21 at 23:51
See edit above - if your files are CSV you can put that in the pattern you pass to `Dir()` – Tim Williams Feb 01 '21 at 00:33
The strFileName turns up as "" ie, strFileName = "" – J.Woe Feb 01 '21 at 00:43
I commented out the PTH variable and set strFileName to \\DATA*Cash*. It was able to pull the file name in, but ran into a 'Sorry, we couldn't find FileName.csv. Is it possible it was moved, renames, or deleted?' message when opening the wbSrc. – J.Woe Feb 01 '21 at 01:07
Are your files directly under \\DATA\ or in a subfolder? – Tim Williams Feb 01 '21 at 02:10
My files are in the 6th subfolder of \\DATA\, for example the following convention works to pull the file name - "\\DATA\subfolder1\subfolder2\subfolder3\main_subfolder" – J.Woe Feb 01 '21 at 02:17
OK that's a whole different thing - `Dir()` only searches one level (whatever path you provide) so your current code (or mine) will not work. I'd try instead getting the files using something like one of the answers here: https://stackoverflow.com/questions/20687810/vba-macro-that-search-for-file-in-multiple-subfolders or maybe https://stackoverflow.com/questions/43284289/convert-rtf-to-docx-from-selected-folder-and-its-subfolders-in-vba/43286419#43286419 – Tim Williams Feb 01 '21 at 02:25

Loop through Folder of Excel Workbooks and Append only Workbooks with a Key Word to Master Sheet

1 Answers1