Convert Variant Array to String

Question

I am trying to take a variant variable and convert it into a string so that I can run a split function on the data. However, whenever I try to redefine the variant I get a type mismatch error. I have used the CStr(), Str(), and ToString functions. None work.

Anything I am missing?

Function FlatLine(ByVal lines As Variant)

Dim flat() As String
ReDim Preserve flat(i)

For i = 0 To UBound(lines)
    flat(UBound(flat)) = lines(i)
    ReDim Preserve flat(LBound(flat) To UBound(flat) + 1)
Next i

Dim flat2 as String
flat2 = Cstr(flat)

^ errors there.

Defensive

1) flat is array of strings, flat2 is string. How are you going to convert array of strings to string? 2) why not just use ReDim flat(0 To UBound(lines))? – Rosannrosanna

I am not sure if i understand your second question (novice myself), the end game after this is to take this new string and run a split. If I try to use your suggested ReDim I get all "" values in flat. – Defensive

relatively to my second quesiont you should also use flat(i) = lines(i) in your loop and remove line ReDim Preserve flat(LBound(flat) To UBound(flat) + 1). So, the end game after this is to take this new string and run a split. - what new string? flat is array of strings, but not single string. You can't convert array of string to single string. – Rosannrosanna

@simoco "You can't convert array of string to single string" ... unless you use Join()? – Vicar

@boost, join array of strings to single string and convert array of string to string is completly different things. Last one is impossible. Join array make no sence to me in this Q - because why OP should join array of string and then split it back to array? What is the point of it? – Rosannrosanna

Then I guess I'm very confused. I can't run a split on lines and I won't be able to run a split on flat? – Defensive

I think I've misunderstood from the beginning (blush) – Vicar

@user3428722, what is your real purpose? Split takes single string and returns array of strings. As I see you already have array. What is the point of converting it to string array, then join and finally split it again? – Rosannrosanna

Because when I split the array and rewrite I was unable to get the array to not overwrite prior lines? For example: If my line said xxxxxx | xxxxxxx. I would want to append 2 lines to the array after the split. I was unsure of how to do this so I figured I'd just make another string and split that. – Defensive

Answer 1

The for is useless, as far as I can see. Better ReDim flat and generate flat2 as below

ReDim flat(UBound(lines))
flat2 = Join(flat,"|")

in fact, given that lines is coming in as ByVal you could probably

flat2 = Join(lines,"|")

Vicar

Thanks for the suggestion. The delimter is going to be a "|", unfortunately i have no non blank rows. – Defensive