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 14th, 2011, 01:15 PM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

I am very sorry. its was fine when it was put data in excel doucment holding macro but then when it got to opening a new doucment it went mad!!!

this is the code you gave me which i altered

Code:
Sub Open_Csv_And_ExtractSize_CountRows()

Dim i As Integer 'for looping

'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

'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").Activate

'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
lNextRow = WorksheetFunction.CountA(wsDealerExtracts.Range("F17:H38"))

'Loop through the csv files (in this case only one)
For i = 1 To 1
    Set wbCsv = Workbooks.Open("C:\Production2\ATX\Extracts\201001\DealerData\*.csv")
    Set wsMyCsvSheet = wbCsv.Sheets(1)
    With wbCsv
        .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

wbExtractSize.Save
'wbExtractSize.Close

'clean up
Set wbExtractSize = Nothing
Set wbCsv = Nothing
Set wsDealerExtracts = Nothing
Set wsMyCsvSheet = Nothing

End Sub
 
Old January 14th, 2011, 01:35 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

ok right now we're not trying to loop through anything so you can't use the asterisk in the set statement. For now just make a new file called myCsvFile.csv in the relevant folder and put exactly what i put in mine, refer back to what i've already posted if you need to:
Code:
'wrong
    Set wbCsv = Workbooks.Open("C:\Production2\ATX\Extracts\201001\DealerData\*.csv")

'right
    Set wbCsv = Workbooks.Open("C:\Production2\ATX\Extracts\201001\DealerData\myCsvFile.csv")
Also, the varaible lNextRow is a replacement for that "xlUp" stuff you were doing before, it's what finds the next row to put the data into so you don't want it referring across multiple columns. You want it to count the number of rows in ExtractSize.xls that have data already in them, like the comment says, I add two to lNextRow because Counta counts cells that have something in them, and since I have a blank row at the top and we want the next AVAILABLE row, we add 2. Just make a new workbook called ExtractSize and put it in the relevant folder with EXACTLY what i have in mine, refer back to what i've already posted if you need to
Code:
'incorrect
lNextRow = WorksheetFunction.CountA(wsDealerExtracts.Range("F17:H38"))

'correct
lNextRow = WorksheetFunction.CountA(wsDealerExtracts.Range("A:A")) + 2
and lastly, you almost never want to activate a workbook as it only slows things down
Code:
'incorrect
Set wsDealerExtracts = wbExtractSize.Sheets("DealerExtracts").Activate

'correct
Set wsDealerExtracts = wbExtractSize.Sheets("DealerExtracts")
and we'll take it from there.
 
Old January 14th, 2011, 01:39 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

one other thing, we're not keeping this all in the same thread so it's going to be useless to anyone else who comes along and tries to get something from our conversation. Dunno what you're doing, but sometimes you start new threads and sometimes you don't. Let's try to keep our conversation on the same thread.
 
Old January 14th, 2011, 01:42 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

lol forget what i just said, this is the first forum where i've seen it become paginated, didn't realize we're still on the same thread, just we're making a book, lol
 
Old January 14th, 2011, 02:08 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

did i lose ya?
 
Old January 17th, 2011, 04:25 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

hi

Sorry i finsihed for the day.

I have made the changes you reccommended but i am now getting an error message of "subscript out of range" when it gets to this line
Code:
 Set wsDealerExtracts = wbExtractSize.Sheets("DealerExtracts")
 
Old January 17th, 2011, 04:30 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

did you rename the sheet to "DealerExtracts" in the workbook?
 
Old January 17th, 2011, 04:43 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Sorry i forgot, error solved, monday morning is never good for the brain!!

However another error has occurred when it gets to this line:
Code:
 .Cells(lNextRow, 1) = WorksheetFunction.CountA(wsMyCsvSheet.Range("A:A"))
when it gets the line above an error appears saying "object doesn't support this property or method"
 
Old January 17th, 2011, 04:45 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

can you post the complete code again please?
 
Old January 17th, 2011, 04:46 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

yeah sure no problem:

Code:
Sub Open_Csv_And_ExtractSize_CountRows()

Dim i As Integer 'for looping

'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

'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("Dealer Extracts")

'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
lNextRow = WorksheetFunction.CountA(wsDealerExtracts.Range("A:A")) + 2

'Loop through the csv files (in this case only one)
For i = 1 To 1
    Set wbCsv = Workbooks.Open("C:\Production2\ATX\Extracts\201001\DealerData\Actual_Curr_Year_AT30010.csv")
    Set wsMyCsvSheet = wbCsv.Sheets(1)
    With wbCsv
        .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

wbExtractSize.Save
'wbExtractSize.Close

'clean up
Set wbExtractSize = Nothing
Set wbCsv = Nothing
Set wsDealerExtracts = Nothing
Set wsMyCsvSheet = Nothing

End Sub





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.