Perhaps something like the following Excel VBA sub for starters (NB all the
code is executed in Excel, so it doesn't use a "selfactivating macro" as you
were asking for. You would need to make a reference to the Word object in
the Excel VBA editor.
Sub Mergecurrentsheet()
Dim objWord As Word.Application
Dim objDoc As Word.Document
Set objWord = CreateObject("Word.Application")
If objWord Is Nothing Then
MsgBox "Could not create the Word object"
Exit Sub
End If
' substitute the correct file name here
Set objDoc = objWord.Documents.Open("C:\MyFiles\MergeLetters\?? ")
If objDoc Is Nothing Then
MsgBox "Could not open the specified document"
objWord.Quit
Set objWord = Nothing
Exit Sub
End If
objDoc.Activate
objWord.Visible = True
With objDoc.MailMerge
.OpenDataSource _
Name:=ActiveWorkbook.FullName, _
sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name &
"$]"
End With
Set objDoc = Nothing
Set objWord = Nothing
End Sub
Notes:
a. this doesn't actually do the merge, but it could be done - you would
need to specify merge type, destination, and so on
b. do your own error handling code
c. see
http://tips.pjmsn.me.uk/t0003.htm for general info. about problems
merging with Excel data.There are general problems getting data from Excel
into Word. In this case, you might encounter locking problems if, for
example, you are in the middle of editing an Excel cell when you invoke the
macro. Saving the workbook before running the macro is probably a good move.
Peter Jamieson
"BobR" wrote in message
...
Good Day,
I have numerous letters that use an Excel spreadsheet as the data source
and the documents are in WORD 2003 and the data is in EXCEL 2003.
I have code in the excel spreadsheet to simply go to
C:\MyFiles\MergeLetters\?? (?? is a specific named file) and open the WORD
document.
Once the document is opened, it just sits there and is ready for
instructions as it is a non merge format at this time. (We've tried to
have the opening code from excel to make it open to the data source and
had really poor results with it.) My question is can someone give me the
proper code (IF this can be done) that when letter one located at
C:\MyFiles\MergeLetters\letter one.doc is called upon a selfactivating
macro will fire and do the following?
When opened it will fire and make the document a mail merge with
"DataSource" as the data source. The datasource is a shortcut in the My
Docs\M\y Data Sources\"DataSource". Once this is opened the select data
tab comes up and we select the tab of data on excel. (If this could be
done with the word code that would be great). Something like datasource is
My Docs\My DataSources\DataSource and tab "datasourcetab".
Then I would have the document openedas a mail merge and either I would
have to make a selection for the datasource or it would be opened to it
already??
Hope the explanation is understandable Thanks for the Assistance.
BOB