Improve speed of data transfer loop

[patrick]

Can this be done more efficiently?

' Routine moves data from text file into excel spreadsheet cells
buildsheet:
RowNdx = 3
Open MTOFile For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = 1
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend
Close #1
Return

 

[ilia]

Can this be done more efficiently?

' Routine moves data from text file into excel spreadsheet cells
buildsheet:
RowNdx = 3
Open MTOFile For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = 1
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend
Close #1
Return

Something like this. Declare a variant

Dim v as Variant
v = Split(WholeLine, sep)
Cells(RowNdx, 1).Resize(1, Ubound(v)).Value = v

That way you avoid looping since the functionality is already built
in.

Here was my test:

Public Sub mySub1()
Dim WholeLine As String
Dim sep As String

sep = ","
RowNdx = 1
Dim rng As Range
Dim v As Variant


WholeLine = "abc,def,ghi,jkl,mno,pqr,"
v = Split(WholeLine, sep)
ColNdx = 1
Pos = 1
Cells(RowNdx, 1).Resize(1, UBound(v)).Value = v

End Sub

 

[ilia]

Cleaning that up a bit:

Public Sub mySub1()
Dim WholeLine As String
Dim sep As String
Dim RowNdx As Long
Dim v As Variant

sep = ","
RowNdx = 1

WholeLine = "abc,def,ghi,jkl,mno,pqr,"
v = Split(WholeLine, sep)
ColNdx = 1
Cells(RowNdx, 1).Resize(1, UBound(v)).Value = v
End Sub