Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 25th, 2011, 06:35 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Great that works!

But it does not bring back a COUNTA result except for the CSV file in the 201001 folder. But it does produce a list of all the files in each of sub folders.
 
Old January 25th, 2011, 06:56 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

not sure why that is, but i think it has something to do with either the do loop, if statement, or Dir function. try making the changes that i highlighted with the "+" symbol.
Code:
Sub Get_Dealer_Count()
'Define Variables
    'Const strFldr As String = "Path2"
    Dim strTemplate As String
    Dim strFldr As String
    '++++++++++++++++++++++++++++++++++++++++++++++++++
    'no longer a need for this varaible
''''    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
'------------------------------------------------------------------------------------
'the following if statement will always evaluate to true, so it's uneccesary
    ''''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
                '------------------------------------------------------------------------------------
                '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
                'we'll have to address this issue next
''''                .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
        
        '--------------------------------------------------------------------------------
        'this is no longer necessary either as we're not using the dir function
        'go to next file
''''            strFile = Dir
''''            Application.StatusBar = strFile
        '--------------------------------------------------------------------------------
        
        '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        'this loop is probably terminating it because of the "dead" Dir function
''''        Loop Until Len(strFile) = 0
        'so try this instead
        Loop Until IsEmpty(wsDealerExtracts.Cells(lNextRow, 6))
        '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        
    '------------------------------------------
    ''''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
The Following User Says Thank You to mtranchi For This Useful Post:
jeskit (January 25th, 2011)
 
Old January 25th, 2011, 07:03 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

THANK YOU for all your help!!

its works perfectly now!!!!
 
Old January 25th, 2011, 07:04 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

don't forget to hit the thanks button, lol. glad it works, gl with future programming tasks :)
The Following User Says Thank You to mtranchi For This Useful Post:
jeskit (January 25th, 2011)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Files and Folders in VB.Net2008 kumar.selva.c Visual Basic 2008 Professionals 6 September 26th, 2011 12:58 PM
Authenticating Specific Files and Folders homepagestore BOOK: Professional ASP.NET 3.5 SP1 Edition: In C# and VB 2 December 13th, 2009 01:50 AM
compress a set of files and folders prathapkumar ASP.NET 3.5 Basics 1 September 11th, 2009 04:48 AM
connect to files from different folders lcyean ASP.NET 2.0 Basics 1 May 11th, 2007 07:23 AM
Including files across folders dsunmedia Beginning PHP 3 July 26th, 2004 04:17 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.