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, 06:13 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default Run time error 1004

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
 
Old January 14th, 2011, 09:02 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default no variable for the workbook object

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
 
Old January 14th, 2011, 09:10 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default you use set once in the loop but not for the main workbook

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

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

Quote:
Originally Posted by mtranchi View Post
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?
 
Old January 14th, 2011, 09:50 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default because...

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

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

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

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

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.





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.