将 Excel 范围转换为 VBA 字符串

我想将给定范围内的值转换为 VBA 字符串，其中原始单元格值由任何选定的列分隔符和行分隔符分隔。分隔符可以是一个字符或更长的字符串。行分隔符是行末尾的字符串。该字符串应该像我们从左上角、从左到右、到右下角读取文本一样完成。

以下是范围 A1:C5 中的值的示例：

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

期望的结果是一个 VBA 字符串：

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

为了便于阅读，我将这样展示：

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

作为列分隔符，我选择了,（逗号），并作为行分隔符@符号。当然，这些可以是任何字符，例如\r\n.

我想要快速烹饪范围内的字符串的原因是因为我想通过 ADO 连接将其发送到 SQL Server。正如我到目前为止所测试的那样，这是即时传输大量数据的最快方法。如何在 SQL Server 上分割这个字符串的双重问题在这里：在 SQL Server 中将字符串拆分为给定行分隔符和列分隔符的表 https://stackoverflow.com/questions/39752188/split-string-into-table-given-row-delimiter-and-column-delimiter-in-sql-server

解决方案1。循环遍历所有行和列。问题是是否有比循环所有行和列更优雅的方法？我更喜欢 VBA 解决方案，而不是公式一。

解决方案2。由 Mat's Mug 在评论中建议。 CSV 文件是期望的结果。我想在不保存的情况下即时执行此操作。但好的一点是——模仿 CSV 是我想要的，但我想要它而不保存。

赏金后编辑

Thomas Inzina 的回答运行得非常快，而且他的解决方案是可移植的。事实证明，在大型数据集上，普通的 VBA 循环比 JOIN 等工作表函数要快得多。我不建议为此目的使用 VBA 中的工作表函数。我已经给大家投票了。谢谢你们。

为了优化性能，我的函数模拟了字符串生成器。

变量

• 文本：用于保存数据的非常大的字符串
• CELL LENGTH：决定 Buffer Size 大小的常量
• BufferSize：文本字符串的初始大小
• Data()：从源范围派生的数组

当 Data() 数组的行和列在当前元素上迭代时 (Data(x, y)) 值替换文本字符串的一部分。根据需要调整文本字符串的大小。这极大地减少了串联的数量。初始 BufferSize 设置得相当高。通过将 CELLLENGTH 减少到 25，我得到了最好的结果，0.8632813 秒。

从 Sample-Videos.com 下载示例数据 http://www.sample-videos.com/csv/Sample-Spreadsheet-500000-rows.csv

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