OPen the module & macro in Excel VBA.
Click Tools|References
Lokk down the list till you find something like
Microsoft Word 11.0 Object Library (it might be "10.0" if you're using Word
2002)
Check the box to the left of the name
Click OK
Peter Jamieson
"BobR" wrote in message
.. .
I got the following at the beginning of this code
Dim objWord As Word.Application
Dim objDoc As Word.Document
User-defined type not defined........
any ideas..?//
The first line shows the error and the
"Peter Jamieson" wrote in message
...
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