CODEWITHSUNDEEP
vbscript
Sebastin Anthony
Sebastin Anthony

Reputation: 660

Copy data from textfile to Excel

Here is the code to copy the data from a textfile and paste it to Excel. I have split the text by vbNewline, but I also need to split it by space.

Is there any alternative method to split the data by space, and how to split the data by both vbNewLine and space?

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

Upvotes: 1

Views: 913

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200293

VBScript doesn't allow you to split a string by multiple delimiters in one step. You need to split it by the first delimiter, then loop over the resulting array and split the substrings by the second delimiter, and so on.

For Each line In Split(strData, vbNewLine)
  For Each word In Split(line, " ")
    'do something with each word
  Next
Next

If you want to put all words into a single array for later processing you'd put them into a dynamically resizable array:

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

Alternatively you could use an 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

Beware that dynamic VBScript arrays perform poorly with large amounts of data, because every time you resize the array the interpreter creates a new array, copies all data from the existing array, then assigns the new array to the variable.

On the other hand, instantiating an ArrayList object means a lot of overhead, so it doesn't perform as well as VBScript builtin arrays for small amounts of data.

Upvotes: 3

Related Questions