Turn Excel range into VBA string

Question

I would like to turn values in given range into VBA string where original cell values are separated by any chosen column delimiter and row delimiter. Delimiters could be one character or longer strings. The row delimiter is the string at the end of the line. The string should be done just as we read text from left top corner, from left to right, to bottom right corner.

Here is an example of the VALUES in range A1:C5:

+----+----+----+
| A1 | B1 | C1 |
+----+----+----+
| A2 | B2 | C2 |
+----+----+----+
| A3 | B3 | C3 |
+----+----+----+
| A4 | B4 | C4 |
+----+----+----+
| A5 | B5 | C5 |
+----+----+----+

Desired results is a VBA string:

A1,B1,C1@$A$2,$B$2,$C$2@A3,B3,C3@A4,B4,C4@A5,B5,C5@

For the sake of readability I will show it like this:

A1,B1,C1@
A2,B2,C2@
A3,B3,C3@
A4,B4,C4@
A5,B5,C5@

As a column delimiter I have chosen , (comma), and as a row delimiter @ sign. Of course these could be any characters like \r\n.

The reason why I want fast cooking of the string from range is because I want to to send it to SQL Server through ADO connection. As I have tested so far it is the fastest way to transfer lots of data on the fly. The twin question how to split this string on SQL Server is here: Split string into table given row delimiter and column delimiter in SQL server

Solution 1. Loop through all rows and columns. Question is if there be any more elegant way then just looping through all rows and columns? I would prefer VBA solution, not formula one.

Solution 2. Suggested by Mat's Mug in comment. CSV file is desired results. I would like to do it on the fly without saving. But good point - imitate CSV is what I want but I want it without saving.

Edit after bounty

Answer of Thomas Inzina works crazy fast and his solution is portable. Ordinary VBA loop turned out to be way faster then worksheet functions like JOIN on large data sets. I do not recommend using worksheet functions in VBA for that purpose. I have voted up everybody. Thank you all.

Answer 1

To optimize performance my function emulates a String Builder.

Variables

• Text: A very large string to hold the data
• CELLLENGTH: A contant that determines the size of the BufferSize
• BufferSize: The initial size of Text string
• Data(): An Array derived from the source range

As the rows and columns of the Data() array are iterated over the current element (Data(x, y)) value replaces a portion of the Text string. The text string is resized as needed. This reduces the number of concatenations immensely. The initial BufferSize is set pretty high. I got my best results, 0.8632813 Second(s), by reducing CELLLENGTH to 25.

Download Sample Data from Sample-Videos.com

Results

Code

Function getRangeText(Source As Range, Optional rowDelimiter As String = "@", Optional ColumnDelimiter As String = ",")
    Const CELLLENGTH = 255
    Dim Data()
    Dim text As String
    Dim BufferSize As Double, length As Double, x As Long, y As Long
    BufferSize = CELLLENGTH * Source.Cells.Count
    text = Space(BufferSize)

    Data = Source.Value

    For x = 1 To UBound(Data, 1)
        If x > 1 Then
            Mid(text, length + 1, Len(rowDelimiter)) = rowDelimiter
            length = length + Len(rowDelimiter)
        End If

        For y = 1 To UBound(Data, 2)
            If length + Len(Data(x, y)) + 2 > Len(text) Then text = text & Space(CDbl(BufferSize / 4))
            If y > 1 Then
                Mid(text, length + 1, Len(ColumnDelimiter)) = ColumnDelimiter
                length = length + Len(ColumnDelimiter))
            End If

            Mid(text, length + 1, Len(Data(x, y))) = Data(x, y)
            length = length + Len(Data(x, y))
        Next
    Next

    getRangeText = Left(text, length) & rowDelimiter
End Function

Test

Sub TestGetRangeText()
    Dim s As String
    Dim Start: Start = Timer

    s = getRangeText(ActiveSheet.UsedRange)

    Debug.Print "Execution Time: "; Timer - Start; "Second(s)"
    Debug.Print "Rows: "; ActiveSheet.UsedRange.Rows.Count; "Columns: "; ActiveSheet.UsedRange.Columns.Count
    Debug.Print "Result Length: "; Format(Len(s), "#,###")
End Sub

Answer 2

