Error 1004 - Application or Object Defined Error
in [Programming]
|
Prev: NumberFormat not getting changing onFly changing the regional sett
Next: Automation of Excel Report using Access Data
From: NoSpam on 28 May 2010 09:08 I am getting this error but the error is not arising in any application or object I created so it must be coming from Excel, VBA, or other MS writtern code. Is there any way to find out specifically what the error means? The VBA Error(Err) function is returning this error. Thanks.
From: Dave Peterson on 28 May 2010 09:48 This is a common error in excel macros that aren't behaving as intended. It's almost a catchall error code for lots and lots of different problems. If this error only occurs when a single workbook is open, then there's a good chance that the error is in the code in that workbook's project. If you're not the developer of the macro, then go back to that developer and explain what's happening. If the error occurs at various times and you can't really isolate the problem, then you'll have some detective work to do. Chip Pearson has some notes on how to diagnose errors: http://www.cpearson.com/excel/StartupErrors.htm And Jan Karel Pieterse has more notes: http://www.jkp-ads.com/Articles/StartupProblems.asp Essentially, you'll turn off all the addins/startup workbooks and then open excel. And then turn on each addin (closing and reopening) one at a time to find the culprit. NoSpam(a)aol.com wrote: > > I am getting this error but the error is not arising in any application or > object I created so it must be coming from Excel, VBA, or other MS writtern > code. Is there any way to find out specifically what the error means? The > VBA Error(Err) function is returning this error. > > Thanks. -- Dave Peterson
From: NoSpam on 28 May 2010 10:41 Thanks so much for your reply. I am the developer and I am trying to open a file which does exist. The code has worked before and I have no idea why it has stopped working. I have just added a LOT of code to portions of a .xla which SHOULD have no effect on this portion. Obviously, I accidentially change something in relevent code, or something in code I intended to change is having an unanticipated side effect. The code that is failing is: With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & Path & "\" & FileName, _ Destination:=Range("A2")) .Name = "SCCI-S10" '===Removing the code between these comments does not prevent the error .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 1254 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True '===Removing the code between these comments does not prevent the error .Refresh BackgroundQuery:=False End With Thanks for any additional clues On Fri, 28 May 2010 08:48:27 -0500, Dave Peterson <petersod(a)verizonXSPAM.net> wrote: >This is a common error in excel macros that aren't behaving as intended. > >It's almost a catchall error code for lots and lots of different problems. > >If this error only occurs when a single workbook is open, then there's a good >chance that the error is in the code in that workbook's project. > >If you're not the developer of the macro, then go back to that developer and >explain what's happening. > >If the error occurs at various times and you can't really isolate the problem, >then you'll have some detective work to do. > >Chip Pearson has some notes on how to diagnose errors: >http://www.cpearson.com/excel/StartupErrors.htm > >And Jan Karel Pieterse has more notes: >http://www.jkp-ads.com/Articles/StartupProblems.asp > >Essentially, you'll turn off all the addins/startup workbooks and then open >excel. And then turn on each addin (closing and reopening) one at a time to >find the culprit. > >NoSpam(a)aol.com wrote: >> >> I am getting this error but the error is not arising in any application or >> object I created so it must be coming from Excel, VBA, or other MS writtern >> code. Is there any way to find out specifically what the error means? The >> VBA Error(Err) function is returning this error. >> >> Thanks.
From: NoSpam on 28 May 2010 10:47 Oops - I should have mentioned that the worksheet was just created, so there are no already existing queries.
From: NoSpam on 28 May 2010 11:34
Weird - I commented out some code and the problem went away. Then I started restoring the code in sections until, testing each time. Finally I uncommented all the code and the error was still gone even though the restored code that worked was 100 % identical to the code that previously failed. On Fri, 28 May 2010 10:47:04 -0400, NoSpam(a)aol.com wrote: >Oops - I should have mentioned that the worksheet was just created, so >there are no already existing queries. |