Hello, Martin,

I suspect that you are trying to assign to the Formula property of the
cell. Try using the Value property instead.

The following works for me:

Dim appXL As New Excel.Application
Dim rngCell As Range
Dim strLong As String
Dim wbkXL As Workbook
Dim wshXL As Worksheet

Set wbkXL = appXL.Workbooks.Add
Set wshXL = wbkXL.Worksheets(1)
Set rngCell = wshXL.Cells(1, 1)

strLong = String(2048, "A")

appXL.Visible = True
rngCell.Value = strLong

This is with a reference to "Microsoft Excel 9.0 Object Library".
Version 9.0 claims that the cell will take 32767 characters (but only
display 1024). I tried the above with a string of 32767 characters and
Excel received them all gladly. Trying with more than that ended up
with fewer than 32767 characters actually in the cell, but still raised
no error.

Making the assignment to the Formula property property however will
generate an error if the length is greater than (about) 256 characters.
I tried this and found I got error # 7 (out of memory) for very long
strings. But if the length of the formula that I was trying to assign
(including the equal sign and quotation marks) was less than 1024
characters, then like you, I get a 1004 error.

That is why I suspect you may be assigning to the Formula property
instead of the Value property of the cell.

But I also have a vague recollection that some earlier version of Excel
limited the Value property to 1024 characters. If so, this might result
in an error even when you use the Value property. (I consider the lack
of an error when I exceed the limit in v9.0 to be a significant problem.)

Cheers,
Randy


Martin Walke wrote:
Hi all,

Can anyone tell me if there's a limit to the size of data that you can
insert into an excel cell using VB6 automation? I'm transferring data into a
worksheet but whenever the data is more than about 1000 bytes I get an
automation error 1004 "Application-defined or object-defined error"... but
if I manually paste the same data into the cell, there's no problem.

The data is coming straight from an access 97 database.

Any ideas?

TIA
Martin