如何使用VBA宏将excel表值转换为由逗号和单引号分隔的值字符串？

Question

我有一个excel表，它有3列。我正在选择具有一定行数的这3列，我想将这些值复制到用逗号分隔的字符串中；这些值用单引号封装。但我看到转置只有一列的限制。以下是我目前正在尝试的内容。

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Dim str as string
str = Join(Application.Transpose(Selection.Value),"','")
msgbox(str)

但正如我所说，转置只适用于一列。任何想法都会有所帮助。

表格

A       B       C
1    99999   88888   77777
2    55555   66666   44444
3    22222   11111   33333

预期输出

'99999','88888','77777','55555'.....'33333'

项目顺序可以是我只想将值转换为指定输出的任何内容。

Answer 1

QuotJoin UDF

用法

在Excel:中

=QuotJoin(A2:C4)

在VBA:中

Sub QuotJoinTEST()
Dim trg As Range: Set trg = Range("A1").CurrentRegion
Dim srg As Range: Set srg = trg.Resize(trg.Rows.Count - 1).Offset(1)
Dim Result As String: Result = QuotJoin(srg)
Debug.Print Result
End Sub

功能

Function QuotJoin( _
ByVal srg As Range, _
Optional ByVal Delimiter As String = ",", _
Optional ByVal ByColumns As Boolean = False) _
As String

If srg Is Nothing Then Exit Function

Dim rCount As Long: rCount = srg.Rows.Count
Dim cCount As Long: cCount = srg.Columns.Count

Dim sData As Variant

If rCount = 1 And cCount = 1 Then
ReDim sData(1 To 1, 1 To 1): sData(1, 1) = srg.Value
Else
sData = srg.Value
End If

Dim rData() As String: ReDim rData(1 To rCount * cCount)

Dim r As Long, c As Long, n As Long

If ByColumns Then
For c = 1 To cCount
For r = 1 To rCount
n = n + 1
rData(n) = "'" & sData(r, c) & "'"
Next r
Next c
Else
For r = 1 To rCount
For c = 1 To cCount
n = n + 1
rData(n) = "'" & sData(r, c) & "'"
Next c
Next r
End If

QuotJoin = Join(rData, Delimiter)
End Function