Update Mail Merge Data Source folderwise

Annesusanne · August 25th, 2010, 11:45 AM

Hi all,

due to a change of our directory structure I have to update the link to the Access data source for about 400 mail merges.
In our database we use parameter queries. All main documents work with such a parameter query (all to the same query). Therefore, we must connect to our database using DDE and not ODBC. How can I define this DDE connection in VBA? I have tried it via wdMergeSubType:

Code:

Sub ChangeDataSource() 

    ActiveDocument.MailMerge.OpenDataSource Name:= _ 
    "C:\Finale Datenstruktur\neue Dateien Laptop\Diagnostik\Serienbriefaktualisierung\Testdatenquelle2.mdb", _ 
    ConfirmConversions:=True, ReadOnly:=True, LinkToSource:=True, AddToRecentFiles:=False, _ 
    Connection:="QUERY qry_SEQ_Befund", SQLStatement:= "SELECT * FROM [qry_SEQ_Befund]", _ 
    SubType:=wdMergeSubTypeOther 
End Sub

Here the correct query seems to be selected (at least I donât get an error message) and the modification of the linked data source seems to work well as the correct (changed) data source is returned upon the following macro:

Code:

Sub ReturnDataSource() 
' ' 
If ActiveDocument.MailMerge.DataSource.Name <> "" Then _ 
MsgBox ActiveDocument.MailMerge.DataSource.Name 

End Sub

However, when executing the macro I get a dialog which asks for the choice of a table, which is not what I intended. If I select the table on which my query is based, everything works fine so far. I only just don't completely understand why this choice is necessary at all. The problem which arises from it, is that the system doesn't ask for my parameter any more if I close the document and reopen it. I get the message that the table which was selected before is being opened, but I need the query to be linked to my main document otherwise there is no way to enter my parameter in order to select a certain data set.

Does anybody have a clue? I don't get any further here.

If I might ask a second question: How could I do - via VBA - the same changes for a bunch of documents within one folder without opening every single file? A query that asks the user which folder shall be updated would be cool but I can also write the paths into the macro by hand.

For a folderwise execution of the macro I have the following in mind:

Code:

 
Sub PerformChangesByFolder() 
    Dim Path, oPath 
    Folder = BrowseForFolder("Select Folder") 
    If Len(Folder) = 0 Then 
        MsgBox "No folder selected." 
        Exit Sub 
    Else 
        'ChangeFileOpenDirectory Folder 
        oPath = Folder 
        'MsgBox oPath 
    End If 

    Pfad = oPath 
    With Application.FileSearch 
        .LookIn = Pfad 
        .SearchSubFolders = True 
        .FileType = msoFileTypeWordDocuments 
        .Execute 
... 
    End With 

End Sub

Here I still would need some help on how I open the documents contained in the folder, apply my macro on them, save and close everything afterwards. For you this might be a piece of cake, for me it isn't :-(

That's it so far. I hope that I'm not too far away from a working solution but perhaps I'm still miles away... Hope, that someone out there can help!!!

Many thanks in advance!!!