这是从文本文件复制数据并将其粘贴到Excel的代码。我已经用分割了文本vbNewline
,但是我也需要按空格将其分割。
是否存在被空间分割数据的任何其他方法,以及如何通过双方分割数据vbNewLine
和空间?
On Error Resume Next
Dim objFSO, strTextFile, strData, strLine, arrLines
Const ForReading = 1
path = InputBox("Enter the path :","Select Your Path !","Type your path here")
'name of the text file
strTextFile = path
'Create a File System Object
Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")
'Open the text file - strData now contains the whole file
strData = objFSO.OpenTextFile(strTextFile, ForReading).ReadAll
'typesplit=inputbox("Enter the type ","Type to split(VbnewLine (or) <Space>)","vbnewline/space")
'Split the text file into lines
arrLines = Split(strData, vbNewLine)
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
intRow = 2
k = 0
align = InputBox("Enter the type of alignment...(default/horizontal/vertical)", _
"Press h (or) v (or) d")
If (align = "v") Then
rowlimit = InputBox("Enter the row limit")
For i=1 To 10 Step 1
For j=1 To rowlimit Step 1
objExcel.Cells(j, i).Value = arrLines(k)
k = k+1
Next
Next
End If
If (align = "h") Then
collimit = InputBox("Enter the col limit")
For i=1 To 10 Step 1
For j=1 To collimit Step 1
objExcel.Cells(i, j).Value = arrLines(k)
k = k+1
Next
Next
End If
MsgBox("Conversion Finished !!")
Set objFSO = Nothing
VBScript不允许您在一个步骤中用多个定界符分割字符串。您需要通过第一个定界符对其进行拆分,然后循环遍历结果数组,并通过第二个定界符对子字符串进行拆分,依此类推。
For Each line In Split(strData, vbNewLine)
For Each word In Split(line, " ")
'do something with each word
Next
Next
如果要将所有单词放入单个数组中以供以后处理,可以将它们放入可动态调整大小的数组中:
ReDim words(-1) 'define empty resizable array
For Each line In Split(strData, vbNewLine)
For Each word In Split(line, " ")
ReDim Preserve words(UBound(words)+1)
words(UBound(words) = word
Next
Next
另外,您可以使用ArrayList
:
Set words = CreateObject("System.Collections.ArrayList")
For Each line In Split(strData, vbNewLine)
For Each word In Split(line, " ")
words.Add word
Next
Next
注意动态VBScript数组在处理大量数据时性能较差,因为每次调整数组大小时,解释器都会创建一个新数组,复制现有数组中的所有数据,然后将新数组分配给该变量。
另一方面,实例化ArrayList
对象意味着很多开销,因此对于少量数据,它的性能不如VBScript内置数组。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句