Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
MailMerge change of datasource
Hi
We have a number of Word letters that are the templates for Mail mereges using MS Access as the datasource. However, we have recently had our database tidied up, with object name being table consistent (tables all now start tbl etc). Of course this has upset our mail merges with the Word documents now un able to find the data sources. Is there an easier way to change the datasource, other than having to open up each document in turn and relink, does anyone know? Many thanks Alex |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
MailMerge change of datasource
If the name change was strictly consistent - that is just the prepending of
tbl to each table, and no other changes, it should be possible to use vba code do prepend tbl to the name of the datasource for each of the merged letters by processing them as a batch. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Alex Hammerstein" wrote in message ... Hi We have a number of Word letters that are the templates for Mail mereges using MS Access as the datasource. However, we have recently had our database tidied up, with object name being table consistent (tables all now start tbl etc). Of course this has upset our mail merges with the Word documents now un able to find the data sources. Is there an easier way to change the datasource, other than having to open up each document in turn and relink, does anyone know? Many thanks Alex |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
MailMerge change of datasource
Hi Doug
Thanks for your post How do I get to the vba code of the file? Sorry if that seems a dumb question Alex On 21/01/2009 19:39, in article , "Doug Robbins - Word MVP" wrote: If the name change was strictly consistent - that is just the prepending of tbl to each table, and no other changes, it should be possible to use vba code do prepend tbl to the name of the datasource for each of the merged letters by processing them as a batch. |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
MailMerge change of datasource
If you place a copy of all of the mailmerge main documents in a folder by
themselves (in the following code, that is assumed to be a folder named "Test" on the C: drive, and then you run the following macro, it should modify the file so that the data source would be a table with the same name as the original with tbl prepended to it: Sub BatchUpdateDataSource() Option Explicit Dim myFile As String Dim PathToUse As String Dim myDoc As Document Dim sourcename As String Dim sourcetable As String 'Set the path to the folder containing the documents PathToUse = "C:\Test\" 'Close all open documents before beginning Documents.Close SaveChanges:=wdPromptToSaveChanges 'Set the directory and type of file to batch process myFile = Dir$(PathToUse & "*.doc") While myFile "" Set myDoc = Documents.Open(PathToUse & myFile) With myDoc.MailMerge 'Get the name of the existing data source (database path and name) sourcename = .DataSource.Name 'Get the name of the database table that contains the records sourcetable = .DataSource.TableName 'Modify the name by prepending tbl to it. sourcetable = Left(sourcetable, 15) & "tbl" & Mid(sourcetable, 16) 'Attach the modified datasource to the document .OpenDataSource Name:=sourcename _ , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & sourcename & ";Mode=Read; _ Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path=""""; _ Jet OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Part" _ , SQLStatement:=sourcetable, SQLStatement1:="", SubType:= _ wdMergeSubTypeAccess End With 'Close the modified document after saving changes myDoc.Close SaveChanges:=wdSaveChanges 'Next file in folder myFile = Dir$() Wend End Sub Note that the following section of the above .OpenDataSource Name:=sourcename _ , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & sourcename & ";Mode=Read; _ Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path=""""; _ Jet OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Part" _ , SQLStatement:=sourcetable, SQLStatement1:="", SubType:= _ wdMergeSubTypeAccess is based on the use of Office 2007. If you are using an earlier version of Office, you should use the macro recorder to record the action of attaching a table in a data base to a mail merge main document and then substitute the code so recorder for the above, replacing the path and database name where is appears in that code with "sourcename" as it appears above replacing the "SELECT * FROM `tablename`" with sourcetable as it appears above. If you do not know what to do with the above, see the article "What do I do with macros sent to me by other newsgroup readers to help me out?" at: http://www.word.mvps.org/FAQs/Macros...eateAMacro.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Alex Hammerstein" wrote in message ... Hi Doug Thanks for your post How do I get to the vba code of the file? Sorry if that seems a dumb question Alex On 21/01/2009 19:39, in article , "Doug Robbins - Word MVP" wrote: If the name change was strictly consistent - that is just the prepending of tbl to each table, and no other changes, it should be possible to use vba code do prepend tbl to the name of the datasource for each of the merged letters by processing them as a batch. |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
MailMerge change of datasource
1. Which version(s) of Word/Access?
2. Are all your mail merge main documents in .doc format? Or .docx format? Or a mix? 3. Do the original data sources still exist? (i.e. does the same ..mdb/.accdb with the original table names still exist). If they do exist, are they in the same database as the new ones? 4. Are all your data sources Access tables? Are some Access queries? Do you use other non-Access data sources? 5. Are all your connections to Access the same type (e.g. all OLE DB, all ODBC, all DDE)? Or is there a mix? 6. Have you applied any filter/sort criteria to the data sources? (Or do you have mail merge main documents that have been user-created where you could not possibly know without looking first? 7. All the above potentially have an impact on what would be necessary to automate changes to your mail merge main documents. For example, if the original data source no longer exists, you will not be able to open the exiting mail merge main document without having to respond to one or more dialog boxes and potentially losing the original data source information. 8. One thing that might help in some cases (I'm not sure) if you are using the same .mdb/.accdb file as before would be to create a Query that has the same name as the original table name (if you have already removed those old table names). Peter Jamieson http://tips.pjmsn.me.uk Alex Hammerstein wrote: Hi We have a number of Word letters that are the templates for Mail mereges using MS Access as the datasource. However, we have recently had our database tidied up, with object name being table consistent (tables all now start tbl etc). Of course this has upset our mail merges with the Word documents now un able to find the data sources. Is there an easier way to change the datasource, other than having to open up each document in turn and relink, does anyone know? Many thanks Alex |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MailMerge Datasource not appearing in VBA when using DDE | Mailmerge | |||
XML as a MailMerge datasource | Mailmerge | |||
Using OLE to change mailmerge datasource | Mailmerge | |||
Mailmerge datasource | Mailmerge | |||
cant open mailmerge datasource | Mailmerge |