将Excel范围转换为VBA字符串 [英] Turn Excel range into VBA string

问题描述

我想将给定范围内的值转换为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\\\
。

我想要快速烹饪的原因的字符串范围是因为我想通过ADO连接将其发送到SQL Server。迄今为止我已经测试过，它是快速传输大量数据的最快方式。在SQL Server上如何拆分此字符串的双重问题如下：在SQL服务器中将字符串拆分为给定行分隔符和列分隔符

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

解决方案2。由Mat's Mug在评论中提出建议。 CSV文件是所需的结果。我想在飞行中做到没有保存。但好点 - 模仿CSV是我想要的，但我想要它而不保存。

赏金后编辑

Thomas Inzina的回答工作疯狂，他的解决方案便携式普通的VBA循环结果证明，在大数据集上的工作表函数如JOIN更快。我不建议在VBA中使用工作表函数。我已经投了赞成票。谢谢大家。

解决方案

为了优化性能，我的函数模拟一个String Builder。

变量

• 文本：保存数据的非常大的字符串


• CELLLENGTH：确定缓冲区大小的大小


• BufferSize：文本字符串的初始大小


• Data（）：从源范围派生的数组由于Data（）数组的行和列在当前元素（ Data（x））上迭代，所以




$ b ，y））值替换文本字符串的一部分。文本字符串根据需要调整大小。这样可以大大减少连接数量。初始BufferSize设置相当高。我得到了最好的结果，0.8632813秒，通过将CELLLENGTH减少到25。

从Sample-Videos.com下载样本数据

结果< h2>

代码

$函数getRangeText（Source As Range，可选rowDelimiter As String =@，可选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 =空格（BufferSize）

Data = Source.Value

对于x = 1 To UBound（Data，1）
如果x> 1然后
Mid（text，length + 1，Len（rowDelimiter））= rowDelimiter
length = length + 1
End If

对于y = 1到UBound （数据，2）
如果length + Len（Data（x，y））+ 2> Len（text）Then text = text&空格（CDbl（BufferSize / 4））
如果y> 1然后
Mid（text，length + 1，Len（ColumnDelimiter））= ColumnDelimiter
length = length + 1
End If

Mid（text，length + 1，Len（Data（x，y）））= Data（x，y）
length = length + Len（Data（x，y））
下一个
下一个

getRangeText = Left（text，length）& rowDelimiter
结束功能

测试

  Sub TestGetRangeText（）
 Dim s As String 
 Dim开始：Start = Timer 
 
s = getRangeText（ActiveSheet.UsedRange）
 
 Debug.Print执行时间：;定时器 - 启动; Second（s）
 Debug.PrintRows：; ActiveSheet.UsedRange.Rows.Count; 列： ; ActiveSheet.UsedRange.Columns.Count 
 Debug.PrintResult Length：;格式（Len（s），＃，###）
 End Sub 
  

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.

解决方案

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 + 1
        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 + 1
            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

这篇关于将Excel范围转换为VBA字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！