[Annesusanne]

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\Testda tenquelle2.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 see what I can do now.

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!!!