[Peter Jamieson]

Given what you say, I suggest

OpenDataSource _
Name:="C:\LettersForms\Full Database.xls", _
sqlstatement1:="SELECT * FROM [DataBase$]"
End With

Peter Jamieson

"BobR" wrote in message
.. .
Thank you
"Peter Jamieson" wrote in message
...
We're going to have the 3 computers we have with 2002 still on it
upgrade to 2003 and that should fix that issue.

I'd consider waiting and seeing. If everything works OK with 2002, why
change? On the other hand, if it's simpler to get everything in line with
2003, why not?
We have just salvaged about 20 desktops and since we own 2003 on them
we're going to remove 3 of them and put them on our 3 missing laptops.
Doesn't make sense to let something we own go out the door if we can
remove it and use it. If it were many more I would say the same as you,
but it's only the three....

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??

If you make the reference, then distribute an Excel workbook that uses
it, it should work. But you really need to /test/ as many of the things
that you eventually intend to /do/ as you possibly can.
Thanks for the heads up, I've got two other testers that I'm having use it
and when I disburse it to them and the others I'll try your suggestion..




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...

Name:=ActiveWorkbook.FullName

is intended to get the full path name of the workbook the user is working
with, using the Excel Object Model. ActiveWorkbook.FullName has a
specific meaning in that case, If you want to use a different file name,
provide the full path name, e.g.

Name:="Name=C:\folder one\folder two\yourworkbookname.xls", _


*****Did I form this one right???******
OpenDataSource _
Name:=C:\LettersForms\Full Database.xls, _
sqlstatement1:="SELECT * FROM [" Full Database.DataBase & "$]"
End With

This is my path that will not change and my "Full Database.xls" will never
change and the Select From is always the "Database$" tab.
your thoughts once more?

In a similar way,

SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]"

is intended to get the active worksheet name from the Excel Object Model.
ActveWorkbook.ActiveSheet (and .Name) have a specific meaning in that
case. Appending "$" may or may not be necessary. If you are trying to use

"SELECT * from [" & MyDataBase.Database & "$]"

it suggests you might be using some other object model (it looks a bit
like Access/DAO to me). In that case, let us know.

Everythin here is in an Excel database for the WORD documents merging
source.

THanks
BOB


Peter Jamieson

"BobR" wrote in message
.. .
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