Run time error 1004

mtranchi · January 17th, 2011, 04:50 AM

so the first counta function which refers to the ExtractSize workbook works but not the second one referring to the csv workbook?

jeskit · January 17th, 2011, 04:56 AM

yep, the code works until it gets to the second counta then the error "object doesn't support this property or method" pops up

mtranchi · January 17th, 2011, 04:59 AM

well i noticed you're not using the workbook that i said you should use, one name myCsvFile.csv and with the exact data in it. As you can see, programming languages are very finicky, so it's important you set everything up EXACTLY as i mentioned, then we can move in from there

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

OK. i have tried using the file you suggested and it still did not work and it stil stopped at the second counta and the error "object doesn't support this property or method" appeared

mtranchi · January 17th, 2011, 05:10 AM

try

Code:

    With wsDealerExtracts
        .Cells(lNextRow, 1) = WorksheetFunction.CountA(wsMyCsvSheet.Range("A:A"))
    End With

instead of

Code:

With wbCsv
        .Cells(lNextRow, 1) = WorksheetFunction.CountA(wsMyCsvSheet.Range("A:A"))
    End With

the with statement is supposed to refer to the wsDealerExtracts, not wbCsv

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

YAY!!! Its working! Thank you so much!

So how do i get this code to loop through the whole folder and perform that for each of the files inside that folder?

mtranchi · January 17th, 2011, 05:24 AM

well you're going to have to strike out on your own a bit i'm afraid. i have a newer version of excel and they did away with the Dir function completely, so i can't make sure the code works on this end before i send it to you, but what i HIGHLY HIGHLY recommend is take the code you have right now that is working and copy it, paste it below and comment out what you pasted. (there's a button on the toolbar that can comment out all selected lines). That way, if you make too many changes and get lost as to what's what, you can always go back to the starting point.
Now you did have the Dir function working in your original code, so looking at that, I'm going to guess try something like replacing

Code:

'Loop through the csv files (in this case only one)
For i = 1 To 1
    Set wbCsv = Workbooks.Open("C:\Users\Mike\Documents\deleteme\myCsvFile.csv")
    Set wsMyCsvSheet = wbCsv.Sheets(1)
    With wsDealerExtracts
        .Cells(lNextRow, 1) = WorksheetFunction.CountA(wsMyCsvSheet.Range("A:A"))
    End With
    
    'increment to the next row
    lNextRow = lNextRow + 1
    
    'close it
    wbCsv.Close
Next i

with... hold on, let me look at your old code and see if i can get it pretty close

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

Way ahead of you!! I have created a word document and each version of the code that works gets pasted into a new page in the word document and then saved both on computer and on usb!!

mtranchi · January 17th, 2011, 05:29 AM

so you're all set?

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

sort of! But i need to get the piece of code you pasted to me to loop through the folder instead of just looking at one file. Also is there a way to paste the result into a certain cell range in the extractsize file??