Excel automation

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

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
"Martin Walke" <martin.walke_no_spam@vega_dot_co_dot_uk> wrote in message
news:uOSp%230kUGHA.5588@TK2MSFTNGP10.phx.gbl...
> 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
>
>
>
>

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" 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
>
>
>
>
>

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


"Mike Fogleman" <mikefogleman@insightbb.com> wrote in message
news:uOFgXtlUGHA.5588@TK2MSFTNGP10.phx.gbl...
>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
> "Martin Walke" <martin.walke_no_spam@vega_dot_co_dot_uk> wrote in message
> news:uOSp%230kUGHA.5588@TK2MSFTNGP10.phx.gbl...
>> 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
>>
>>
>>
>>
>
>

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

Martin

"Microchip" <Microchip@discussions.microsoft.com> wrote in message
news:79DEAA95-B6DD-41F9-9EE1-D25A3A6D92FC@microsoft.com...
>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" 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
>>
>>
>>
>>
>>

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

Martin

"Microchip" <Microchip@discussions.microsoft.com> wrote in message
news:79DEAA95-B6DD-41F9-9EE1-D25A3A6D92FC@microsoft.com...
>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" 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
>>
>>
>>
>>
>>