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

so the first counta function which refers to the ExtractSize workbook works but not the second one referring to the csv workbook?
 
Old January 17th, 2011, 04:56 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

yep, the code works until it gets to the second counta then the error "object doesn't support this property or method" pops up
 
Old January 17th, 2011, 04:59 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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

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

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

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

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

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

so you're all set?
 
Old January 17th, 2011, 05:34 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

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??





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.