Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Macro for Mail Merge: problem with Dialogbox for Data Source
Hello,
I frequently have to create Mail Merge results, so I want to create a macro for this. The macro has to do the following tasks: 1. Open my Main Document, with is always the same. 2. Connect the main document to a Data Source (because it changes all the time, I want to show a Dialog Box for selecting the Data Source. 3. Then I do the Merge to a New Blank Document 4. I want to save the merged document, here I want to show again a Dialog Box 5. At the end, I want to close the Main Document without saving. The macro to be completed looks like this: Dim Folder As String Dim SourceFile As String Dim fd As FileDialog Folder = "D:\Feedback\" 'Standard directory ChangeFileOpenDirectory Folder 'Open the existing template Documents.Open Filename:="Primary_Document.doc", _ ConfirmConversions:=True, _ AddToRecentFiles:=False, Format:=wdOpenFormatAuto 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE ActiveDocument.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen Windows("Primary_Document.doc").Activate ActiveDocument.Close Who can help me to complete the VBA code, specially for showing the Dialog Box as mentioned in line "2." and "4."? Thank you in advance, Guus |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Macro for Mail Merge: problem with Dialogbox for Data Source
Something like -
Dim SourceFile As String Dim MergeDoc As Document Dim DataDoc As String Dim fDialog As FileDialog Set fDialog = Application.FileDialog(msoFileDialogFilePicker) SourceFile = "D:\Feedback\Primary_Document.doc" 'Open the existing template Set MergeDoc = Documents.Open(FileName:=SourceFile, _ ConfirmConversions:=True, _ AddToRecentFiles:=False, _ format:=wdOpenFormatAuto) 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE MergeDoc.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) Set fDialog = Application.FileDialog(msoFileDialogFilePicker) With fDialog .Title = "Select data source and click OK" .AllowMultiSelect = False .InitialView = msoFileDialogViewList If .Show -1 Then MsgBox "Cancelled By User", , Title Exit Sub End If DataDoc = fDialog.SelectedItems.Item(1) End With MergeDoc.MailMerge.OpenDataSource name:= _ DataDoc, ConfirmConversions:=False '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen MergeDoc.Close wdDoNotSaveChanges -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Guus wrote: Hello, I frequently have to create Mail Merge results, so I want to create a macro for this. The macro has to do the following tasks: 1. Open my Main Document, with is always the same. 2. Connect the main document to a Data Source (because it changes all the time, I want to show a Dialog Box for selecting the Data Source. 3. Then I do the Merge to a New Blank Document 4. I want to save the merged document, here I want to show again a Dialog Box 5. At the end, I want to close the Main Document without saving. The macro to be completed looks like this: Dim Folder As String Dim SourceFile As String Dim fd As FileDialog Folder = "D:\Feedback\" 'Standard directory ChangeFileOpenDirectory Folder 'Open the existing template Documents.Open Filename:="Primary_Document.doc", _ ConfirmConversions:=True, _ AddToRecentFiles:=False, Format:=wdOpenFormatAuto 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE ActiveDocument.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen Windows("Primary_Document.doc").Activate ActiveDocument.Close Who can help me to complete the VBA code, specially for showing the Dialog Box as mentioned in line "2." and "4."? Thank you in advance, Guus |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Macro for Mail Merge: problem with Dialogbox for Data Source
Hello Graham,
Thank you for your quick reply, but I do not understand where the Data Source (in my case allways an Excel file) will be connected to the Main Document. Is is possible to give me some more details? Thank you in advance, Guus "Graham Mayor" wrote: Something like - Dim SourceFile As String Dim MergeDoc As Document Dim DataDoc As String Dim fDialog As FileDialog Set fDialog = Application.FileDialog(msoFileDialogFilePicker) SourceFile = "D:\Feedback\Primary_Document.doc" 'Open the existing template Set MergeDoc = Documents.Open(FileName:=SourceFile, _ ConfirmConversions:=True, _ AddToRecentFiles:=False, _ format:=wdOpenFormatAuto) 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE MergeDoc.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) Set fDialog = Application.FileDialog(msoFileDialogFilePicker) With fDialog .Title = "Select data source and click OK" .AllowMultiSelect = False .InitialView = msoFileDialogViewList If .Show -1 Then MsgBox "Cancelled By User", , Title Exit Sub End If DataDoc = fDialog.SelectedItems.Item(1) End With MergeDoc.MailMerge.OpenDataSource name:= _ DataDoc, ConfirmConversions:=False '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen MergeDoc.Close wdDoNotSaveChanges -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Guus wrote: Hello, I frequently have to create Mail Merge results, so I want to create a macro for this. The macro has to do the following tasks: 1. Open my Main Document, with is always the same. 2. Connect the main document to a Data Source (because it changes all the time, I want to show a Dialog Box for selecting the Data Source. 3. Then I do the Merge to a New Blank Document 4. I want to save the merged document, here I want to show again a Dialog Box 5. At the end, I want to close the Main Document without saving. The macro to be completed looks like this: Dim Folder As String Dim SourceFile As String Dim fd As FileDialog Folder = "D:\Feedback\" 'Standard directory ChangeFileOpenDirectory Folder 'Open the existing template Documents.Open Filename:="Primary_Document.doc", _ ConfirmConversions:=True, _ AddToRecentFiles:=False, Format:=wdOpenFormatAuto 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE ActiveDocument.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen Windows("Primary_Document.doc").Activate ActiveDocument.Close Who can help me to complete the VBA code, specially for showing the Dialog Box as mentioned in line "2." and "4."? Thank you in advance, Guus |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Macro for Mail Merge: problem with Dialogbox for Data Source
The data source is DataDoc ie the file you select from the dialog. It is
added to the merge document SourceFile = "D:\Feedback\Primary_Document.doc" 'Open the existing template Set MergeDoc = Documents.Open(FileName:=SourceFile, _ ConfirmConversions:=True, _ AddToRecentFiles:=False, _ format:=wdOpenFormatAuto) with the line MergeDoc.MailMerge.OpenDataSource name:= _ DataDoc, ConfirmConversions:=False -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Guus wrote: Hello Graham, Thank you for your quick reply, but I do not understand where the Data Source (in my case allways an Excel file) will be connected to the Main Document. Is is possible to give me some more details? Thank you in advance, Guus "Graham Mayor" wrote: Something like - Dim SourceFile As String Dim MergeDoc As Document Dim DataDoc As String Dim fDialog As FileDialog Set fDialog = Application.FileDialog(msoFileDialogFilePicker) SourceFile = "D:\Feedback\Primary_Document.doc" 'Open the existing template Set MergeDoc = Documents.Open(FileName:=SourceFile, _ ConfirmConversions:=True, _ AddToRecentFiles:=False, _ format:=wdOpenFormatAuto) 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE MergeDoc.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) Set fDialog = Application.FileDialog(msoFileDialogFilePicker) With fDialog .Title = "Select data source and click OK" .AllowMultiSelect = False .InitialView = msoFileDialogViewList If .Show -1 Then MsgBox "Cancelled By User", , Title Exit Sub End If DataDoc = fDialog.SelectedItems.Item(1) End With MergeDoc.MailMerge.OpenDataSource name:= _ DataDoc, ConfirmConversions:=False '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen MergeDoc.Close wdDoNotSaveChanges -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Guus wrote: Hello, I frequently have to create Mail Merge results, so I want to create a macro for this. The macro has to do the following tasks: 1. Open my Main Document, with is always the same. 2. Connect the main document to a Data Source (because it changes all the time, I want to show a Dialog Box for selecting the Data Source. 3. Then I do the Merge to a New Blank Document 4. I want to save the merged document, here I want to show again a Dialog Box 5. At the end, I want to close the Main Document without saving. The macro to be completed looks like this: Dim Folder As String Dim SourceFile As String Dim fd As FileDialog Folder = "D:\Feedback\" 'Standard directory ChangeFileOpenDirectory Folder 'Open the existing template Documents.Open Filename:="Primary_Document.doc", _ ConfirmConversions:=True, _ AddToRecentFiles:=False, Format:=wdOpenFormatAuto 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE ActiveDocument.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen Windows("Primary_Document.doc").Activate ActiveDocument.Close Who can help me to complete the VBA code, specially for showing the Dialog Box as mentioned in line "2." and "4."? Thank you in advance, Guus |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Macro for Mail Merge: problem with Dialogbox for Data Source
If you want to limit the dialog box display to Excel files, use
Set fDialog = Application.FileDialog(msoFileDialogFilePicker) With fDialog .Title = "Select data source and click OK" .AllowMultiSelect = False .InitialView = msoFileDialogViewList .Filters.Clear .Filters.Add "Excel", "*.xl*", 1 If .Show -1 Then MsgBox "Cancelled By User", , Title Exit Sub End If DataDoc = fDialog.SelectedItems.Item(1) End With -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Graham Mayor wrote: The data source is DataDoc ie the file you select from the dialog. It is added to the merge document SourceFile = "D:\Feedback\Primary_Document.doc" 'Open the existing template Set MergeDoc = Documents.Open(FileName:=SourceFile, _ ConfirmConversions:=True, _ AddToRecentFiles:=False, _ format:=wdOpenFormatAuto) with the line MergeDoc.MailMerge.OpenDataSource name:= _ DataDoc, ConfirmConversions:=False Guus wrote: Hello Graham, Thank you for your quick reply, but I do not understand where the Data Source (in my case allways an Excel file) will be connected to the Main Document. Is is possible to give me some more details? Thank you in advance, Guus "Graham Mayor" wrote: Something like - Dim SourceFile As String Dim MergeDoc As Document Dim DataDoc As String Dim fDialog As FileDialog Set fDialog = Application.FileDialog(msoFileDialogFilePicker) SourceFile = "D:\Feedback\Primary_Document.doc" 'Open the existing template Set MergeDoc = Documents.Open(FileName:=SourceFile, _ ConfirmConversions:=True, _ AddToRecentFiles:=False, _ format:=wdOpenFormatAuto) 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE MergeDoc.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) Set fDialog = Application.FileDialog(msoFileDialogFilePicker) With fDialog .Title = "Select data source and click OK" .AllowMultiSelect = False .InitialView = msoFileDialogViewList If .Show -1 Then MsgBox "Cancelled By User", , Title Exit Sub End If DataDoc = fDialog.SelectedItems.Item(1) End With MergeDoc.MailMerge.OpenDataSource name:= _ DataDoc, ConfirmConversions:=False '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen MergeDoc.Close wdDoNotSaveChanges -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Guus wrote: Hello, I frequently have to create Mail Merge results, so I want to create a macro for this. The macro has to do the following tasks: 1. Open my Main Document, with is always the same. 2. Connect the main document to a Data Source (because it changes all the time, I want to show a Dialog Box for selecting the Data Source. 3. Then I do the Merge to a New Blank Document 4. I want to save the merged document, here I want to show again a Dialog Box 5. At the end, I want to close the Main Document without saving. The macro to be completed looks like this: Dim Folder As String Dim SourceFile As String Dim fd As FileDialog Folder = "D:\Feedback\" 'Standard directory ChangeFileOpenDirectory Folder 'Open the existing template Documents.Open Filename:="Primary_Document.doc", _ ConfirmConversions:=True, _ AddToRecentFiles:=False, Format:=wdOpenFormatAuto 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE ActiveDocument.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen Windows("Primary_Document.doc").Activate ActiveDocument.Close Who can help me to complete the VBA code, specially for showing the Dialog Box as mentioned in line "2." and "4."? Thank you in advance, Guus |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Macro for Mail Merge: problem with Dialogbox for Data Source
FWIW, if you are trying to open the Excel sheet without any interaction
from the user, you will have to specify a query that names the /sheet/ or the /named range/ in the SQLStatement parameter of the OpenDataSource parameter. Otherwise the user will always see a dialog box asking for the sheet/range name. That's only going to be easy if you always know that whatever Excel file the user selects, you always want to use the same sheet name. Otherwise you would have to discover the sheet names programmatically and make a decision about which one to use. Peter Jamieson http://tips.pjmsn.me.uk Guus wrote: Hello Graham, Thank you for your quick reply, but I do not understand where the Data Source (in my case allways an Excel file) will be connected to the Main Document. Is is possible to give me some more details? Thank you in advance, Guus "Graham Mayor" wrote: Something like - Dim SourceFile As String Dim MergeDoc As Document Dim DataDoc As String Dim fDialog As FileDialog Set fDialog = Application.FileDialog(msoFileDialogFilePicker) SourceFile = "D:\Feedback\Primary_Document.doc" 'Open the existing template Set MergeDoc = Documents.Open(FileName:=SourceFile, _ ConfirmConversions:=True, _ AddToRecentFiles:=False, _ format:=wdOpenFormatAuto) 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE MergeDoc.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) Set fDialog = Application.FileDialog(msoFileDialogFilePicker) With fDialog .Title = "Select data source and click OK" .AllowMultiSelect = False .InitialView = msoFileDialogViewList If .Show -1 Then MsgBox "Cancelled By User", , Title Exit Sub End If DataDoc = fDialog.SelectedItems.Item(1) End With MergeDoc.MailMerge.OpenDataSource name:= _ DataDoc, ConfirmConversions:=False '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen MergeDoc.Close wdDoNotSaveChanges -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Guus wrote: Hello, I frequently have to create Mail Merge results, so I want to create a macro for this. The macro has to do the following tasks: 1. Open my Main Document, with is always the same. 2. Connect the main document to a Data Source (because it changes all the time, I want to show a Dialog Box for selecting the Data Source. 3. Then I do the Merge to a New Blank Document 4. I want to save the merged document, here I want to show again a Dialog Box 5. At the end, I want to close the Main Document without saving. The macro to be completed looks like this: Dim Folder As String Dim SourceFile As String Dim fd As FileDialog Folder = "D:\Feedback\" 'Standard directory ChangeFileOpenDirectory Folder 'Open the existing template Documents.Open Filename:="Primary_Document.doc", _ ConfirmConversions:=True, _ AddToRecentFiles:=False, Format:=wdOpenFormatAuto 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE ActiveDocument.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen Windows("Primary_Document.doc").Activate ActiveDocument.Close Who can help me to complete the VBA code, specially for showing the Dialog Box as mentioned in line "2." and "4."? Thank you in advance, Guus |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Macro for Mail Merge: problem with Dialogbox for Data Source
Hello Peter/Graham,
Thank you for your instructions, but on the moment with the suggestions it looks like the database will be linked as 'OLE DB databases files' instead of the way - Open Data Source - Files of type = Excel Files (*.xls) - MS Excel Worksheets (VIA DDE) !!!!! (attention to DDE) it works (I think) much quicker, but the problem for me still is: I do not know how to instruct the macro to choose the "DDE-way". Maybe one of you know a solution for this problem too. Although....with a lot of patience, the suggested way works. If my macro works complete I will show the complete VBA-code here too. Thank you in advance. Guus "Peter Jamieson" wrote: FWIW, if you are trying to open the Excel sheet without any interaction from the user, you will have to specify a query that names the /sheet/ or the /named range/ in the SQLStatement parameter of the OpenDataSource parameter. Otherwise the user will always see a dialog box asking for the sheet/range name. That's only going to be easy if you always know that whatever Excel file the user selects, you always want to use the same sheet name. Otherwise you would have to discover the sheet names programmatically and make a decision about which one to use. Peter Jamieson http://tips.pjmsn.me.uk Guus wrote: Hello Graham, Thank you for your quick reply, but I do not understand where the Data Source (in my case allways an Excel file) will be connected to the Main Document. Is is possible to give me some more details? Thank you in advance, Guus "Graham Mayor" wrote: Something like - Dim SourceFile As String Dim MergeDoc As Document Dim DataDoc As String Dim fDialog As FileDialog Set fDialog = Application.FileDialog(msoFileDialogFilePicker) SourceFile = "D:\Feedback\Primary_Document.doc" 'Open the existing template Set MergeDoc = Documents.Open(FileName:=SourceFile, _ ConfirmConversions:=True, _ AddToRecentFiles:=False, _ format:=wdOpenFormatAuto) 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE MergeDoc.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) Set fDialog = Application.FileDialog(msoFileDialogFilePicker) With fDialog .Title = "Select data source and click OK" .AllowMultiSelect = False .InitialView = msoFileDialogViewList If .Show -1 Then MsgBox "Cancelled By User", , Title Exit Sub End If DataDoc = fDialog.SelectedItems.Item(1) End With MergeDoc.MailMerge.OpenDataSource name:= _ DataDoc, ConfirmConversions:=False '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen MergeDoc.Close wdDoNotSaveChanges -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Guus wrote: Hello, I frequently have to create Mail Merge results, so I want to create a macro for this. The macro has to do the following tasks: 1. Open my Main Document, with is always the same. 2. Connect the main document to a Data Source (because it changes all the time, I want to show a Dialog Box for selecting the Data Source. 3. Then I do the Merge to a New Blank Document 4. I want to save the merged document, here I want to show again a Dialog Box 5. At the end, I want to close the Main Document without saving. The macro to be completed looks like this: Dim Folder As String Dim SourceFile As String Dim fd As FileDialog Folder = "D:\Feedback\" 'Standard directory ChangeFileOpenDirectory Folder 'Open the existing template Documents.Open Filename:="Primary_Document.doc", _ ConfirmConversions:=True, _ AddToRecentFiles:=False, Format:=wdOpenFormatAuto 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE ActiveDocument.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen Windows("Primary_Document.doc").Activate ActiveDocument.Close Who can help me to complete the VBA code, specially for showing the Dialog Box as mentioned in line "2." and "4."? Thank you in advance, Guus |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Macro for Mail Merge: problem with Dialogbox for Data Source
As long as it's a .xls, you can use:
Mergedoc.MailMerge.OpenDataSource _ Name:="the full pathname of the .xls", _ Connection:="Entire Spreadsheet", _ Subtype:=wdMergeSubtypeWord2000 However, a. that does not work with .xlsx and the other new Excel 2007 formats. In fact, I do not know how you can write an OpenDataSource (or OpenDataSouce2000) method call that can open those formats using DDE, despite the fact that it is possible to do it manually. My guess is that it is not possible just using VBA, and that if you really had to do it, the solution would be complex. b. It is possible that on a non-English language version of Windows/Word, the name "Entire Spreadsheet" needs to be localised. If so, I hope you do not have to write code that might need to run on more than one language platform.single language platform! Peter Jamieson http://tips.pjmsn.me.uk Guus wrote: Hello Peter/Graham, Thank you for your instructions, but on the moment with the suggestions it looks like the database will be linked as 'OLE DB databases files' instead of the way - Open Data Source - Files of type = Excel Files (*.xls) - MS Excel Worksheets (VIA DDE) !!!!! (attention to DDE) it works (I think) much quicker, but the problem for me still is: I do not know how to instruct the macro to choose the "DDE-way". Maybe one of you know a solution for this problem too. Although....with a lot of patience, the suggested way works. If my macro works complete I will show the complete VBA-code here too. Thank you in advance. Guus "Peter Jamieson" wrote: FWIW, if you are trying to open the Excel sheet without any interaction from the user, you will have to specify a query that names the /sheet/ or the /named range/ in the SQLStatement parameter of the OpenDataSource parameter. Otherwise the user will always see a dialog box asking for the sheet/range name. That's only going to be easy if you always know that whatever Excel file the user selects, you always want to use the same sheet name. Otherwise you would have to discover the sheet names programmatically and make a decision about which one to use. Peter Jamieson http://tips.pjmsn.me.uk Guus wrote: Hello Graham, Thank you for your quick reply, but I do not understand where the Data Source (in my case allways an Excel file) will be connected to the Main Document. Is is possible to give me some more details? Thank you in advance, Guus "Graham Mayor" wrote: Something like - Dim SourceFile As String Dim MergeDoc As Document Dim DataDoc As String Dim fDialog As FileDialog Set fDialog = Application.FileDialog(msoFileDialogFilePicker) SourceFile = "D:\Feedback\Primary_Document.doc" 'Open the existing template Set MergeDoc = Documents.Open(FileName:=SourceFile, _ ConfirmConversions:=True, _ AddToRecentFiles:=False, _ format:=wdOpenFormatAuto) 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE MergeDoc.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) Set fDialog = Application.FileDialog(msoFileDialogFilePicker) With fDialog .Title = "Select data source and click OK" .AllowMultiSelect = False .InitialView = msoFileDialogViewList If .Show -1 Then MsgBox "Cancelled By User", , Title Exit Sub End If DataDoc = fDialog.SelectedItems.Item(1) End With MergeDoc.MailMerge.OpenDataSource name:= _ DataDoc, ConfirmConversions:=False '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen MergeDoc.Close wdDoNotSaveChanges -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Guus wrote: Hello, I frequently have to create Mail Merge results, so I want to create a macro for this. The macro has to do the following tasks: 1. Open my Main Document, with is always the same. 2. Connect the main document to a Data Source (because it changes all the time, I want to show a Dialog Box for selecting the Data Source. 3. Then I do the Merge to a New Blank Document 4. I want to save the merged document, here I want to show again a Dialog Box 5. At the end, I want to close the Main Document without saving. The macro to be completed looks like this: Dim Folder As String Dim SourceFile As String Dim fd As FileDialog Folder = "D:\Feedback\" 'Standard directory ChangeFileOpenDirectory Folder 'Open the existing template Documents.Open Filename:="Primary_Document.doc", _ ConfirmConversions:=True, _ AddToRecentFiles:=False, Format:=wdOpenFormatAuto 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE ActiveDocument.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen Windows("Primary_Document.doc").Activate ActiveDocument.Close Who can help me to complete the VBA code, specially for showing the Dialog Box as mentioned in line "2." and "4."? Thank you in advance, Guus |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Macro for Mail Merge: problem with Dialogbox for Data Source
Hello everyone,
The result of the tricks you gave me is that the macro works!!! But an extra reason to create the macro was: because I have to do 10 or more times the same action. So I thought that a macro would go: 1. more realiable. 2. quicker Quicker it is on the moment 'no way', maybe I have still done something wrong. Maybe there still is a suggestion to run faster. The code of the Macro and its additional Function a Sub CreatingMergeFeedback() ' ' Macro Creating Quick Merge Feedback ' Macro recorded 2/11/2009 by User1 ' Dim Folder As String Dim SourceFile As String Dim strFile As Variant Dim venster As Window Dim fd As FileDialog Folder = "D:\2008-2009\Feedback\" 'Setup Standard Directory ChangeFileOpenDirectory Folder 'Open the Standard Document to merge the Data with Documents.Open FileName:="Primary_document.doc", _ ConfirmConversions:=True, _ AddToRecentFiles:=False, Format:=wdOpenFormatAuto 'Show the Merge Toolbar CommandBars("Mail Merge").Visible = True '2. DATASOUREE strFile = "*.xls" strFile = DataCatch() Options.ConfirmConversions = True ActiveDocument.MailMerge.OpenDataSource _ Name:=strFile, _ ReadOnly:=True, LinkToSource:=True, _ Revert:=False, _ Format:=wdOpenFormatAuto, _ Connection:="Entire Spreadsheet", _ SubType:=wdMergeSubTypeOther '3. Execute the Merge Action With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Save the New Created File Dialogs(wdDialogFileSaveAs).Show '5. Close the Window with the Template File Windows("Primary_document.doc").Activate ActiveDocument.Close End Sub ---------------------------- Function DataCatch() As String Dim strFile As String Dim dlgFile As FileDialog Set dlgFile = Application.FileDialog(msoFileDialogFilePicker) With dlgFile .Title = "Open the Excelsheet with calculated data " .InitialView = msoFileDialogViewList .Filters.Clear .Filters.Add "Excel", "*.xls", 1 .AllowMultiSelect = False If .Show -1 Then MsgBox "Cancelled by User", , "Open the Excelsheet with calculations" Else DataCatch = .SelectedItems(1) End If End With End Function "Peter Jamieson" wrote: As long as it's a .xls, you can use: Mergedoc.MailMerge.OpenDataSource _ Name:="the full pathname of the .xls", _ Connection:="Entire Spreadsheet", _ Subtype:=wdMergeSubtypeWord2000 However, a. that does not work with .xlsx and the other new Excel 2007 formats. In fact, I do not know how you can write an OpenDataSource (or OpenDataSouce2000) method call that can open those formats using DDE, despite the fact that it is possible to do it manually. My guess is that it is not possible just using VBA, and that if you really had to do it, the solution would be complex. b. It is possible that on a non-English language version of Windows/Word, the name "Entire Spreadsheet" needs to be localised. If so, I hope you do not have to write code that might need to run on more than one language platform.single language platform! Peter Jamieson http://tips.pjmsn.me.uk Guus wrote: Hello Peter/Graham, Thank you for your instructions, but on the moment with the suggestions it looks like the database will be linked as 'OLE DB databases files' instead of the way - Open Data Source - Files of type = Excel Files (*.xls) - MS Excel Worksheets (VIA DDE) !!!!! (attention to DDE) it works (I think) much quicker, but the problem for me still is: I do not know how to instruct the macro to choose the "DDE-way". Maybe one of you know a solution for this problem too. Although....with a lot of patience, the suggested way works. If my macro works complete I will show the complete VBA-code here too. Thank you in advance. Guus "Peter Jamieson" wrote: FWIW, if you are trying to open the Excel sheet without any interaction from the user, you will have to specify a query that names the /sheet/ or the /named range/ in the SQLStatement parameter of the OpenDataSource parameter. Otherwise the user will always see a dialog box asking for the sheet/range name. That's only going to be easy if you always know that whatever Excel file the user selects, you always want to use the same sheet name. Otherwise you would have to discover the sheet names programmatically and make a decision about which one to use. Peter Jamieson http://tips.pjmsn.me.uk Guus wrote: Hello Graham, Thank you for your quick reply, but I do not understand where the Data Source (in my case allways an Excel file) will be connected to the Main Document. Is is possible to give me some more details? Thank you in advance, Guus "Graham Mayor" wrote: Something like - Dim SourceFile As String Dim MergeDoc As Document Dim DataDoc As String Dim fDialog As FileDialog Set fDialog = Application.FileDialog(msoFileDialogFilePicker) SourceFile = "D:\Feedback\Primary_Document.doc" 'Open the existing template Set MergeDoc = Documents.Open(FileName:=SourceFile, _ ConfirmConversions:=True, _ AddToRecentFiles:=False, _ format:=wdOpenFormatAuto) 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE MergeDoc.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) Set fDialog = Application.FileDialog(msoFileDialogFilePicker) With fDialog .Title = "Select data source and click OK" .AllowMultiSelect = False .InitialView = msoFileDialogViewList If .Show -1 Then MsgBox "Cancelled By User", , Title Exit Sub End If DataDoc = fDialog.SelectedItems.Item(1) End With MergeDoc.MailMerge.OpenDataSource name:= _ DataDoc, ConfirmConversions:=False '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen MergeDoc.Close wdDoNotSaveChanges -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Guus wrote: Hello, I frequently have to create Mail Merge results, so I want to create a macro for this. The macro has to do the following tasks: 1. Open my Main Document, with is always the same. 2. Connect the main document to a Data Source (because it changes all the time, I want to show a Dialog Box for selecting the Data Source. 3. Then I do the Merge to a New Blank Document 4. I want to save the merged document, here I want to show again a Dialog Box 5. At the end, I want to close the Main Document without saving. The macro to be completed looks like this: Dim Folder As String Dim SourceFile As String Dim fd As FileDialog Folder = "D:\Feedback\" 'Standard directory ChangeFileOpenDirectory Folder 'Open the existing template Documents.Open Filename:="Primary_Document.doc", _ ConfirmConversions:=True, _ AddToRecentFiles:=False, Format:=wdOpenFormatAuto 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE ActiveDocument.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen Windows("Primary_Document.doc").Activate ActiveDocument.Close Who can help me to complete the VBA code, specially for showing the Dialog Box as mentioned in line "2." and "4."? Thank you in advance, Guus |
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Macro for Mail Merge: problem with Dialogbox for Data Source
Quicker it is on the moment 'no way', maybe I have still done something wrong. Maybe there still is a suggestion to run faster. DDE will always be slow because a. Word has to start Excel if it is not already running b. Word has to ask Excel to open the workbook if it is not already open c. openig a workbook and displaying it takes time d. Word and Excel have to do inter-process communication which is probably more "expensive" than f Word just gets the data from a file via a driver (ODBC) or provider (OLE DB). The questions are a. whether you need to use DDE for other reasons b. if you do, whether there are other "speed-up" strategies: perhaps only "get Excel to open all the workbooks you need in advance if you can" ? c. if you do not, do you already know what sheet names your users will be using, or will you have to find out in code? Peter Jamieson http://tips.pjmsn.me.uk Guus wrote: Hello everyone, The result of the tricks you gave me is that the macro works!!! But an extra reason to create the macro was: because I have to do 10 or more times the same action. So I thought that a macro would go: 1. more realiable. 2. quicker Quicker it is on the moment 'no way', maybe I have still done something wrong. Maybe there still is a suggestion to run faster. The code of the Macro and its additional Function a Sub CreatingMergeFeedback() ' ' Macro Creating Quick Merge Feedback ' Macro recorded 2/11/2009 by User1 ' Dim Folder As String Dim SourceFile As String Dim strFile As Variant Dim venster As Window Dim fd As FileDialog Folder = "D:\2008-2009\Feedback\" 'Setup Standard Directory ChangeFileOpenDirectory Folder 'Open the Standard Document to merge the Data with Documents.Open FileName:="Primary_document.doc", _ ConfirmConversions:=True, _ AddToRecentFiles:=False, Format:=wdOpenFormatAuto 'Show the Merge Toolbar CommandBars("Mail Merge").Visible = True '2. DATASOUREE strFile = "*.xls" strFile = DataCatch() Options.ConfirmConversions = True ActiveDocument.MailMerge.OpenDataSource _ Name:=strFile, _ ReadOnly:=True, LinkToSource:=True, _ Revert:=False, _ Format:=wdOpenFormatAuto, _ Connection:="Entire Spreadsheet", _ SubType:=wdMergeSubTypeOther '3. Execute the Merge Action With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Save the New Created File Dialogs(wdDialogFileSaveAs).Show '5. Close the Window with the Template File Windows("Primary_document.doc").Activate ActiveDocument.Close End Sub ---------------------------- Function DataCatch() As String Dim strFile As String Dim dlgFile As FileDialog Set dlgFile = Application.FileDialog(msoFileDialogFilePicker) With dlgFile .Title = "Open the Excelsheet with calculated data " .InitialView = msoFileDialogViewList .Filters.Clear .Filters.Add "Excel", "*.xls", 1 .AllowMultiSelect = False If .Show -1 Then MsgBox "Cancelled by User", , "Open the Excelsheet with calculations" Else DataCatch = .SelectedItems(1) End If End With End Function "Peter Jamieson" wrote: As long as it's a .xls, you can use: Mergedoc.MailMerge.OpenDataSource _ Name:="the full pathname of the .xls", _ Connection:="Entire Spreadsheet", _ Subtype:=wdMergeSubtypeWord2000 However, a. that does not work with .xlsx and the other new Excel 2007 formats. In fact, I do not know how you can write an OpenDataSource (or OpenDataSouce2000) method call that can open those formats using DDE, despite the fact that it is possible to do it manually. My guess is that it is not possible just using VBA, and that if you really had to do it, the solution would be complex. b. It is possible that on a non-English language version of Windows/Word, the name "Entire Spreadsheet" needs to be localised. If so, I hope you do not have to write code that might need to run on more than one language platform.single language platform! Peter Jamieson http://tips.pjmsn.me.uk Guus wrote: Hello Peter/Graham, Thank you for your instructions, but on the moment with the suggestions it looks like the database will be linked as 'OLE DB databases files' instead of the way - Open Data Source - Files of type = Excel Files (*.xls) - MS Excel Worksheets (VIA DDE) !!!!! (attention to DDE) it works (I think) much quicker, but the problem for me still is: I do not know how to instruct the macro to choose the "DDE-way". Maybe one of you know a solution for this problem too. Although....with a lot of patience, the suggested way works. If my macro works complete I will show the complete VBA-code here too. Thank you in advance. Guus "Peter Jamieson" wrote: FWIW, if you are trying to open the Excel sheet without any interaction from the user, you will have to specify a query that names the /sheet/ or the /named range/ in the SQLStatement parameter of the OpenDataSource parameter. Otherwise the user will always see a dialog box asking for the sheet/range name. That's only going to be easy if you always know that whatever Excel file the user selects, you always want to use the same sheet name. Otherwise you would have to discover the sheet names programmatically and make a decision about which one to use. Peter Jamieson http://tips.pjmsn.me.uk Guus wrote: Hello Graham, Thank you for your quick reply, but I do not understand where the Data Source (in my case allways an Excel file) will be connected to the Main Document. Is is possible to give me some more details? Thank you in advance, Guus "Graham Mayor" wrote: Something like - Dim SourceFile As String Dim MergeDoc As Document Dim DataDoc As String Dim fDialog As FileDialog Set fDialog = Application.FileDialog(msoFileDialogFilePicker) SourceFile = "D:\Feedback\Primary_Document.doc" 'Open the existing template Set MergeDoc = Documents.Open(FileName:=SourceFile, _ ConfirmConversions:=True, _ AddToRecentFiles:=False, _ format:=wdOpenFormatAuto) 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE MergeDoc.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) Set fDialog = Application.FileDialog(msoFileDialogFilePicker) With fDialog .Title = "Select data source and click OK" .AllowMultiSelect = False .InitialView = msoFileDialogViewList If .Show -1 Then MsgBox "Cancelled By User", , Title Exit Sub End If DataDoc = fDialog.SelectedItems.Item(1) End With MergeDoc.MailMerge.OpenDataSource name:= _ DataDoc, ConfirmConversions:=False '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen MergeDoc.Close wdDoNotSaveChanges -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Guus wrote: Hello, I frequently have to create Mail Merge results, so I want to create a macro for this. The macro has to do the following tasks: 1. Open my Main Document, with is always the same. 2. Connect the main document to a Data Source (because it changes all the time, I want to show a Dialog Box for selecting the Data Source. 3. Then I do the Merge to a New Blank Document 4. I want to save the merged document, here I want to show again a Dialog Box 5. At the end, I want to close the Main Document without saving. The macro to be completed looks like this: Dim Folder As String Dim SourceFile As String Dim fd As FileDialog Folder = "D:\Feedback\" 'Standard directory ChangeFileOpenDirectory Folder 'Open the existing template Documents.Open Filename:="Primary_Document.doc", _ ConfirmConversions:=True, _ AddToRecentFiles:=False, Format:=wdOpenFormatAuto 'Show the Toolbar "Mail Merge" CommandBars("Mail Merge").Visible = True 'Configure the Main Document '1. TYPE ActiveDocument.MailMerge.MainDocumentType = wdFormLetters '2. DATA SOURCE (MY PROBLEM IS "HOW TO SHOW A DIALOGBOX" !!!!!) '3. Maak een nieuw document van de samengevoegde gegevens With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = wdDefaultFirstRecord .DataSource.LastRecord = wdDefaultLastRecord .Execute Pause:=False End With '4. Bewaar het nieuwe samengevoegde bestand Dialogs(wdDialogFileSaveAs).Show '5. Sluit het sjabloon voor samenvoegen Windows("Primary_Document.doc").Activate ActiveDocument.Close Who can help me to complete the VBA code, specially for showing the Dialog Box as mentioned in line "2." and "4."? Thank you in advance, Guus |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word 2003 Mail Merge Problem when E-Mailing Data Source | Mailmerge | |||
Mail Merge Problem: Not Merging Source Data | Mailmerge | |||
DotNet Data Table as Mail Merge Data Source | Mailmerge | |||
data in mail merge letter being cut off - data source field size? | Mailmerge | |||
Mail merge data source problem | Mailmerge |