Excel automation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Martin Walke - 28 Mar 2006 10:50 GMT

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

Reply to this Message

Mike Fogleman - 28 Mar 2006 12:32 GMT

I have been told a cell can hold 32k, but I have never tried to push it to
that limit. Perhaps if you posted the code that does the transfer we can
find where the break-down is. Normally Excel imports from a database with 1
field entry per cell, not all in 1 cell.
Mike F

> Hi all,
>
[quoted text clipped - 11 lines]
> TIA
> Martin

Reply to this Message

Martin Walke - 28 Mar 2006 14:35 GMT

Mike,

I am transferring one field to one cell as you can see from the code.

Do While Not MySet.EOF
WkSheet.Rows.Cells(Row, 1) = MySet("ID")
WkSheet.Rows.Cells(Row, 2) = MySet("Text") '<-- this line bombs when
MySet("text") > 1000 ish
WkSheet.Range("B" & Row).WrapText = True
NextRow:
MySet.MoveNext
Row = Row + 1
Loop

The field type is memo and based on Microchips answer it may be better to
assign it to a known VB type before assigning to the cell. I'll try it.

Thanks
Martin

>I have been told a cell can hold 32k, but I have never tried to push it to
>that limit. Perhaps if you posted the code that does the transfer we can
[quoted text clipped - 17 lines]
>> TIA
>> Martin

Reply to this Message

R. MacDonald - 28 Mar 2006 12:43 GMT

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

> Hi all,
>
[quoted text clipped - 10 lines]
> TIA
> Martin

Reply to this Message

Microchip - 28 Mar 2006 12:50 GMT

I was getting the same problem. Check your variable declarations carry the
correct types. E.g. Byte 0-255, Integer -32,768 to 32,767, Single, Double
etc.

> Hi all,
>
[quoted text clipped - 10 lines]
> TIA
> Martin

Reply to this Message

Martin Walke - 28 Mar 2006 14:36 GMT

Thanks Microchip. You can see from the code (posted against Mike) that I
don;t use any variables but perhaps i need to!

Martin

>I was getting the same problem. Check your variable declarations carry the
> correct types. E.g. Byte 0-255, Integer -32,768 to 32,767, Single, Double
[quoted text clipped - 16 lines]
>> TIA
>> Martin

Reply to this Message

Martin Walke - 28 Mar 2006 14:55 GMT

Bingo!! Thanks guys. Assigning it to a string solved the problem.

Martin

Reply to this Message