Counting files within sub folders

mtranchi · January 25th, 2011, 04:40 AM

if you re-read the post at 12:09 yesterday, you'll notice i said "you have to set a reference to VBScript in order to make it work: Hit Tools > References then scroll down and put a check next to Microsoft Scripting Runtime." So make sure you've done that

jeskit · January 25th, 2011, 05:17 AM

OK IT WORKS! no errors!! Always good!

But!! Its only bringing back results from one of the subfolders i need it to bring back results from all 6 subfolders which are in the folder.

I think its because of the strFile and strFldr bit but i am not sure! This is the code which i have.

Code:

 Sub Get_Dealer_Count()
'Define Variables
    'Const strFldr As String = "Path2"
    Dim strTemplate As String
    Dim strFldr As String
    Dim strFile As String
    Dim wbExtractSize As Workbook
    Dim wbCsv As Workbook
    Dim wsDealerExtracts As Worksheet
    Dim wsMyCsvSheet As Worksheet
    Dim lNextRow As Long
    Dim FH As New clsFileHandler
    
'set strFldr variables
    strFldr = "C:\Production2\ATX\Extracts\201001"

'set strFile variables
    strFile = Dir(strFldr & "\*.csv")

    strTemplate = "Extract_Size_Checker_template.xls"

'set the calculation mode
    Application.Calculation = xlCalculationManual

'set the workbook and worksheet
    Set wbExtractSize = Workbooks.Open("C:\Documents and Settings\SeymourJ\Desktop\Tasks\MacroTask\" & strTemplate)
    Set wsDealerExtracts = wbExtractSize.Sheets("Dealer Extracts")

'find the next row available in ExtractSize, add two to
    lNextRow = 18
   
'Loop through the csv files
    If Len(strFile) > 0 Then
        Do
            FH.ClearPreviousSearch Sheet1
            FH.GetAllFiles "C:\Production2\ATX\Extracts\201001\", Sheet1.Range("A2")
            Set FH = Nothing
            Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile)
            Set wsMyCsvSheet = wbCsv.Sheets(1)
            With wsDealerExtracts
                .Cells(lNextRow, 6) = strFldr
                .Cells(lNextRow, 7) = strFile
                .Cells(lNextRow, 8) = WorksheetFunction.CountA(wsMyCsvSheet.Range("A:A"))
            End With
        
        'increment to the next row
            lNextRow = lNextRow + 1
        
        'close it
            wbCsv.Close
        
        'go to next file
            strFile = Dir
            Application.StatusBar = strFile
        Loop Until Len(strFile) = 0
    End If

    ActiveWorkbook.ActiveSheet.Range("A1").Select
    
'clean up
    Set wbExtractSize = Nothing
    Set wbCsv = Nothing
    Set wsDealerExtracts = Nothing
    Set wsMyCsvSheet = Nothing

End Sub

jeskit · January 25th, 2011, 05:20 AM

Sorry also as i have just noticed i put

Code:

 Set FH = Nothing

in the wrong place in the code i just uploaded the line above is now under 'clean up

mtranchi · January 25th, 2011, 05:38 AM

right, you want to put that

Code:

Set FH = Nothing

at the bottom of the module where you're releasing any of your computer's memory that is still being held by excel. Also, I noticed you're saving your workbooks as .xls files. Unless you have a need for backward compatibility, I.E. you have other people who will be using your program who do not have excel 2010, you should save your workbooks as .xlsm (excel macro enabled workbooks). Lastly, though it's minor now, i noticed you didn't update your comment:

Code:

'find the next row available in ExtractSize, add two to
    lNextRow = 18

'should be something like

'set the row  in ExtractSize where we're going to start adding data
    lNextRow = 18

like i said, minor now, but if you need to come back to this code in six months, you may have no idea what does what, and your comments will be a good source to refresh your memory as to what is doing what and why.

oh one other thing. the way i set up that file handler class module it puts the list of files onto a worksheet. after having repositioned the "Set FH = Nothing", is it still going through all the files? it doesn't appear that it will.

jeskit · January 25th, 2011, 05:43 AM

Hi

Ok. I have repositioned the "Set FH = Nothing" bit and it is still just pulling files from the main folder "C:\Production2\ATX\Extracts\201001" not from its sub folders.

It runs the code without error and does find the one CSV file that is in the 201001 folder but not the CSV files that are in the sub folders inside 201001

jeskit · January 25th, 2011, 05:46 AM

OK.

