Excel automation

[Martin Walke]

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

 

[Mike Fogleman]

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

 

[R. MacDonald]

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

 

[Guest]

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.

 

[Martin Walke]

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

 

[Martin Walke]

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

Martin

 

[Martin Walke]

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

Martin