Getting Word to work with Excel and have it bind with excel without going into the code

[LabrGuy Bob R]

Hello,
I have WORD 2003 and Excel 2003 with WINXP Pro and have the following
problem. I'm opening a WORD (mail merge) document through code in Excel.
I've gotten assistance with the code here and other places and the code
works just as it's supposed to in bringing up WORD and selecting the
particular datasheet to merge from, all automatically. It does give me
issues regarding 0's instead of some proper entires but I'm going to convert
them all to text and see if I can get it to work. '
My problem When I start my excel database and try to launch a WORD document
with the code I get a warning that the ...... isn't connected. Then I need
to go to the code, select relationships and then select the WORD connections
that I need. I'm afraid that the people that are going to use this will
cause damage to the code or won't be able to get this done.

Here is the code I'm using (again thanks to a helper on here) Could someone
please look at it and see if I have too many of the wdApp objDoc objWord in
it and especially help me with an automated way to set a reference to the
Word Object library, if anyone knows this trick in VB. We will all be using
Excel 2003 so I shouldn't have any other versions of Excel or Word around.

Any help would be appreciated.
Thanks
BOb



Sub OpenWordDocument(WhichLetter As String)
'In order to use this code you must set a reference to the
'Word object library by doing this. In the VB Editor click
'Tools, References. Then search for Microsoft Word n.n Object Library
'where n.n will depend on your version of Word.
Dim wdApp As Object
Dim objWord As Word.Application
Dim objDoc As Word.Document
Dim wdDoc As Variant
Application.ScreenUpdating = True
Set objWord = CreateObject("Word.Application")
If objWord Is Nothing Then
MsgBox "Could not create the Word object"
Exit Sub
End If
Set objDoc = objWord.Documents.Open("C:\LettersForms\LETTERS\" &
WhichLetter & ".doc") 'actual name went here
If objDoc Is Nothing Then
MsgBox "Could not open the specified document"
objWord.Quit
Set objWord = Nothing
Exit Sub
End If
If WhichLetter = "03CInterview" Or WhichLetter = "03FInterview" Or
WhichLetter = "10FAgreement" Or _
WhichLetter = "11FCMP" Or WhichLetter = "12AExhibit" Or
WhichLetter = "12BExhibit" Or _
WhichLetter = "13IA" Then Exit Sub
objDoc.Activate
objWord.Visible = True
With objDoc.MailMerge
'.OpenDataSource Name:=C:\LettersForms\Full Database.xls,
_
'sqlstatement1:="SELECT * FROM [" Full Database.DataBase &
"$]"
.OpenDataSource Name:="C:\LettersForms\Full Database.xls",
_
sqlstatement1:="SELECT * FROM [DataBase$]"
End With
Set objDoc = Nothing
Set objWord = Nothing

 

[Peter Jamieson]

You may have to modify the registry as per

http://support.microsoft.com/?kbid=825765

The easiest way to do that for each workstation is to create the necessary
entriy on one system, export it to a .reg file, edit with Notepad to cut out
everything except the entries you need, then distribute it and ask users to
double-click on the .reg. That assumes that admin. lockdowns are not in
place to prevent exactly that (in which case you probably have your own
mechanism for distributing changes).

Here is the code I'm using (again thanks to a helper on here) Could
someone please look at it and see if I have too many of the wdApp objDoc
objWord in

So far, you don't need wdApp or wdDoc.

After you have made the connection, are you leaving the user to do the
actual merge?

Is the Excel workbook the current workbook in Excel? If so, you can use

.OpenDataSource _
Name:=ActiveWorkbook.FullName, _
SQLStatement:="SELECT * FROM [DataBase$]"

(note that you should really use SQLStatement above, not SQLStatement1 as
you have at present, although it doesn't actually make any difference since
the statement word uses is SQLStatement & SQLStatement if you see what I
mean)

If you need to use DDE as discussed elsewhere, try

.OpenDataSource _
Name:=ActiveWorkbook.FullName, _
Connection:="Entire Spreadsheet", _
SubType:=wdMergeSubTypeWord2000

it and especially help me with an automated way to set a reference to the
Word Object library,

Sorry, I don't know how to do that. But you may be able to get away with

