Here is what I ended up with after I found the all lines I wanted started with space tab. It really helped to that there were tabs between all of the columns as well. If it wasn't for this I would still be trying to figure out what to do.
I realize I could condense portions of this code but it's easier for me to read this way.
Thank you very much for all your suggestions.
Code:
Do While Not MyFile.AtEndOfStream
InBuffer = MyFile.ReadLine '-- Bring in the next Input Line
Dim firstTab As Integer
Dim secondTab As Integer
Dim newBuffer As String
Dim tblValue(20) As String ' Array to save values
Dim i As Integer
'Two spaces for lot number
If Mid(InBuffer, 1, 3) = " " & vbTab Then 'Check for lines that start with 2 spaces and a tab
newBuffer = Trim(InBuffer) 'trim the spaces off both sides, string has to be clean
i = 1 ' counter
Do While Len(newBuffer) > 0 'The code will remove portions of the string so when at 0 end loop
newBuffer = LTrim(newBuffer)
firstTab = InStr(1, newBuffer, vbTab) 'Start of string to process
secondTab = InStr(2, newBuffer, vbTab) 'end of string to Process
If secondTab = 0 Then 'different code for the end of the string when there is no tab
tblValue(i) = Mid(newBuffer, firstTab)
newBuffer = ""
Else
'+1 skips first tab, -3 because there are 3 spaces between items for this particular file
tblValue(i) = Mid(newBuffer, firstTab + 1, secondTab - 3)
newBuffer = Mid(newBuffer, secondTab)
i = i + 1
End If
Loop
With MyRs
.AddNew
!Lot_Number = tblValue(1)
!Vaccine_Name = tblValue(2)
!Manufacturer = tblValue(3)
!Expires = tblValue(4)
.Update
End With
Else
End If
Loop