使用 VBA 将文本转换为 Excel 中的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12431231/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert text to columns in Excel using VBA
提问by user1112251
I'm trying to convert text to columns using a macro but I'm not able to do it, I have tried to record a macro to achieve this, however I'm running into some issues since the text to columns VBA function expects a selection, is there a way I can dinamically chose let's say range A7:A50000? or even better A7:lastnonempty cell?
我正在尝试使用宏将文本转换为列，但我无法做到，我试图记录一个宏来实现这一点，但是我遇到了一些问题，因为文本到列 VBA 函数需要一个选择，有没有办法我可以动态选择让我们说范围 A7:A50000？甚至更好的 A7:lastnonempty 单元格？
Thanks,
谢谢，
FYI, the delimeter is not important since I need to do this to convert text into formula
仅供参考，分隔符并不重要，因为我需要这样做才能将文本转换为公式
Here is my code
这是我的代码
Range("O6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("O6"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
回答by chris neilsen
Try this
尝试这个
Sub Txt2Col()
Dim rng As Range
Set rng = [C7]
Set rng = Range(rng, Cells(Rows.Count, rng.Column).End(xlUp))
rng.TextToColumns Destination:=rng, DataType:=xlDelimited, ' rest of your settings
Update: button click event to act on another sheet
更新：按钮单击事件以作用于另一张纸
Private Sub CommandButton1_Click()
Dim rng As Range
Dim sh As Worksheet
Set sh = Worksheets("Sheet2")
With sh
Set rng = .[C7]
Set rng = .Range(rng, .Cells(.Rows.Count, rng.Column).End(xlUp))
rng.TextToColumns Destination:=rng, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=True,
Space:=False,
Other:=False, _
FieldInfo:=Array(Array(1, xlGeneralFormat), Array(2, xlGeneralFormat), Array(3, xlGeneralFormat)), _
TrailingMinusNumbers:=True
End With
End Sub
Note the .'s (eg .Range) they refer to the Withstatement object
注意.'s (eg .Range) 它们指的是With语句对象
回答by Bhanupratap Tomar
If someone is facing issue using texttocolumns function in UFT. Please try using below function.
如果有人在 UFT 中使用 texttocolumns 函数遇到问题。请尝试使用以下功能。
myxl.Workbooks.Open myexcel.xls
myxl.Application.Visible = false `enter code here`
set mysheet = myxl.ActiveWorkbook.Worksheets(1)
Set objRange = myxl.Range("A1").EntireColumn
Set objRange2 = mysheet.Range("A1")
objRange.TextToColumns objRange2,1,1, , , , true
Here we are using coma(,) as delimiter.
这里我们使用 coma(,) 作为分隔符。