I think i found the problem and it was my fault i forgot the files in the folders are not named sheet1 so i changed it to Sheet (1) and it worked BUT its putting the results in cell A2 in the extracts folder and its not giving me the counta results just the path and filename

mtranchi · January 25th, 2011, 05:48 AM

right well we'll have to do some recoding, give me a few minutes

mtranchi · January 25th, 2011, 06:09 AM

in the first column of data that holds the path (the strFldr variable data), is it important for it to hold ONLY the path or can it hold the path and filename? e.g. "C:\Users\Mike\Documents\Business\StatsCustomers1\ CustomerWebsites\elizabethsloomroom\Templates\Text forpages\HowToTellQualityRagRug.txt" vs. "C:\Users\Mike\Documents\Business\StatsCustomers1\ CustomerWebsites\elizabethsloomroom\Templates\Text forpages" ?

jeskit · January 25th, 2011, 06:12 AM

Yeah it can hold both not a problem

mtranchi · January 25th, 2011, 06:29 AM

okay i made some changes in both modules. here's the code for your main module:

Code:

 Sub Get_Dealer_Count()
'Define Variables
    'Const strFldr As String = "Path2"
    Dim strTemplate As String
    Dim strFldr As String
    Dim strFile As String
    Dim wbExtractSize As Workbook
    Dim wbCsv As Workbook
    Dim wsDealerExtracts As Worksheet
    Dim wsMyCsvSheet As Worksheet
    Dim lNextRow As Long
    Dim FH As New clsFileHandler
    
'set strFldr variables
    strFldr = "C:\Production2\ATX\Extracts\201001"

'set strFile variables
    strFile = Dir(strFldr & "\*.csv")

    strTemplate = "Extract_Size_Checker_template.xls"

'set the calculation mode
    Application.Calculation = xlCalculationManual

'set the workbook and worksheet
    Set wbExtractSize = Workbooks.Open("C:\Documents and Settings\SeymourJ\Desktop\Tasks\MacroTask\" & strTemplate)
    Set wsDealerExtracts = wbExtractSize.Sheets("Dealer Extracts")

'find the next row available in ExtractSize, add two to
    lNextRow = 18
    
    '--------------------------------------------------------------------------------------------------------------------
    'this should put all the file names with paths on your "dealer extracts" sheet. you may have to adjust the target cell
    '--------------------------------------------------------------------------------------------------------------------
    FH.ClearPreviousSearch wsDealerExtracts
    FH.GetAllFiles "C:\Production2\ATX\Extracts\201001\", wsDealerExtracts.Range("F18")
   
'Loop through the csv files
    If Len(strFile) > 0 Then
        Do
            '---------------------------------------------------------------------------------------------------------------
            'i moved the with statement up so that the open parameter would be included. you may need to adjust the cell from
            'which it looks for the file name
            '-----------------------------------------------------------------------------------------------------------------
            With wsDealerExtracts
                Set wbCsv = Workbooks.Open(Filename:=.Cells(lNextRow, 6))
                Set wsMyCsvSheet = wbCsv.Sheets(1)
                '------------------------------------------------------------------------------------
                'no longer any need for this as it's already been put in by the clsFileHandler object
                ''''.Cells(lNextRow, 6) = strFldr
                '------------------------------------------------------------------------------------
                .Cells(lNextRow, 7) = strFile
                .Cells(lNextRow, 8) = WorksheetFunction.CountA(wsMyCsvSheet.Range("A:A"))
            End With
        
        'increment to the next row
            lNextRow = lNextRow + 1
        
        'close it
            wbCsv.Close
        
        'go to next file
            strFile = Dir
            Application.StatusBar = strFile
        Loop Until Len(strFile) = 0
    End If

    ActiveWorkbook.ActiveSheet.Range("A1").Select
    
'clean up
    Set wbExtractSize = Nothing
    Set wbCsv = Nothing
    Set wsDealerExtracts = Nothing
    Set wsMyCsvSheet = Nothing
    Set FH = Nothing

End Sub

and here's the code that i changed in the clsFileHandler class module:

Code:

Public Sub ClearPreviousSearch(shtSheet As Worksheet)
'----------------------------------------------------------------------------------------------
'you may need to adjust where you want the sheet to be cleared from. you do that by adjusting the number 17 in the next line of code
'------------------------------------------------------------------------------------------------
shtSheet.UsedRange.Offset(17, 0).EntireRow.Delete
lTargetOffset = 0
End Sub

like i said in the comments, you may need to do some adjusting on the target cells, etc. Try that and see if it works.