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