Run time error 1004

jeskit · January 14th, 2011, 06:13 AM

Hi

The piece of code below keeps stopping and display and error saying "Run time error 1004: Application-defined or Object-defined error"

The piece of code below should open a specific workbook, open the sheet, run the loop (which opens all the files in a folder and perofrms a counta formula) then paste the results in the workbook opened.

Code:

 Sub Get_Dealer_Count()
Dim ws As Worksheet
Dim wb As Workbook
Dim xlCalc As XlCalculation
Dim strFldr As String
Dim strFile As String

Application.ScreenUpdating = False
       
Application.Workbooks.Open ("c:\Documents and settings\SeymourJ\My Documents\Extract_Size_Checker_test.xls")
       
Set ws = Sheets("Dealer Extracts")

ws.Range("F17:H38").ClearContents
ws.Range("F17:H17").Value = [{"Directory", "Filename", "Row Number"}]

strFldr = ("C:\Production2\ATX\Extracts\201001\DealerData")

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

If Len(strFile) > 0 Then
    With Application
        xlCalc = .Calculation
        .Calculation = xlCalculationManual
    End With
    Do
        Set wb = Workbooks.Open(Filename:=strFldr & "\" & strFile)
        With ws.Cells(ws.Rows.Count, "F").End(xlUp)
            .Offset(1).Value = strFldr
            .Offset(1, 1).Value = strFile
            .Offset(1, 2).Formula = "=COUNTA('[" & wb.Name & "]" & wb.Sheets(1).Name & "'!$A:$A)"
        End With
        wb.Close False
        strFile = Dir
        Application.StatusBar = strFile
    Loop Until Len(strFile) = 0
End If

With ws.Range("F17:H17")
    .Font.Bold = False
    .EntireColumn.AutoFit
End With
        
End Sub

However when it gets to this bit:

Code:

 .Offset(1, 2).Formula = "=COUNTA('[" & wb.Name & "]" & wb.Sheets(1).Name & "'!$A:$A)"
        End With

It stops and displays the error mentioned above. I have tried it without the opening a workbook bit and it works fine but when i add the piece of code to open an exisiting workbook

Code:

 Application.Workbooks.Open ("c:\Documents and settings\SeymourJ\My Documents\Extract_Size_Checker_test.xls")

it falls over.

Can anyone help me??

Thanks

jeskit

mtranchi · January 14th, 2011, 09:02 AM

didn't read through your code thoroughly, but when you open a workbook, you want to create a reference to it just like you do worksheets.
[code]
dim wkb as Workbook
set wkb = Workbooks.Open("C:\myfile.xlsm")
[\code]
then when you use your with statements, make sure you qualify the sheet to the wkb object
hope that helps

mtranchi · January 14th, 2011, 09:10 AM

looked through your code a bit more. Although you have the Set keyword in the loop for the csv files, you don't have the Set keyword for the main workbook. Almost everytime you get the "application or object defined error" it's because you didn't "Set" an object.

jeskit · January 14th, 2011, 09:38 AM

Hi

I have tried putting the workbook as a reference but it still comes up with an error. I ahve tried running the code so that it does not open a workbook first and just puts the results in the document calling the macro and it works fine with no errors but as soon as i put

Code:

 Application.Workbooks.Open ("c:\Documents and settings\SeymourJ\My Documents\Extract_Size_Checker_test.xls")

in it falls over and i cant work out why?

jeskit · January 14th, 2011, 09:40 AM

Quote:

Originally Posted by mtranchi

looked through your code a bit more. Although you have the Set keyword in the loop for the csv files, you don't have the Set keyword for the main workbook. Almost everytime you get the "application or object defined error" it's because you didn't "Set" an object.

I am not sure i understand what you mean. Where should i reference the main workbook in the loop?

mtranchi · January 14th, 2011, 09:50 AM

becuase the workbook you're running the code from isn't the workbook that has the worksheet "Dealer Extracts". First you open the workbook with "Dealer Extracts" in it and set that equal to a variable, then when you set the worksheet "Dealer Extracts", qualify it like so:

Code:

Dim wbkMain as Workbook
Set wbkMain = Workbooks.Open("c:\Documents and settings\SeymourJ\My Documents\Extract_Size_Checker_test.xls")
'This fully qualifies the worksheet in question when you refer to it in the loop
Set ws = wbkMain.Sheets("Dealer Extracts")

If you don't fully qualify that worksheet, then it will refer to whatever workbook was active when you started running the code, and I'm guessing that's what is causing your problem.

jeskit · January 14th, 2011, 09:55 AM

Hi

Oh i get it.

I have already tried that. I set wb1 as the Extract_Size_Checker_test and set ws as wb1.sheets but it still did not work.

mtranchi · January 14th, 2011, 10:03 AM

Ok then try it with a different and simple entry into that thrid row of the loop. Instead of

Code:

.Offset(1, 2).Formula = "=COUNTA('[" & wb.Name & "]" & wb.Sheets(1).Name & "'!$A:$A)"

try something simple like

Code:

.Offset(1,2).Value = "works?"

and see if that works. But I have a feeling it has to do with the fact that whatever workbook is active when you run the code with the line that doesn't work is not the one that you want the data to go into correct? I mean, when you take that line

Code:

Application.Workbooks.Open ("c:\Documents and settings\SeymourJ\My Documents\Extract_Size_Checker_test.xls")

out of the macro, are you running the macro from the same workbook? If you are, then that line is meaningless because you never use that workbook anywhere in your code.

jeskit · January 14th, 2011, 10:09 AM

HI

ok i tried what you suggested and it worked with

Code:

 .Offset(1,2).Value = "works?"

and produced no errors.

And also to your query. yes the workbook that is running the code is not the workbook i want the results to go in i want the results to go into Extract Size checker document.

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

Ok well then what you need to do is build that formula up gradually until you find the piece that breaks it. So for example first try

Code:

'Use the formula keyword to see if that breaks it
.Offset(1,2).Formula = "works?"

then maybe

Code:

.Offset(1,2).Formula = "=5*8"

and next a Counta function and so on. Let me know when you find the piece that breaks it.