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