Here's a quick way to test (Note: this will only work with Excel 2016 (or if you have the TextJoin() function).

First, in the empty column D, do =C1&"@", so you get your last column filled with the cell+@

Then, say in cell E1, =TEXTJOIN(",",TRUE,A1:C5) (Note: TRUE there means to skip blanks. If you have blanks, and want to keep them, change that to FALSE).

THen, on that cell, run

=Substitute(E1,"@,","@")

Or combine the formulas into one: =SUBSTITUTE(TEXTJOIN(",",TRUE,A1:C4),"@,","@").

If you need vba, just throw the formula into a VBA macro and run like that.

Answer 3

Here is a UDF that returns the desired output:

EDIT Changed to add EOL at the end.

Option Explicit
Function MultiJoin(Rng As Range, Delimiter As String, EOL As String) As String
    Dim V As Variant, W As Variant
    Dim COL As Collection
    Dim I As Long, J As Long

V = Rng
Set COL = New Collection
ReDim W(1 To UBound(V, 2))
For I = 1 To UBound(V, 1)
    For J = 1 To UBound(V, 2)
        W(J) = V(I, J)
    Next J
    COL.Add W
Next I

ReDim V(1 To COL.Count)
For I = 1 To COL.Count
    V(I) = Join(COL(I), Delimiter)
Next I

W = Join(V, EOL)
MultiJoin = W & EOL

End Function

One could shorten the code by using WorksheetFunctions, but I would guess execution time would be slower.

Shortened Code

Option Explicit
Function MultiJoin(Rng As Range, Delimiter As String, EOL As String) As String
    Dim V As Variant, W As Variant
    Dim I As Long, J As Long

V = Rng
With WorksheetFunction

For I = 1 To UBound(V, 1)
    V(I, 1) = Join(.Index(V, I, 0), Delimiter)
Next I
MultiJoin = Join(.Transpose(.Index(V, 0, 1)), EOL) & EOL

End With

End Function

Answer 4

This solution will require either a reference to the Microsoft Forms 2.0 Object Library in your project or some other way of fetching the contents of the clipboard (like through an API call).

Function TurnExcelRangeIntoVBAString(Optional cellDelimiter As String = ",", _
                                     Optional rowDelimiter As String = "@") _
         As String

    Dim rng As Range
    Set rng = ActiveSheet.UsedRange
    rng.Copy

    Dim clip As New MSForms.DataObject
    Dim txt As String
    clip.GetFromClipboard
    txt = clip.GetText()
    txt = Replace(Replace(txt, vbTab, cellDelimiter), vbCrLf, rowDelimiter)

    TurnExcelRangeIntoVBAString = txt
End Function

Answer 5

you could try this

Option Explicit

Sub main()
    Dim strng As String
    Dim cell As Range

    With Worksheets("TurnRangeIntoString") '<--| change "TurnRangeIntoString" to your actual worksheet name
        For Each cell In Intersect(.UsedRange, .Columns(1)) '<--| loop through its column 1 cells
            strng = strng & Join(Application.Transpose(Application.Transpose(.Range(cell, cell.End(xlToRight)).value)), ",") & "@" '<--| build string
        Next cell
    End With
    MsgBox strng
End Sub

Answer 6

Sub aquatique()
dim a(),s$,i&,j&:a=selection.value
for i=1 to ubound(a)
for j=1 to ubound(a,2)
    if j=1 then
        if i=1 then
            s=  a(i,j)
        else
            s=s &"@" & vbnewline & a(i,j)
        end if
    else
        s=s &";" & a(i,j)
    end if
next
next
end sub

simple but does the job. Slow on huge ranges, you'd need to use "join"

Answer 7

How about this?:

Sub Concatenate()
Dim Cel As Range, Rng As Range
Dim sString As String, r As Long, c As Long, r2 As Long

Set Rng = Selection
r = Selection.Row
c = Selection.Column
r2 = Selection.Row
For Each Cel In Rng
    r = Cel.Row
    If sString = "" Then
        sString = Cel.Value
        Else
            If r <> r2 Then sString = sString & "@" & Cel.Value
            If r = r2 Then sString = sString & "," & Cel.Value
    End If
    r2 = Cel.Row
Next

sString = sString & "@"
Debug.Print sString

End Sub