Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help reading in a text file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help reading in a text file
-----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 . 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading in poorly formatted text file | Excel Discussion (Misc queries) | |||
Reading from a text file | Excel Discussion (Misc queries) | |||
Reading a text file ? | Excel Worksheet Functions | |||
Reading a text file | Excel Discussion (Misc queries) | |||
how do you skip lines when reading a text file | Excel Programming |