Data Source being lost each time the word merge document starts

[BobR]

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

 

[Peter Jamieson]

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]

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

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

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

 

[Peter Jamieson]

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

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

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

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

 

[BobR]

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

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

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

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

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

 

[Peter Jamieson]

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?

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.

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", _

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.

Peter Jamieson

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

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

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

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

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

 

[BobR]

Thank you

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

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

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

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

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

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

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

 

[BobR]

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

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

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

 

[Peter Jamieson]

Given what you say, I suggest

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

Peter Jamieson

Thank you

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

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

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



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

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

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

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

 

[Peter Jamieson]

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

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

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

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

 

[BobR]

Peter, Thank you so much for the Specific information you gave to me. My
hat's off to all of you that help us out on these forums and Thanks just
doesn't seem like enough but that's about all we've go, so Thanks

BOB

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

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

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

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