255 char cutoff populating cells with a variant array

[rljenki1]

Hello All,
I am having a problem populating an excel (Excel 2k on win2k) worksheet. I am using a two dimensional variant array which contains strings of various sizes, and generally this technique works great.

The code is something like

worksheet(1).Range(cells(1,1), cells(100,100)).resize(100,100) = myVariantArray

The problem I have is that I can't populate a cell with more the 255 chars at a time. The data is very often larger than this and so gets cut off at 255 chars. There is no error message or anything, it works as normal, but all the data isn't loaded.

I have been stuck on this problem for ages (weeks!) and I have developed a pretty clunky work-around but efficiency is key and I would love to be able to use the variant array method.

If anyone has any ideas they would be very appreciated.

Ross J.

 

[tusharm]

Yes, it is a reasonably popular method of exchanging data between XL and VBA. But, how often do people have a string longer than 1,823 characters? What are you trying to do? The new and improved version of "War & Peace in a single cell?"

In any case, I've forwarded this to MS. Just don't hold your breath awaiting a resolution.

{snip}
I had a quick google search with no luck. You would think more people would have hit this barrier. This must be a pretty popular method of populating a worksheet.
{snip}

 

[tusharm]

Follow up: I forwarded the problem to MS and spoke with someone there. The problem is being researched as to whether it is a 'feature' or a 'bug'. It would help if you can provide examples of when you use such long strings.

Like I wrote the first time around. Don't hold your breath awating a resolution that you find satisfactory.

 

[Andrew Poulsom]

Follow up: I forwarded the problem to MS and spoke with someone there. The problem is being researched as to whether it is a 'feature' or a 'bug'. It would help if you can provide examples of when you use such long strings.

Like I wrote the first time around. Don't hold your breath awating a resolution that you find satisfactory.

Here is an example:

Sub T()
    Dim MyArr() As Variant
    Dim Rng As Range
    Dim c As Range
    Dim Msg As String
    MyArr = Array(String(500, "a"), String(1000, "b"), String(2000, "c"))
    Set Rng = Range(Cells(1, 1), Cells(1, 3))
    Rng.Value = MyArr
    For Each c In Rng
        Msg = Msg & c.Address(False, False) & " is " & Len(c) & " characters" & vbCrLf
    Next c
    MsgBox Msg
End Sub

 

[tusharm]

You've got to be kidding. You really think I need help coming up with a made-up example?

What would help is to understand what *real* problem (plural would be even better) calls for the transfer from a variant array element to a worksheet range of a string of length > 1823 characters.

{snip}It would help if you can provide examples of when you use such long strings.

Like I wrote the first time around. Don't hold your breath awating a resolution that you find satisfactory.

{snip}

 

[Andrew Poulsom]

You've got to be kidding. You really think I need help coming up with a made-up example?

Well I did wonder.

But I don't really understand why the nature of the problem(s) makes any difference.

 

[tusharm]

Wow! I am impressed by the speed of the response. Here's what I got:

a) This functionality is by design. There are other limitations that make this necessary;

b) The workaround is to simply populate each cell on at a time from the array. The following code, which writes each value one at a time, places the entire string in the cell without truncation:

Dim x
ReDim x(1 To 2, 1 To 2)
x(1, 1) = String(2000, "a"): x(1, 2) = String(5000, "b")
x(2, 1) = String(17000, "c"): x(2, 2) = String(33000, "d")
MsgBox Len(x(1, 1)) & "," & Len(x(1, 2)) _
& "," & Len(x(2, 1)) & "," & Len(x(2, 2))
'[a1].Resize(2, 2).Value = x
Range("a1").Value = x(1, 1)
Range("b1").Value = x(1, 2)
Range("a2").Value = x(2, 1)
Range("b2").Value = x(2, 2)

Obviously, one could also loop through the array with two nested For clauses.

 

[rodrigorpinto]

I'm having a similar issue and I didn't find anything related...

I'm trying to use a string to open a file. Eventually this path can be bigger than 255 characters giving me a "Method 'Open' of object 'Workbooks' failed."

I also tested with shorter strings and it worked pretty well.

Does anyone has any idea?

Thanks in advance...

 

[Andrew Poulsom]

Welcome to MrExcel.

It's documented here:

http://support.microsoft.com/kb/213983

and the only workaround is to make the path shorter by moving/renaming.

 

[rodrigorpinto]

Thanks a lot for that Andrew!!! You are a star.

I'll try to create a function to rename a file. With that in mind I can rename a big name to "a.xlsm" and after everything I can rename it back to it's original name... should work this way.

I'll let you know my progress on this.

Thanks again,
Rodrigo

Welcome to MrExcel.

It's documented here:

http://support.microsoft.com/kb/213983

and the only workaround is to make the path shorter by moving/renaming.

 

[rodrigorpinto]

Public Function RenameFile(ByVal strDirPath As String, ByVal OldName As String, ByVal NewName As String) As String
' This function renames a file strDirPath\old_nm to strDirPath\new_nm
 
Dim old_nm As String
Dim new_nm As String
 
old_nm = strDirPath & OldName
new_nm = strDirPath & NewName
 
Name old_nm As new_nm
 
End Function

I'm trying to use a function to rename the file before opening it. the old_nm is bigger than 255. IT looks like I can't use strings bigger than 255 characters even here to rename a file. Is that right? Does anyone can help here?

Thanks,
Rodrigo