Trim spaces from merge data fields

war4d · #1 02-09-2024, 01:20 PM

I am working on a Word document that uses an export from our accounting software. We have no control over how this data is provided to us. This is for listing transactions and emailing them a remittance. There is a "Table" in the doc that is really just one field per line. They send the whole line as one field and if the line is blank we get several spaces, so line suppression is not working.

I would like to clean up the fields and remove the leading and tailing spaces. It would be even better if I could split a field into several to become a better output. Sometimes their output field wraps around to the next line and the "Columns do not line up.

I tried adding =Trim(<<mergefield>>) but it did not like that either.

I feel like we can do this with EditDataSource but the only examples I can find are about adding a line, not looping through each, and modifying the value.

This is what I have been trying but Datafields.Value is Read Only.

Code:

Sub CleanData()

Dim masterDoc As Document, singleDoc As Document, field As String, cols As Integer, rows As Integer
Set masterDoc = ActiveDocument
Set singleDoc = Documents.Add
If masterDoc.MailMerge.DataSource.Type = _
    wdMergeInfoFromWord Then
   With masterDoc.MailMerge
   .EditDataSource
     For Each afield In .DataSource.DataFields
     
         field = Trim(afield.Value)
         afield.Value = field
         singleDoc.Content.InsertAfter Text:=afield.Name  & ":" & afield.Value & vbCrLf
     Next afield
    End With
End If

End Sub

The new doc is just so I can see the results. Could it make a temp datasource from the current one and then connect to that? I could have it delete the temp file on close.

Here is some sample data. It is the L## fields that are the biggest issue.

sampleData.txt

This is the issue we get with some of the remittances:

Bad: RemirBadLines.png

Normal: remitGoodLines.png

I hope this makes sense, I was interrupted a few times writing this.

Thanks for any help,
Luke

war4d · #2 02-12-2024, 11:42 AM

If anyone is curious. I ended up creating a new text file and connecting to that as the data source. The below code is what is working.

The TrimAll function was found here in Ovidiu Luca's answer.

Code:

' Go to Tools -> References... and check "Microsoft Scripting Runtime" 
' to be able to use the FileSystemObject

Sub CleanData()

Dim masterDoc As Document, field As String, line As String, lastRecordNum As Long, filePath As String, fso As FileSystemObject, fileStream As TextStream
Set masterDoc = ActiveDocument
filePath = "C:\temp\MyTestFile.txt"
If Dir("C:\temp\") = "" Then MkDir "C:\temp\"
Set fso = New FileSystemObject
Set fileStream = fso.CreateTextFile(filePath)
masterDoc.MailMerge.DataSource.ActiveRecord = wdLastRecord
lastRecordNum = masterDoc.MailMerge.DataSource.ActiveRecord
masterDoc.MailMerge.DataSource.ActiveRecord = wdFirstRecord
If masterDoc.MailMerge.DataSource.Type = _
    wdMergeInfoFromWord Then
    'Create FieldName Line
    With masterDoc.MailMerge
      For Each aname In .DataSource.DataFields
          field = LTrim(RTrim(aname.Name))
            If Len(line) < 1 Then
            line = line & field
            Else
            line = line & "|" & field
            End If
      Next aname
     End With
          fileStream.WriteLine line
' Loop Through Data and trim whitespace from both sides. Space and tab
    Do While lastRecordNum > 0
        With masterDoc.MailMerge
            line = """"
          For Each afield In .DataSource.DataFields
            field = TrimAll(afield.Value)
            If Len(line) < 2 Then
            line = line & field
            Else
            line = line & """|""" & field
            End If
          Next afield
          line = line & """"
         End With
          fileStream.WriteLine line
         
        If masterDoc.MailMerge.DataSource.ActiveRecord >= lastRecordNum Then
            lastRecordNum = 0
        Else
            masterDoc.MailMerge.DataSource.ActiveRecord = wdNextRecord
        End If
    Loop
    fileStream.Close
    'Connect to new file
ary = Split(filePath, "\")
With ActiveDocument.MailMerge
  .OpenDataSource Name:=filePath, ReadOnly:=True, AddToRecentFiles:=False, _
    LinkToSource:=False, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
    "Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
    SQLStatement:="SELECT * FROM " & ary(UBound(ary))
End With
End If

End Sub

'Function to Trim whitespace.
Private Function TrimAll(Text As String) As String

Const toRemove As String = " " & vbTab & vbCr & vbLf 'what to remove

Dim s As Long: s = 1
Dim e As Long: e = Len(Text)
Dim c As String

If e = 0 Then Exit Function 'zero len string

Do 'how many chars to skip on the left side
    c = Mid(Text, s, 1)
    If c = "" Or InStr(1, toRemove, c) = 0 Then Exit Do
    s = s + 1
Loop
Do 'how many chars to skip on the right side
    c = Mid(Text, e, 1)
    If e = 1 Or InStr(1, toRemove, c) = 0 Then Exit Do
    e = e - 1
Loop
TrimAll = Mid(Text, s, (e - s) + 1) 'return remaining text

End Function

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Trim spaces from table cells	jeffreybrown	Word VBA	2	09-04-2018 07:36 PM
Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work	jhato160	Excel	5	06-17-2016 04:06 AM
Merge fields in Word and source data from Excel	SteveVai	Mail Merge	3	03-13-2014 05:25 AM
Phantom spaces when updating linked fields	freefalladdict	Word	3	01-08-2012 02:23 PM
How do I set up the fields in mail merge word 07 from my data base	mbcrabber	Mail Merge	4	06-06-2010 01:25 PM