Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Replace Mail Merge Data Source Header for Word docs

Posted on 2010-09-20
14
Medium Priority
?
1,384 Views
Last Modified: 2013-11-27
Hello, I have a client with several hundred Word Mail Merge documents that we've migrated to a different system. All of these documents have incorrect data header sources and need to be pointed to a new source. I already know how to open the Word docs one by one and replace the header source. But this is very time consuming and is confusing users. I want to know how to programmatically replace the header source on Word docs.

My source document is an Access database .mdb file stored on the C:\ drive. I prefer to point each mail merge to this .mdb file. All data is stored on SQL Server 2008 though, so I would entertain the idea of using other data source types to connect (i.e. DSN's, etc.). Again, I can successfully open and connect to my data source .mdb or using DSN but I need a way to convert a bunch of Word files at once so that I don't have to open and save each one by hand.

Also, when I open a Word file and it can't find the old data source, it pops up with a "Data Link Properties" window. I can then point to my Access database and it connects/merges fine, but for some stupid reason I can't SAVE this to the document. When I save the window just pops up again and when I resave, close and reopen it keeps popping up. I have to manually remove the header/source in order for it to save changes. Very frustrating and time consuming.

Thanks
0
Comment
Question by:goldylamont
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
14 Comments
 

Author Comment

by:goldylamont
ID: 33719607
please note--my issue is exactly what this previous poster was going through; however this post is 4 years old and I'm wondering if there is a better solution:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21992664.html?sfQueryTermInfo=1+10+30+data+header+mail+merg+replac
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 33721860
This resemble the situation where templates are kept on a server which results in very slow document opening it the server is changed. I usually advise that the old server be reinstated while the paths are changed in the documents. I think that it will be even more essential here.

Can you clarify that you are actually talking about the header source as opposed to the data source? This is quite unusual. The whole thing is problematic, and if you are using a separate header source, it seems to be even more difficult than where the datasource contains the header information.

0
 

Author Comment

by:goldylamont
ID: 33722126
All I want to change is the DATA Source to point to a locally stored .mdb (Access) file on the user's computer. But, when I do open a document manually and change the data source; although this works, even if I SAVE the document the new Data source isn't saved. The only way I've found to save the Data source changes is to Remove the Header Source and add a new one with a new Data Source.

Honestly, I don't care if I need to create just a new Data or the whole Header Source. Also, I don't mind using a DSN or perhaps an ODBC connection in the Header/Data source. What I really really need though is a way to change this programmatically for hundreds of documents so that I don't have to do it by hand.

I'm not sure if I mentioned but we use an Access Database to connect to a SQL Server 2008 backend.
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 33726860
First of all, you need to apply the registry patch in this article:

http://support.microsoft.com/kb/825765

Without that patch, programattic opening of a mailmerge main document causes all the mailmerge settings to be dropped., so we lose the SQL, the document type (letters, labels, etc) and the destination (printer, email &c).

With the patch, we can open the document, change the database and save the document.

You do not need to re-instate the old database path(s) as I suggested above.
Sub ChangeDataSource()
    Dim maindoc As Document
    Dim strDocFile As String
    Dim strDocsFolder As String
    Dim strNewDatabase As String
    strNewDatabase = "C:\MyDatabases\MyDatabase.mdb"
    strDocsFolder = "C:\MyDocuments"
    
    strDocFile = Dir$(strDocsFolder & "\*.doc*")
    Do Until strDocFile = ""
        Set maindoc = Documents.Open(strDocsFolder & "\" & strDocFile)
        maindoc.MailMerge.OpenDataSource Name:=strNewDatabase, SQLStatement:=maindoc.MailMerge.DataSource.QueryString
        maindoc.Close wdSaveChanges
        strDocFile = Dir$()
    Loop
End Sub

Open in new window

0
 

Author Comment

by:goldylamont
ID: 33758192
oooh! i can't wait to try this GrahamSkan. thanks. I've been slammed lately but will let you know how this works out. I'll be sure to update the registry first.
0
 

Author Comment

by:goldylamont
ID: 33834002
OK, I'm trying the code and it stops at the second statement:

SQLStatement:=maindoc.MailMerge.DataSource.QueryString

It highlights .MailMerge and says data member not found. Do I need to add a reference to some library? I added reference to Word 12.0 data objects. Not sure how to make this work.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 33834441
It doesn't sound like a reference problem.

Are you sure that the Document is a mail merge main document, and has the registry patch been applied?

If so, can you give the precise message details? Thanks
0
 

Author Comment

by:goldylamont
ID: 33834521
OK, nix the last comment--I didn't know that I needed to have Word already open for this to work. So, now I get to the point that the code opens the correct document, however in Word even though the registry hack you provided suppresses the popup asking if it's OK to connect to the data source, I still get another popup for "Data Link Properties" that asks me to specify the data link. No matter what I do here any code running after Document.Open runs...
0
 

Author Comment

by:goldylamont
ID: 33834791
Yes the registry patch has been supplied and it works--but there are TWO dialogue boxes that I get when opening Merge Docs. The first one is now successfully suppressed by the registry patch. But I still get the 2nd dialogue box, which is the "Data Link Properties" dialogue box. This effectively stops the rest of the code from running.
0
 

Author Comment

by:goldylamont
ID: 33834796
I'm using Office 2007 if that makes a difference. At least the code seems to run but yeah I need to get rid of the 2nd dialogue when opening these docs. this sucks
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 33835251
The patch stopped both dialogues for me. I'll set up a test again.
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 2000 total points
ID: 33836249
In my comment: 33726860 I said

"You do not need to re-instate the old database path(s) as I suggested above."

This is incorrect. In my tests, I thought that I had removed the old database, but I hadn't. You must be able to access both the old and the new databases for the code to work
0
 

Author Comment

by:goldylamont
ID: 33836297
ooooooh, I'll try this out...I think I can let me take a look and I'll let you know
0
 

Author Closing Comment

by:goldylamont
ID: 33846875
that was it! thanks GrahamSkan, I need to allow Merge Docs to see the old database file also to avoid the 2nd popup. what a pain. thanks for all your help.
0

Featured Post

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question