Is there a Size limitation when writing from VB - Excel?
 

Dim objData as Variant
objData = <a string
objXLWorkBook.Sheets("Sheet1").Range(<whatever).V alue = objData

If objData is a string around 200 characters long, all is fine and my
string gets written the the cell as expected. If objData is a string
around 1000 character long, I get Error number 1004
"Applicaiton-defined or object-defined error". Is there a size
limitation? I can manually enter 1000 characters into an Excel
spreadsheet just fine.

Is there a Size limitation when writing from VB - Excel?
 

Hello Tif!

Ther is probable an issue with your code. I used the following code on
my machine and it worked fine. I am running Office 2000 on Windows XP.
Try this code out on your end and see if you still have the same
problem.


Code:
--------------------
Sub Test()

Dim I, S

For I = 1 To 1000
S = Str(I) & S
Next I

Range("A1").Value = S

End Sub


--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=478008

Is there a Size limitation when writing from VB - Excel?
 

You're reply prompted me to try something different and it worked!
When I tried writing to a large range of cells using:

objXLWorkBook.Sheets("Sheet1").Range("A1:M3000").V alue = <my
multidimensional array of variants

It throws the 1004 error when the contents of a certain cell are too
big (somewhere between 200 and 1000 characters). But, if I write to
individual cells using:

objXLWorkBook.Sheets("Sheet1").Cells("A1").Value = <one element of my
multidimensional array of variants

I can enter in data as big as 6000 characters.

So, not sure "why" this works, but I'm using it! Thanks for the reply!