Word Mail Merge - CSV Datafile - change fieldnames before merge?

[jeeves]

Backround info to question:
So I get to customize our current setup at work to accomodate an automated mail merge in an upgraded version of one of our apps.
This application is a contact database that exports contact data to a csv file to be used as the merge data field for Word. We have this functioning properly for our current version of the application, but it's due for an "upgrade" this year, and the base user population will be getting a web version of the app, with what has turned out to be limited functionality. The version in production allowed us to edit what fields were exported, in what order, and how they were named.

So basically the upgraded application's exported fields do not match the existing fields in our automated merge set up. After much discussion with the vendor, it turns out that this is the way their app is coded and they will not change it for us. Well, they may develop a new function altogether, but for a price.

Any tips... I need to change the fieldnames in the CSV file before running the merge in Word. I'm hoping to address this as part of the existing code in Word, rather than within Excel in the CSV since a new CSV file will be created every time the contact information is exported. Most of the fieldnames are close, i.e. "addr_line_3" instead of "address_3".

Ideas?
Note: it is not feasible for me to change the fields in our template suite to match those in the exported data file.

[Jacob Hilderbrand]

Can you post an attachment with the file you want to change?

[jeeves]

Can you post an attachment with the file you want to change?

I can't, unfortunately. The two templates that I'm editing are vendor produced, paid for software. I'm the in-house developer that gets to put it all together to make it work.
When I'm in the office tomorrow, I'll see if I can copy in specific parts of the code...

thanks for your response...

[jeeves]

OK... I'm back after a hellish week @ work. It's Friday night, so no gorey details of sitting in meeting after meeting and getting no work done.

Anyway, I think I have a better grasp of what I'm trying to do here.

The 3rd party contact management app exports the data to a CSV in the same format/layout each time, but the field (column) names do not match the fields in my existing merge templates.

I need to either execute a find and replace, or just rename certain fields

i.e.
Field: 9
fieldname: "JobTitle"
I need to rename it to "Job Title" so it matches the existing autotext entry in my merge templates. I have to change the names of approximately 20 fields. They will always be in the same order when exported.

I've figured out that I can open the csv from Word and i'm assuming that i can make changes to it programmatically before executing the merge.

[VBA]ActiveDocument.MailMerge.OpenDataSource path + fName
[/VBA]

Then what?

Any suggestions, tips are appreciated. I'm on my own for this one, and brand new to VBA.

[mdmackillop]

Hi Jeeves,
You need to add your old/new field names into the arrays replacing my sample code, then run the following
MD

[VBA]
Option Explicit
Option Compare Text
Sub ChangeHeader()

Dim ToFind
Dim ReplaceWith
Dim TF
Dim i As Integer

ActiveDocument.MailMerge.EditDataSource

ToFind = Array("Add1", "Add2", "Add5")
ReplaceWith = Array("Add_1", "Add_2", "Add_5")
i = -1
For Each TF In ToFind
i = i + 1
Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ToFind(i)
.Replacement.Text = ReplaceWith(i)
.Forward = True
.MatchWholeWord = True
End With
Selection.Find.Execute Replace:=wdReplaceOne
Next

End Sub


[/VBA]

[jeeves]

Thanks very much! I'm working with your sample code now.
I've gotten it to work once, and I'm trying to convince it to work again consistently!

[jeeves]

thanks very much! the code you provided, with a bit of tweaking, did the trick.

The only thing is, it opens the CSV on the desktop - is there a command I can provide to get have the CSV opened and edited so the user will not see it? It's not just the window placement, and the prompt to save and close, I can't add any extra steps to the existing process.

[mdmackillop]

Hi Jeeves,
Modified to suit
MD

[VBA] Option Explicit
Option Compare Text
Sub ChangeHeader()

Dim ToFind, ReplaceWith
Dim MyDS As String
Dim TF
Dim i As Integer

Application.ScreenUpdating = False
MyDS = ActiveDocument.MailMerge.DataSource.Name
ActiveDocument.MailMerge.EditDataSource
ToFind = Array("Add1", "Add2", "Add5")
ReplaceWith = Array("Add_1", "Add_2", "Add_5")
i = -1
For Each TF In ToFind
i = i + 1
Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ToFind(i)
.Replacement.Text = ReplaceWith(i)
.Forward = True
.MatchWholeWord = True
End With
Selection.Find.Execute Replace:=wdReplaceOne
Next
Documents(MyDS).Close True
Application.ScreenUpdating = True
End Sub
[/VBA]