Dim objWord As Object
Dim objDoc As Object

(AIUI the references are there to assist with "Intellisense/Autocomplete"
and of course so that you can be specific about the types of the objects
concerned).

Peter Jamieson

Hello,
I have WORD 2003 and Excel 2003 with WINXP Pro and have the following
problem. I'm opening a WORD (mail merge) document through code in Excel.
I've gotten assistance with the code here and other places and the code
works just as it's supposed to in bringing up WORD and selecting the
particular datasheet to merge from, all automatically. It does give me
issues regarding 0's instead of some proper entires but I'm going to
convert them all to text and see if I can get it to work. '
My problem When I start my excel database and try to launch a WORD
document with the code I get a warning that the ...... isn't connected.
Then I need to go to the code, select relationships and then select the
WORD connections that I need. I'm afraid that the people that are going to
use this will cause damage to the code or won't be able to get this done.

Here is the code I'm using (again thanks to a helper on here) Could
someone please look at it and see if I have too many of the wdApp objDoc
objWord in it and especially help me with an automated way to set a
reference to the Word Object library, if anyone knows this trick in VB. We
will all be using Excel 2003 so I shouldn't have any other versions of
Excel or Word around.

Any help would be appreciated.
Thanks
BOb



Sub OpenWordDocument(WhichLetter As String)
'In order to use this code you must set a reference to the
'Word object library by doing this. In the VB Editor click
'Tools, References. Then search for Microsoft Word n.n Object Library
'where n.n will depend on your version of Word.
Dim wdApp As Object
Dim objWord As Word.Application
Dim objDoc As Word.Document
Dim wdDoc As Variant
Application.ScreenUpdating = True
Set objWord = CreateObject("Word.Application")
If objWord Is Nothing Then
MsgBox "Could not create the Word object"
Exit Sub
End If
Set objDoc = objWord.Documents.Open("C:\LettersForms\LETTERS\" &
WhichLetter & ".doc") 'actual name went here
If objDoc Is Nothing Then
MsgBox "Could not open the specified document"
objWord.Quit
Set objWord = Nothing
Exit Sub
End If
If WhichLetter = "03CInterview" Or WhichLetter = "03FInterview" Or
WhichLetter = "10FAgreement" Or _
WhichLetter = "11FCMP" Or WhichLetter = "12AExhibit" Or
WhichLetter = "12BExhibit" Or _
WhichLetter = "13IA" Then Exit Sub
objDoc.Activate
objWord.Visible = True
With objDoc.MailMerge
'.OpenDataSource Name:=C:\LettersForms\Full Database.xls,
_
'sqlstatement1:="SELECT * FROM [" Full Database.DataBase
& "$]"
.OpenDataSource Name:="C:\LettersForms\Full
Database.xls", _
sqlstatement1:="SELECT * FROM [DataBase$]"
End With
Set objDoc = Nothing
Set objWord = Nothing

 

[LabrGuy Bob R]

Thanks for the help. It does have a lockdown so that would be out. I'm going
to modify the code I'm using to clean it up as you suggested.

So far, you don't need wdApp or wdDoc.
Thanks
After you have made the connection, are you leaving the user to do the
actual merge?

The user can select the record number and must select the view the data.

Is the Excel workbook the current workbook in Excel? If so, you can use

.OpenDataSource _
Name:=ActiveWorkbook.FullName, _
SQLStatement:="SELECT * FROM [DataBase$]"

(note that you should really use SQLStatement above, not SQLStatement1 as
you have at present, although it doesn't actually make any difference
since the statement word uses is SQLStatement & SQLStatement if you see
what I mean)

If you need to use DDE as discussed elsewhere, try

.OpenDataSource _
Name:=ActiveWorkbook.FullName, _
Connection:="Entire Spreadsheet", _
SubType:=wdMergeSubTypeWord2000

it and especially help me with an automated way to set a reference to the
Word Object library,

Sorry, I don't know how to do that. But you may be able to get away with

Dim objWord As Object
Dim objDoc As Object

I was thinking that might actually be the case but wasn't sure.

Thanks so much for the help and the response. It's greatly appreciated.
BOB Reynolds