Wrox Programmer Forums
|
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 17th, 2011, 05:34 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

give me a few minutes
 
Old January 17th, 2011, 05:59 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

i'm almost certain this isn't going to work because intellisense isn't popping up, and I can't test it on my computer because i don't have the Dir function, but try it and see what happens. Double check that the folders and whatnot are correct for your file system
Code:
Sub Open_Csv_And_ExtractSize_CountRows()

'your old vars

'since this isn't going to change, make it a constant, makes things move faster
Const strFldr As String = "C:\Production2\ATX\Extracts\201001\DealerData"

Dim strFile As String
'------------------------------------------------------

'relevant workbooks
Dim wbExtractSize As Workbook
Dim wbCsv As Workbook

'relevant sheets
Dim wsDealerExtracts As Worksheet
Dim wsMyCsvSheet As Worksheet

'this will hold the next row to put data into
Dim lNextRow As Long

Application.ScreenUpdating = False

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

'no need to set a variable and all that, just set the calculation mode
Application.Calculation = xlCalculationManual

'set the workbook and worksheet
Set wbExtractSize = Workbooks.Open("C:\Documents and Settings\SeymourJ\My Documents\Extract_Size_Checker_test.xls")
Set wsDealerExtracts = wbExtractSize.Sheets("DealerExtracts")

'find the next row available in ExtractSize, add two to it because there is a blank row at the top and you want the next available row

'---------------------------------------------------------------------------------------
' okay, see the number "2" at the end of the following line of code?
lNextRow = WorksheetFunction.CountA(wsDealerExtracts.Range("A:A")) + 2
'that number determines the row number it's going to go into, so if you want the entry to go further down, increment the number 2
'---------------------------------------------------------------------------------------

'Loop through the csv files
If Len(strFile) > 0 Then
    Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With wsDealerExtracts
            '-----------------------------------------------------------------------------------
            'in the following .Cells() statements, the 1, 2, 3 refer to the column numbers, if you want the entries to go further
            'to the right, increment those numbers, I.E. if you want them to go into the F, G, H columns you would do this:
            ' .Cells(lNextRow, 6) = strFldr
            ' .Cells(lNextRow, 7) = and so on
            '---------------------------------------------------------------------------------------
            
            .Cells(lNextRow, 1) = strFldr
            .Cells(lNextRow, 2) = strFile
            .Cells(lNextRow, 3) = 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 = stfile
    Loop Until Len(strFile) = 0
End If
wbExtractSize.Save
'wbExtractSize.Close

'clean up
Set wbExtractSize = Nothing
Set wbCsv = Nothing
Set wsDealerExtracts = Nothing
Set wsMyCsvSheet = Nothing
Application.ScreenUpdating = True
End Sub
let me know what happens
 
Old January 17th, 2011, 06:03 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

i think you had this line of code:
Code:
'code i just sent you
Set wsDealerExtracts = wbExtractSize.Sheets("DealerExtracts")

'the way your code was
Set wsDealerExtracts = wbExtractSize.Sheets("Dealer Extracts")
with a space inbetween the words. You should get away from using spaces in ANY names. They are bad news.
 
Old January 17th, 2011, 06:07 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

one other thing, at the end of it all you'll want to reset your status bar otherwise it will hold the last file processed, forget how to do that, but you can just set it to "Ready":
Code:
Application.Statusbar = "Ready"
 
Old January 17th, 2011, 06:08 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

ITS WORKS!!!!!!!!

Thank you so much!!! i am very grateful!!

the problem is solved thanks so much
 
Old January 17th, 2011, 06:09 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

great. gl with your future programming!
Mike





Similar Threads
Thread Thread Starter Forum Replies Last Post
Run-time error '1004' with Named cells mahaneca Beginning VB 6 1 January 29th, 2009 01:04 AM
Run time error 1004 Ken Matthews Excel VBA 1 March 2nd, 2007 06:30 AM
Run-time error '1004' Document not saved Tucky Excel VBA 0 October 28th, 2004 12:04 AM
Run-time error '1004': raja.gogoi VB Components 1 July 18th, 2004 11:02 AM





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