Run time error 1004

jeskit · January 14th, 2011, 01:15 PM

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

mtranchi · January 14th, 2011, 01:35 PM

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.

mtranchi · January 14th, 2011, 01:39 PM

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.

mtranchi · January 14th, 2011, 01:42 PM

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

mtranchi · January 14th, 2011, 02:08 PM

did i lose ya?

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

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")

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

did you rename the sheet to "DealerExtracts" in the workbook?

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

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"

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

can you post the complete code again please?

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

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