Help reading in a text file

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi all,

I'm trying to write some VBA code import a text file, but
I'm running into problems. Here's what I have so far
(Thanks John Walkenback):

Private Sub Workbook_Open()

Dim vdata2 As Integer
Dim FullText As String
Set ImpRange = ActiveCell
Open "E:\Tagtest.txt" For Input As #1
r = 0
c = 0
txt = ""
Application.ScreenUpdating = False
Do While Not EOF(1)
Line Input #1, vdata
vdata2 = Len(vdata) + 1
For i = 1 To vdata2
char = Mid(vdata, i, 1)
If char = "|" Or i = vdata2 Then
ActiveCell.Offset(r, c) = txt
'FullText = FullText & txt
c = c + 1
txt = ""
Else
If char <> Chr(34) Then
txt = txt & Mid(vdata, i, 1)
End If
End If
Next i
c = 0
r = r + 1
Loop
Close #1
Application.ScreenUpdating = True



End Sub

This works great for single lines in the text file,
delimited by "|", but my problem is this: I have
multiple lines that I need in one cell. Here's an
example of the output in the file:

~P-DisabilityInsuranceSummaryHead1|
~P-DisabilityInsuranceSurpDefSummaryHead1|This cash flow
graph shows the difference between your desired expenses
in the event that Bob becomes disabled and your ability
to cover them without owning any additional disability
insurance. We have analyzed your plan from 2004 to 2032
to estimate the deficit amounts you are likely to
encounter in the event that Bob becomes disabled. The
average deficit you will face is $4,901, with the largest
deficit ($5,725) occurring in 2008.
~P-DisabilityInsuranceObjectivesHead1|In the event that
Bob becomes disabled, you have requested that 100% of
your current lifestyle expenses be covered. You would
like to maintain the level of surplus cash that would
normally be available if Bob had not become disabled.
Also, existing and proposed investment plans will be
maintained unless these goals have been reduced or
eliminated. This means that any assets that you have
linked with other goals (e.g. retirement, education,
major purchase) will continue to be used to help fund
those goals.
~P-DisabilityInsuranceSurpDefSummaryPresHead1|The average
deficit you will face is $4,901, with the largest deficit
($5,725) occurring in 2008.
~P-DisabilityInsuranceAnalysisQuickHead1|SHORTFALL
~DisabilityInsuranceAverageDeficitHead1| $4,901
~DisabilityInsuranceFirstYearDeficitHead2| $0

Summary

It should also be noted that in the event that Mary
becomes disabled, you would like to maintain 100% of the
level of surplus cash that would normally be available if
Mary had not become disabled.

In this case, the
section "DisabilityInsuranceFirstDeficitHead2" should be
in one cell and everything following it should be in the
second cell. With the logic in the code above, "Summary"
and "It should also be noted..." appear in their own
cells.

I'm hoping there's somebody out there that can give me a
hand, I'm having a hard time wrapping my head around this
one.


Thanks in advance,

Mike
 
-----Original Message-----
Hi all,

I'm trying to write some VBA code import a text file, but
I'm running into problems. Here's what I have so far
(Thanks John Walkenback):

Private Sub Workbook_Open()

Dim vdata2 As Integer
Dim FullText As String
Set ImpRange = ActiveCell
Open "E:\Tagtest.txt" For Input As #1
r = 0
c = 0
txt = ""
Application.ScreenUpdating = False
Do While Not EOF(1)
Line Input #1, vdata
vdata2 = Len(vdata) + 1
For i = 1 To vdata2
char = Mid(vdata, i, 1)
If char = "|" Or i = vdata2 Then
ActiveCell.Offset(r, c) = txt
'FullText = FullText & txt
c = c + 1
txt = ""
Else
If char <> Chr(34) Then
txt = txt & Mid(vdata, i, 1)
End If
End If
Next i
c = 0
r = r + 1
Loop
Close #1
Application.ScreenUpdating = True



End Sub

This works great for single lines in the text file,
delimited by "|", but my problem is this: I have
multiple lines that I need in one cell. Here's an
example of the output in the file:

~P-DisabilityInsuranceSummaryHead1|
~P-DisabilityInsuranceSurpDefSummaryHead1|This cash flow
graph shows the difference between your desired expenses
in the event that Bob becomes disabled and your ability
to cover them without owning any additional disability
insurance. We have analyzed your plan from 2004 to 2032
to estimate the deficit amounts you are likely to
encounter in the event that Bob becomes disabled. The
average deficit you will face is $4,901, with the largest
deficit ($5,725) occurring in 2008.
~P-DisabilityInsuranceObjectivesHead1|In the event that
Bob becomes disabled, you have requested that 100% of
your current lifestyle expenses be covered. You would
like to maintain the level of surplus cash that would
normally be available if Bob had not become disabled.
Also, existing and proposed investment plans will be
maintained unless these goals have been reduced or
eliminated. This means that any assets that you have
linked with other goals (e.g. retirement, education,
major purchase) will continue to be used to help fund
those goals.
~P-DisabilityInsuranceSurpDefSummaryPresHead1|The average
deficit you will face is $4,901, with the largest deficit
($5,725) occurring in 2008.
~P-DisabilityInsuranceAnalysisQuickHead1|SHORTFALL
~DisabilityInsuranceAverageDeficitHead1| $4,901
~DisabilityInsuranceFirstYearDeficitHead2| $0

Summary

It should also be noted that in the event that Mary
becomes disabled, you would like to maintain 100% of the
level of surplus cash that would normally be available if
Mary had not become disabled.

In this case, the
section "DisabilityInsuranceFirstDeficitHead2" should be
in one cell and everything following it should be in the
second cell. With the logic in the code above, "Summary"
and "It should also be noted..." appear in their own
cells.

I'm hoping there's somebody out there that can give me a
hand, I'm having a hard time wrapping my head around this
one.


Thanks in advance,

Mike
.
Click to expand...

Tough because you need to distinguish between two types
of "new line" codes when it is the same code on the
computer. The computer is dumb and can't tell the
difference unless it has some logic to go by. You need to
think, "How do I make the distinction in my mind?" and
program that logic into the computer. So how do you make
the distinction? Are all your text files formatted
similarly? Is it the "blank" lines that are the only
problem area? If so, add some code to check the line and
see if it is blank (in other words, look for two
consecutive new line codes and throw them out). It is
hard to specify the necessary code without knowing the
details, but that is how you need to approach this kind of
problem.
 
Back
Top