Macro for Mail Merge: problem with Dialogbox for Data Source

[Guus]

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

 

[Graham Mayor]

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Guus]

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]

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Graham Mayor]

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Peter Jamieson]

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]

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]

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]

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 are:
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]

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