lucpian
03-14-2008, 08:25 AM
Hi All,
I wrote the following vba code as part of an automated process to import text file into an excel template, but I keep having errors whenever I run it. I have tried to fix this error, but it still does not work. Here, is the code: Sub GetTextFile()
Dim sFile As String
Dim sInput As String
Dim lFNum As Long
Dim vaFields As Variant
Dim i As Long
Dim lRow As Long
Dim vaStrip As Variant
Const sDELIM = "^" 'Set the delimeter
lFNum = FreeFile
sFile = Application.GetOpenFilename(FileFilter:="Text File (*.txt),*.txt")
If sFile = False Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If
'sFile = "C:CaratDelim.txt"
vaStrip = Array(vbLf, vbTab) 'list the text to strip
'Open the file
Open sFile For Input As lFNum
'Loop through the file until the end
Do While Not EOF(lFNum)
Line Input #lFNum, sInput 'input the current line
'remove the unwanted text
For i = LBound(vaStrip) To UBound(vaStrip)
sInput = Replace(sInput, vaStrip(i), "")
Next i
'split the text based on the delimeter
vaFields = Split(sInput, sDELIM)
lRow = lRow + 1
'Write to the worksheet
For i = 0 To UBound(vaFields)
Sheet1.Cells(lRow, i + 1).Value = vaFields(i)
Next i
Loop
Close lFNum
End Sub
Please, I would be grateful if anyone in this forum can help me out.
Thanks
Lucpian
Aussiebear: Edited thread to enclose code within VBA Tags
I wrote the following vba code as part of an automated process to import text file into an excel template, but I keep having errors whenever I run it. I have tried to fix this error, but it still does not work. Here, is the code: Sub GetTextFile()
Dim sFile As String
Dim sInput As String
Dim lFNum As Long
Dim vaFields As Variant
Dim i As Long
Dim lRow As Long
Dim vaStrip As Variant
Const sDELIM = "^" 'Set the delimeter
lFNum = FreeFile
sFile = Application.GetOpenFilename(FileFilter:="Text File (*.txt),*.txt")
If sFile = False Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If
'sFile = "C:CaratDelim.txt"
vaStrip = Array(vbLf, vbTab) 'list the text to strip
'Open the file
Open sFile For Input As lFNum
'Loop through the file until the end
Do While Not EOF(lFNum)
Line Input #lFNum, sInput 'input the current line
'remove the unwanted text
For i = LBound(vaStrip) To UBound(vaStrip)
sInput = Replace(sInput, vaStrip(i), "")
Next i
'split the text based on the delimeter
vaFields = Split(sInput, sDELIM)
lRow = lRow + 1
'Write to the worksheet
For i = 0 To UBound(vaFields)
Sheet1.Cells(lRow, i + 1).Value = vaFields(i)
Next i
Loop
Close lFNum
End Sub
Please, I would be grateful if anyone in this forum can help me out.
Thanks
Lucpian
Aussiebear: Edited thread to enclose code within VBA Tags