Reply
 
Thread Tools Display Modes
  #1   Report Post  
Annesusanne Annesusanne is offline
Junior Member
 
Posts: 1
Default Update data source in mail merge - folderwise

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!!!
Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge Data source Subhasis Mailmerge 5 February 1st 10 12:05 PM
How can I add data fields to a data source in Word Mail Merge? cd Mailmerge 2 November 7th 05 07:41 PM
DotNet Data Table as Mail Merge Data Source goraya Mailmerge 1 July 7th 05 09:51 AM
How do I move data Colums within the data source of a mail merge hf Microsoft Word Help 1 July 5th 05 09:57 PM
data in mail merge letter being cut off - data source field size? tgone Mailmerge 1 May 20th 05 02:10 PM


All times are GMT +1. The time now is 10:37 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"