If doesn't exist, then add this

[castlemve]

I'm working on cleaning up a text file of records to import into Excel.
Using macros, I have been able to get it to look like the following:

fieldname1;value^p
fieldname2;value^p
fieldname3;value^p
^p
fieldname1;value^p
fieldname2;value^p
fieldname3;value^p
^p
.....

Actually, this is the way it's supposed to look. Unfortunately, one of the
fields shows up in a record only if there was a value for it. If there is no
value, the field itself is not there. For instance, fieldname2 exists only
in some records. There isn't a ^p or anything to show the field exists. I
need the field to be there in order to import the document into Excel.

I'm hoping someone can provide some code that would only add the field if it
doesn't exist in a record. There are about 20 records in the document. I
have about 20 of these documents to do and will likely need to do this again
in the future.

Thanks.

 

[Helmut Weber]

Hi,
i am assunimg, that your file looks like this,
entirely, without any exception:
fieldname1;value^p
fieldname2;value^p
fieldname3;value^p
^p
fieldname1;value^p ' field2 may be missing
fieldname3;value^p
^p
fieldname1;value^p ' field2 may be missing
fieldname3;value^p
^p
Then, your file consists of as many records as there
are "^p^p" in your document.
Count the records. (^p^p)
Loop through the records.
Count the fields in the record
Insert one, if number of fields is too small.
---
Only in principle (!), therefore using selection
insted of range for easier debugging.
And there are many other ways.
---
Sub Makro1()
Dim iRec As Integer ' number of records
Dim i As Integer
Dim sRec As String
Dim aFld() As String
Resetsearch
' Count the records
With Selection
.Collapse
.HomeKey unit:=wdStory
With .Find
.Text = "^p^p"
While .Execute
iRec = iRec + 1
Wend
End With
End With
' msgbox iRec
' loop through the records
With Selection
.Collapse
.HomeKey unit:=wdStory
For i = 1 To iRec
.ExtendMode = True
With .Find
.Text = "^p^p"
.Execute
End With
aFld = Split(Selection.Text, Chr(13))
' count the fields in the record
' here + 1 because of the included record delimiter
If UBound(aFld) < 4 Then
MsgBox "Missing Record"
.Collapse
.ExtendMode = False
.MoveDown
.TypeText "fieldname2;valueX" & vbCrLf
With .Find
.Text = "^p^p"
.Execute
End With
End If
.Collapse direction:=wdCollapseEnd
Next
End With
Resetsearch
End Sub
' ---
Sub Resetsearch()
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute
End With
End Sub