Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
Excel automation
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 ... 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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
Excel automation
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 |
#4
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Excel automation
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 |
#5
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
Excel automation
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" wrote in message ... 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 ... 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 |
#6
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Excel automation
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" wrote in message ... 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 |
#7
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Excel automation
Bingo!! Thanks guys. Assigning it to a string solved the problem.
Martin "Microchip" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Excel with automation | Excel Discussion (Misc queries) | |||
Excel Automation | Excel Programming | |||
Excel Automation using .NET | Excel Programming | |||
Automation : from Excel to VB ? | Excel Programming | |||
Excel automation | Excel Programming |