Error 1004 - Application or Object Defined Error

[Guest]

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]

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.

 

[Guest]

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

 

[Guest]

Oops - I should have mentioned that the worksheet was just created, so
there are no already existing queries.

 

[Guest]

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.

 

[Dave Peterson]

Did the error occur on the .name line? Maybe you're trying to use a QT name
that's already in use???

If you actually type out the string in that .add() portion, does the code work?

with activesheet.querytables.add(connection:="TEXT;C:\yourpath\yourfilename",...

Does it work? Maybe your variables aren't what they think they are???

 

[Dave Peterson]

Is the code in a General module -- or is it in a worksheet module.

If it's in a worksheet module, then this unqualified range (Range("A1")) refers
to the sheet that owns the code--not the activesheet.

Try qualifying the range:
...Destination:=activesheet.Range("A2"))...

 

[Dave Peterson]

I'd still keep an eye out for problems.

(But I've had the same thing happen to me. I don't usually blame excel -- I
just figured that I made a change that I thought was insignificant and it
wasn't!)