Still need help with this vba code

[lucpian]

Hi All,

Thanks xld, but I am still having a big problem with the code I wrote to import text files into Excel. It works fine as it validates that the following column headings as it appears in the following order: First_Name, Last_Name, Purch_Ord_Num, Amount, Purc_Date, Received_By, Verified_By. The problem now is that if the headings are right, it does not import the headings, but just the data. Also, the blank cells in the text file with "" are imported as that instead of having it blank

Here is the code,
Sub ImportTextFile()

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
'sFile = "C:CaratDelim.txt"
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)
If UBound(vaFields) - i + 1 = 8 Then

If vaFields(i) = "Contract" And _
vaFields(i + 1) = "Effective Date" And _
vaFields(i + 2) = "Install Date" And _
vaFields(i + 3) = "On Maint Date" And _
vaFields(i + 4) = "Serial Number" And _
vaFields(i + 5) = "Cost" And _
vaFields(i + 6) = "Catalog ID" And _
vaFields(i + 7) = "Car Owner" Then

'Loop through the file until the end
Do While Not EOF(lFNum)

vaFields = GetData(lFNum, vaStrip, 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
Else

MsgBox "The fields are not arrange in order. Invalid headers"
End If
'MsgBox ((i))
Else
'MsgBox (vaFields(i + 17))
MsgBox "The fields are not arrange in order. Invalid headers"
End If

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

[figment]

replace the while loop with this and give it a try

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

[lucpian]

Thanks that last piece of code solved my problem. Thanks a lot.