problem with preventing "" spreading all over the Spreadsheet

[lucpian]

Hi All,

I am having a problem with my code. It works quite alright, but the space characters are spread all over at the end of the last data entry in the Excel spreadsheet. This code basically is trying to import text files into Excel Spreadsheet, which it does very fine with the code. Please, will someone help to look at my vba code and see if there is somewhere wher I could set a defined variable with the range such as rowcount = ActiveSheet.Range(Split(c.Address, "$")(1) & "65536").End(xlUp).Row

Sub ImportText2File()
Dim sFile As String
Dim lFNum As Long
Dim vaFields As Variant
Dim i As Long
Dim lRow As Long
Dim vaStrip As Variant
Dim FileName As Variant
Dim Sep As String
Application.ScreenUpdating = False
On Error GoTo EndMacro:
lFNum = FreeFile
sFile = Application.GetOpenFilename(FileFilter:="Excel File (*.txt),*.txt")
'If sFile = False Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
' Exit Sub
'End If
Const sDELIM = "," 'Set the delimiter
lFNum = FreeFile
vaStrip = Array(vbLf, vbTab) 'list the text to strip
'Open the file
Open sFile For Input As lFNum
vaFields = GetData(lFNum, vaStrip, sDELIM)
i = LBound(vaFields)
' Loop
lRow = 1
Do While Not EOF(lFNum)
If lRow > 1 Then vaFields = GetData(lFNum, vaStrip, sDELIM)
'Write to the worksheet
For i = 0 To UBound(vaFields)
Sheet1.Cells(lRow, i + 1).Value = vaFields(i)
Next i
lRow = lRow + 1
Loop
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close lFNum
End Sub
Private Function GetData(ByVal FileNum As Long, _
ByVal Redundant As Variant, _
ByVal Delimiter As String) As Variant
Dim sInput As String
Dim i As Long
Line Input #FileNum, sInput 'input the current line
'remove the unwanted text
For i = LBound(Redundant) To UBound(Redundant)
sInput = Replace(sInput, Redundant(i), " ")
Next i
'split the text based on the delimeter
GetData = Split(sInput, Delimiter)
End Function


Thanks

Lucpian

[lucas]

If I were going to look at this with you I would request a sample excel file and a sample textfile that is giving you this problem...

[mikerickson]

Have you tried adding Trim to this line
Sheet1.Cells(lRow, i + 1).Value = Trim(vaFields(i))

[JimmyTheHand]

There is no need to read the text line by line. Workbooks.Opentext method can open and read the textfile all in one step. For example,

Sub ImportText2File()
Dim sFile As String
Dim i As Long
Dim vaStrip As Variant
Application.ScreenUpdating = False
On Error GoTo EndMacro:
sFile = Application.GetOpenFilename(FileFilter:="Excel File (*.txt),*.txt")
vaStrip = Array(vbLf, vbTab) 'list the text to strip

Workbooks.OpenText FileName:=sFile, DataType:=xlDelimited, Comma:=True, Tab:=False
For i = LBound(vaStrip) To UBound(vaStrip)
ActiveSheet.Cells.Replace vaStrip(i), " "
Next i
ActiveSheet.Cells.Copy Sheet1.Cells
ActiveWorkbook.Close SaveChanges:=False
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Is there a reason you use that long code instead of Opentext method?

Jimmy