vba Excel 宏文本到列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21378412/
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
Excel Macro Text to Columns
提问by thaking
I want to write "macro" for my .csv files. I need to perform first on data "text do columns", but when I tried to record macro I get an error:
我想为我的 .csv 文件编写“宏”。我需要首先对数据“文本做列”执行，但是当我尝试记录宏时出现错误：
"too many line continuation".
“太多的行延续”。
I select collumn A and then pressed "text to Columns".
我选择列 A，然后按“文本到列”。
Code of macro:
宏代码：
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+e
'
Selection.TextToColumns Destination := Range("A1"), DataType := xlDelimited, _
TextQualifier := xlDoubleQuote, ConsecutiveDelimiter := FALSE, Tab := FALSE, _
Semicolon := FALSE, Comma := TRUE, Space := FALSE, Other := FALSE, FieldInfo _
:= Array(Array(1,1),Array(2,1),Array(3,1),Array(4,1),Array(5,1),Array(6,1), _
Array(7,1),Array(8,1),Array(9,1),Array(10,1),Array(11,1),Array(12,1),Array(13,1 _
),Array(14,1),Array(15,1),Array(16,1),Array(17,1),Array(18,1),Array(19,1),Array _
(20,1),Array(21,1),Array(22,1),Array(23,1),Array(24,1),Array(25,1),Array(26,1), _
Array(27,1),Array(28,1),Array(29,1),Array(30,1),Array(31,1),Array(32,1),Array( _
33,1),Array(34,1),Array(35,1),Array(36,1),Array(37,1),Array(38,1),Array(39,1), _
Array(40,1),Array(41,1),Array(42,1),Array(43,1),Array(44,1),Array(45,1),Array( _
46,1),Array(47,1),Array(48,1),Array(49,1),Array(50,1),Array(51,1),Array(52,1), _
Array(53,1),Array(54,1),Array(55,1),Array(56,1),Array(57,1),Array(58,1),Array( _
59,1),Array(60,1),Array(61,1),Array(62,1),Array(63,1),Array(64,1),Array(65,1), _
Array(66,1),Array(67,1),Array(68,1),Array(69,1),Array(70,1),Array(71,1),Array( _
72,1),Array(73,1),Array(74,1),Array(75,1),Array(76,1),Array(77,1),Array(78,1), _
Array(79,1),Array(80,1),Array(81,1),Array(82,1),Array(83,1),Array(84,1),Array( _
85,1),Array(86,1),Array(87,1),Array(88,1),Array(89,1),Array(90,1),Array(91,1), _
Array(92,1),Array(93,1),Array(94,1),Array(95,1),Array(96,1),Array(97,1),Array( _
98,1),Array(99,1),Array(100,1),Array(101,1),Array(102,1),Array(103,1),Array(104 _
,1),Array(105,1),Array(106,1),Array(107,1),Array(108,1),Array(109,1),Array(110, _
1),Array(111,1),Array(112,1),Array(113,1),Array(114,1),Array(115,1),Array(116,1 _
),Array(117,1),Array(118,1),Array(119,1),Array(120,1),Array(121,1),Array(122,1) _
,Array(123,1),Array(124,1),Array(125,1),Array(126,1),Array(127,1),Array(128,1), _
Array(129,1),Array(130,1),Array(131,1),Array(132,1),Array(133,1),Array(134,1)
End Sub
How can I solve this problem ?
我怎么解决这个问题 ？
----------------------- EDIT ----------------------------
- - - - - - - - - - - - 编辑 - - - - - - - - - - - - - ——
I tried to change code as you suggest:
我尝试按照您的建议更改代码：
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+e
'
Selection.TextToColumns Destination := Range("A1"), DataType := xlDelimited, TextQualifier := xlDoubleQuote, ConsecutiveDelimiter := FALSE, Tab := FALSE, Semicolon := FALSE, Comma := TRUE, Space := FALSE, Other := FALSE, FieldInfo _
:= Array(Array(1,1),Array(2,1),Array(3,1),Array(4,1),Array(5,1),Array(6,1), Array(7,1),Array(8,1),Array(9,1),Array(10,1),Array(11,1),Array(12,1),Array(13,1),Array(14,1),Array(15,1),Array(16,1),Array(17,1),Array(18,1),Array(19,1) _
End Sub
BUt after tried to save it, I get an error "Compile error: invalid character at last lin of Array(19,1)_"
但是在尝试保存它之后，我收到一个错误“编译错误：Array(19,1)_ 的最后一个 lin 字符无效”
I tried to delete _ but it was same error.
我试图删除 _ 但它是同样的错误。
回答by Kazimierz Jawor
Too many lines continuationrefers to characters which you will find at the and of most of the lines inside the code you presented: _(underscore). What you need to do is to compact the code by adding some of the lines into the other.
Too many lines continuation指的是您将在您提供的代码中的大多数行中找到的字符：（_下划线）。您需要做的是通过将一些行添加到另一行来压缩代码。
In other words- you need to have less lines of code but make them longer.
换句话说 - 您需要更少的代码行，但使它们更长。
Example:
例子：
'instead of this part of your code:
Selection.TextToColumns Destination := Range("A1"), DataType := xlDelimited, _
TextQualifier := xlDoubleQuote, ConsecutiveDelimiter := FALSE, Tab := FALSE, _
Semicolon := FALSE, Comma := TRUE, Space := FALSE, Other := FALSE, FieldInfo _
:= Array(Array(1,1),Array(2,1),Array(3,1),Array(4,1),Array(5,1),Array(6,1), _
Array(7,1),Array(8,1),Array(9,1),Array(10,1),Array(11,1),Array(12,1),Array(13,1 _
),Array(14,1),Array(15,1),Array(16,1),Array(17,1),Array(18,1),Array(19,1),Array _
'...etc
'you should try to compact it in this way:
Selection.TextToColumns Destination := Range("A1"), DataType := xlDelimited, TextQualifier := xlDoubleQuote, ConsecutiveDelimiter := FALSE, Tab := FALSE, Semicolon := FALSE, Comma := TRUE, Space := FALSE, Other := FALSE, FieldInfo _
:= Array(Array(1,1),Array(2,1),Array(3,1),Array(4,1),Array(5,1),Array(6,1), Array(7,1),Array(8,1),Array(9,1),Array(10,1),Array(11,1),Array(12,1),Array(13,1),Array(14,1),Array(15,1),Array(16,1),Array(17,1),Array(18,1),Array(19,1),Array _
'...etc
IMPORTANTWhen doing this please be sure that you left (empty) space before underscore at the end of each lines. You need to have something like:
重要执行此操作时，请确保在每行末尾的下划线之前留有（空）空格。你需要有这样的东西：
...) _
..., _
....), _
回答by L42
If you're data type is ther same for each field, you can just use this:
如果每个字段的数据类型都相同，则可以使用以下命令：
Selection.TextToColumns Destination:= Range("A1"), DataType:= xlDelimited, Comma:=True
I think that should do it?
Hope this helps.
我认为应该这样做吗？
希望这可以帮助。
回答by Developer
Kindly try this code Which prompts the user to select a range then it applies the text to column feature. Note: Parameter values may change depending on the data set that your working with. -> Source Link
请尝试此代码，它提示用户选择一个范围，然后将文本应用于列功能。注意：参数值可能会根据您使用的数据集而变化。->源链接
Sub TextToColumnsRangeSelection()
On Error Resume Next
Dim MySelection As Range
Application.DisplayAlerts = False
Set MySelection = Application.InputBox(Prompt:="Please select your range", Title:="Select Range", Type:=8)
MySelection.TextToColumns _
Destination:=MySelection, _
DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False
End Sub
