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, 04:40 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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
 
Old January 25th, 2011, 05:17 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

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
 
Old January 25th, 2011, 05:20 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

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
 
Old January 25th, 2011, 05:38 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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.
 
Old January 25th, 2011, 05:43 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

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
 
Old January 25th, 2011, 05:46 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

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
 
Old January 25th, 2011, 05:48 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

right well we'll have to do some recoding, give me a few minutes
 
Old January 25th, 2011, 06:09 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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" ?
 
Old January 25th, 2011, 06:12 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Yeah it can hold both not a problem
 
Old January 25th, 2011, 06:29 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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.





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.