Peter, Thank you so much.
We're going to have the 3 computers we have with 2002 still on it upgrade to
2003 and that should fix that issue.
2. When you create the macro I suggested, you have to use the
Tools|References menu option in the VBA Editor to make a reference to
Word. Is there a way you know of to make the code enable this menu option,
so I don't have to have issues with the operator of the computer??
3. With objDoc.MailMerge
.OpenDataSource _
Name:=ActiveWorkbook.FullName, _
sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name &
"$]"
End With
I'm confused witht he third row.. for me woudl it read Name=C:\folder
one\folder two,_ sqlstatement1.="SELECT * from [" & MyDataBase.Database &
"$"]"
or did I miss the boat...
BOB
"Peter Jamieson" wrote in message
...
1. A number of problems in 2002 were fixed in 2003 - if you encounter
them, I wouldn't waste much time trying to compromise to make 2002 work -
by all means raise specific problems here but there's no guarantee that
there will be a /simple/ solution, if any.
I couldn't grasp the issue of reference to WORD in the VBA editor.
2. When you create the macro I suggested, you have to use the
Tools|References menu option in the VBA Editor to make a reference to
Word. I would suggest that you work in Word/Excel 2002 and see what
happens as/when you port to 2003, but you would be better off searching
one of the VBA-oriented groups (e.g. using groups.google.com ) for
information on cross-version development. In this case, you should not
encounter any differences in the /interfaces (i.e. method parameters
should be the same on both versions, and most are omitted in the code I
suggested, but the behaviour of the method may differ).
Peter Jamieson
"BobR" wrote in message
.. .
Peter, Thank you. I will try this out and see if I can make it work. I
had two different versions of WORD that I have to work with, 2002 and
2003 and I couldn't grasp the issue of reference to WORD in the VBA
editor.
If I have two different versions is there something that could cover both
of them??
Bob
"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