Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Datasource
Hi
With Mailmerge documents in Word 2007 is it possible to establish the datasource for the mail merge, without having to open up the Word document? Many thanks Alex |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Datasource
You can create a data source (e.g. with Excel or Access - or even from the
mailings tab (Select Recipients) of Word 2007 itself) without opening the document with which it will be used, but in order to attach it to the document the document needs to be opened. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Alex Hammerstein wrote: Hi With Mailmerge documents in Word 2007 is it possible to establish the datasource for the mail merge, without having to open up the Word document? Many thanks Alex |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Datasource
Hi, Thanks for your response.
What I am trying to establish is that for a document that already has a data source, is it possible to establish the name of the data source without having to open the document. Its just that I have a few hundred to check and I was hoping that I wouldn't have to go through each one opening them up. I looked under properties but couldn't see anything Thanks Alex On 23/01/2009 10:59, in article , "Graham Mayor" wrote: You can create a data source (e.g. with Excel or Access - or even from the mailings tab (Select Recipients) of Word 2007 itself) without opening the document with which it will be used, but in order to attach it to the document the document needs to be opened. |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Datasource
If you put the documents in a separate folder and run the following macro,
then provided the documents are not password protected, the macro should list the documents and their associated data sources in a new document. If no data source is attached, the macro reports that also. Any automacros in the documents are temporarily disabled and the documents are not altered.. Sub ListDataSources() Dim strFile As String Dim strPath As String Dim oDoc As Document Dim oTarget As Document Dim iFld As Integer Dim fDialog As FileDialog Set fDialog = Application.FileDialog(msoFileDialogFolderPicker) With fDialog .Title = "Select Folder containing the documents and click OK" .AllowMultiSelect = False .InitialView = msoFileDialogViewList If .Show -1 Then MsgBox "Cancelled By User" Exit Sub End If strPath = fDialog.SelectedItems.Item(1) If Right(strPath, 1) "\" Then strPath = strPath + "\" End With WordBasic.DisableAutoMacros 1 If Documents.Count 0 Then Documents.Close SaveChanges:=wdPromptToSaveChanges End If Set oTarget = Documents.Add strFile = Dir$(strPath & "*.do?") While strFile "" Set oDoc = Documents.Open(strPath & strFile) oTarget.Activate Selection.TypeText oDoc.FullName & Chr(11) If oDoc.MailMerge.MainDocumentType wdNotAMergeDocument Then Selection.TypeText "Datasource: - " & oDoc.MailMerge.DataSource.name Else Selection.TypeText "Not a merge document" End If Selection.TypeParagraph oDoc.Close SaveChanges:=wdDoNotSaveChanges strFile = Dir$() Wend WordBasic.DisableAutoMacros 0 End Sub http://www.gmayor.com/installing_macro.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Alex Hammerstein wrote: Hi, Thanks for your response. What I am trying to establish is that for a document that already has a data source, is it possible to establish the name of the data source without having to open the document. Its just that I have a few hundred to check and I was hoping that I wouldn't have to go through each one opening them up. I looked under properties but couldn't see anything Thanks Alex On 23/01/2009 10:59, in article , "Graham Mayor" wrote: You can create a data source (e.g. with Excel or Access - or even from the mailings tab (Select Recipients) of Word 2007 itself) without opening the document with which it will be used, but in order to attach it to the document the document needs to be opened. |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Datasource
In order to run Graham's macro successfully, you will probably need to
apply the registry change described in http://support.microsoft.com/kb/825765/en-us For each document, if the data source no longer exists, you will not be able to get any information about it by opening the .doc, because Word will force you to discard the data source or provide another one. After that, VBA will not let you see the details of the old data source. For documents with other formats such as .docx/.docm (Word 2007), and in some cases, .rtf, .xml, .html, you may be able to find the data source details by opening the files outside Word - e.g. you can open .rtf, .xml and .htm in Notepad. .docx and .docm files can be renamed to .zip and the internal .xml files examined. If all these documents connected to a .mdb which has now gone, or which does not have the same query/table names in it, your task will be made a lot easier if you can restore the old .mdb temporarily. If you do that, I strongly recommend that you extract the following pieces of information for each data source: oDoc.MailMerge.DataSource.Name oDoc.MailMerge.DataSource.Connectstring oDoc.MailMerge.DataSource.Querystring It wouldn't do any harm to extract oDoc.MailMerge.DataSource.Tablename either. The chances are that all the mail merge main documents that connect to your Access database have the same or similar COnnectstring, but if for some reason, some connections needed to use the DDE method (e.g. because they reference parameter queries), each connection string will be different. The querystring will typically be something like SELECT * FROM `tablename` but if the user (or programmer) has set up any sort/filter criteria, the Querystring embodies those criteria and will help you set up the mail merge main document with the new table name and the same criteria. Peter Jamieson http://tips.pjmsn.me.uk Alex Hammerstein wrote: Hello Graham Thank you so much for the macro and for your help - it is much appreciated. I am having a slight problem. I have created a folder and placed all the Word files into this folder along with a word file called test, and which contains the macro. I open this file and run the macro. I then get a message that Word is trying to run the SQL command to select the data. If I select yes, I am getting and error: Microsoft Visual Basic Run-time error 5398 The operation is cancelled (W:\checklistinvoice * IT e-mail.doc) It started debugging and highlighted the following line in yellow: Set oDoc = Documents.Open(strPath & strFile) I subsequently stopped the debugging. If I select No, The word file being checked is listed on the test document and shown as: F:\mail merge letters\College Results\failed 1st attempt - 3pl.doc Not a merge document Have I done something wrong? Alex On 23/01/2009 14:15, in article , "Graham Mayor" wrote: If you put the documents in a separate folder and run the following macro, then provided the documents are not password protected, the macro should list the documents and their associated data sources in a new document. If no data source is attached, the macro reports that also. Any automacros in the documents are temporarily disabled and the documents are not altered.. Sub ListDataSources() Dim strFile As String Dim strPath As String Dim oDoc As Document Dim oTarget As Document Dim iFld As Integer Dim fDialog As FileDialog Set fDialog = Application.FileDialog(msoFileDialogFolderPicker) With fDialog .Title = "Select Folder containing the documents and click OK" .AllowMultiSelect = False .InitialView = msoFileDialogViewList If .Show -1 Then MsgBox "Cancelled By User" Exit Sub End If strPath = fDialog.SelectedItems.Item(1) If Right(strPath, 1) "\" Then strPath = strPath + "\" End With WordBasic.DisableAutoMacros 1 If Documents.Count 0 Then Documents.Close SaveChanges:=wdPromptToSaveChanges End If Set oTarget = Documents.Add strFile = Dir$(strPath & "*.do?") While strFile "" Set oDoc = Documents.Open(strPath & strFile) oTarget.Activate Selection.TypeText oDoc.FullName & Chr(11) If oDoc.MailMerge.MainDocumentType wdNotAMergeDocument Then Selection.TypeText "Datasource: - " & oDoc.MailMerge.DataSource.name Else Selection.TypeText "Not a merge document" End If Selection.TypeParagraph oDoc.Close SaveChanges:=wdDoNotSaveChanges strFile = Dir$() Wend WordBasic.DisableAutoMacros 0 End Sub http://www.gmayor.com/installing_macro.htm |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Datasource
The macro should go in the normal template or an add-in and not in a
documnent in the folder you are working on. As for the SQL message - You receive the "Opening this will run the following SQL command" message when you open a Word mail merge main document that is linked to a data source - http://support.microsoft.com/?kbid=825765. See also the Word macro to toggle the registry setting referred to in that link at http://www.gmayor.com/word_vba_examples.htm#SQL. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Alex Hammerstein wrote: Hello Graham Thank you so much for the macro and for your help - it is much appreciated. I am having a slight problem. I have created a folder and placed all the Word files into this folder along with a word file called test, and which contains the macro. I open this file and run the macro. I then get a message that Word is trying to run the SQL command to select the data. If I select yes, I am getting and error: Microsoft Visual Basic Run-time error 5398 The operation is cancelled (W:\checklistinvoice * IT e-mail.doc) It started debugging and highlighted the following line in yellow: Set oDoc = Documents.Open(strPath & strFile) I subsequently stopped the debugging. If I select No, The word file being checked is listed on the test document and shown as: F:\mail merge letters\College Results\failed 1st attempt - 3pl.doc Not a merge document Have I done something wrong? Alex On 23/01/2009 14:15, in article , "Graham Mayor" wrote: If you put the documents in a separate folder and run the following macro, then provided the documents are not password protected, the macro should list the documents and their associated data sources in a new document. If no data source is attached, the macro reports that also. Any automacros in the documents are temporarily disabled and the documents are not altered.. Sub ListDataSources() Dim strFile As String Dim strPath As String Dim oDoc As Document Dim oTarget As Document Dim iFld As Integer Dim fDialog As FileDialog Set fDialog = Application.FileDialog(msoFileDialogFolderPicker) With fDialog .Title = "Select Folder containing the documents and click OK" .AllowMultiSelect = False .InitialView = msoFileDialogViewList If .Show -1 Then MsgBox "Cancelled By User" Exit Sub End If strPath = fDialog.SelectedItems.Item(1) If Right(strPath, 1) "\" Then strPath = strPath + "\" End With WordBasic.DisableAutoMacros 1 If Documents.Count 0 Then Documents.Close SaveChanges:=wdPromptToSaveChanges End If Set oTarget = Documents.Add strFile = Dir$(strPath & "*.do?") While strFile "" Set oDoc = Documents.Open(strPath & strFile) oTarget.Activate Selection.TypeText oDoc.FullName & Chr(11) If oDoc.MailMerge.MainDocumentType wdNotAMergeDocument Then Selection.TypeText "Datasource: - " & oDoc.MailMerge.DataSource.name Else Selection.TypeText "Not a merge document" End If Selection.TypeParagraph oDoc.Close SaveChanges:=wdDoNotSaveChanges strFile = Dir$() Wend WordBasic.DisableAutoMacros 0 End Sub http://www.gmayor.com/installing_macro.htm |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Datasource
Thanks Peter
The client had asked us to tidy up their database, so we went through renaming objects properly and generally tidying up the object naming and code. What they forgot to tell us was that they had about 100 or so mail merge Word documents, that of course now do not link properly, and we need to establish what links they had so we can relink them. I will try what you suggest Alex On 27/01/2009 15:05, in article , "Peter Jamieson" wrote: In order to run Graham's macro successfully, you will probably need to apply the registry change described in http://support.microsoft.com/kb/825765/en-us For each document, if the data source no longer exists, you will not be able to get any information about it by opening the .doc, because Word will force you to discard the data source or provide another one. After that, VBA will not let you see the details of the old data source. For documents with other formats such as .docx/.docm (Word 2007), and in some cases, .rtf, .xml, .html, you may be able to find the data source details by opening the files outside Word - e.g. you can open .rtf, .xml and .htm in Notepad. .docx and .docm files can be renamed to .zip and the internal .xml files examined. If all these documents connected to a .mdb which has now gone, or which does not have the same query/table names in it, your task will be made a lot easier if you can restore the old .mdb temporarily. If you do that, I strongly recommend that you extract the following pieces of information for each data source: oDoc.MailMerge.DataSource.Name oDoc.MailMerge.DataSource.Connectstring oDoc.MailMerge.DataSource.Querystring It wouldn't do any harm to extract oDoc.MailMerge.DataSource.Tablename either. The chances are that all the mail merge main documents that connect to your Access database have the same or similar COnnectstring, but if for some reason, some connections needed to use the DDE method (e.g. because they reference parameter queries), each connection string will be different. The querystring will typically be something like SELECT * FROM `tablename` but if the user (or programmer) has set up any sort/filter criteria, the Querystring embodies those criteria and will help you set up the mail merge main document with the new table name and the same criteria. Peter Jamieson http://tips.pjmsn.me.uk Alex Hammerstein wrote: Hello Graham Thank you so much for the macro and for your help - it is much appreciated. I am having a slight problem. I have created a folder and placed all the Word files into this folder along with a word file called test, and which contains the macro. I open this file and run the macro. I then get a message that Word is trying to run the SQL command to select the data. If I select yes, I am getting and error: Microsoft Visual Basic Run-time error 5398 The operation is cancelled (W:\checklistinvoice * IT e-mail.doc) It started debugging and highlighted the following line in yellow: Set oDoc = Documents.Open(strPath & strFile) I subsequently stopped the debugging. If I select No, The word file being checked is listed on the test document and shown as: F:\mail merge letters\College Results\failed 1st attempt - 3pl.doc Not a merge document Have I done something wrong? Alex On 23/01/2009 14:15, in article , "Graham Mayor" wrote: If you put the documents in a separate folder and run the following macro, then provided the documents are not password protected, the macro should list the documents and their associated data sources in a new document. If no data source is attached, the macro reports that also. Any automacros in the documents are temporarily disabled and the documents are not altered.. Sub ListDataSources() Dim strFile As String Dim strPath As String Dim oDoc As Document Dim oTarget As Document Dim iFld As Integer Dim fDialog As FileDialog Set fDialog = Application.FileDialog(msoFileDialogFolderPicker) With fDialog .Title = "Select Folder containing the documents and click OK" .AllowMultiSelect = False .InitialView = msoFileDialogViewList If .Show -1 Then MsgBox "Cancelled By User" Exit Sub End If strPath = fDialog.SelectedItems.Item(1) If Right(strPath, 1) "\" Then strPath = strPath + "\" End With WordBasic.DisableAutoMacros 1 If Documents.Count 0 Then Documents.Close SaveChanges:=wdPromptToSaveChanges End If Set oTarget = Documents.Add strFile = Dir$(strPath & "*.do?") While strFile "" Set oDoc = Documents.Open(strPath & strFile) oTarget.Activate Selection.TypeText oDoc.FullName & Chr(11) If oDoc.MailMerge.MainDocumentType wdNotAMergeDocument Then Selection.TypeText "Datasource: - " & oDoc.MailMerge.DataSource.name Else Selection.TypeText "Not a merge document" End If Selection.TypeParagraph oDoc.Close SaveChanges:=wdDoNotSaveChanges strFile = Dir$() Wend WordBasic.DisableAutoMacros 0 End Sub http://www.gmayor.com/installing_macro.htm |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shared DataSource | Mailmerge | |||
URL as datasource | Mailmerge | |||
Excel datasource | Mailmerge | |||
Datasource Path | Mailmerge | |||
Mailmerge datasource | Mailmerge |