lucpian
04-04-2008, 09:26 AM
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
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