Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / November 2006

Tip: Looking for answers? Try searching our database.

mailmerge to Word From Access

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Loralee - 23 Nov 2006 15:52 GMT
My Access app which ran FINE until yesterday is now throwing a "missing DLL"
error.  The code that is now failinig is in a button that uses the common
dialog control and opens Word to complete mailmerge execution.  It fails at
getobject().  

See below.  
1) Does anyone know of another way to get to Word from Access?
2) Has this recently happened to anyone else?

====================
Dim strFilePath As String
Dim objWord As Word.Document
Dim strPatientID As String '   use to link to patient
Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
Dim strSQLMerge As String

'i removed the SQL concantenation code as error not there

CurrentDb.QueryDefs("qrymailmerge").SQL = strSQLMerge

Set objWord = GetObject(strFilePath, "Word.document") 'fails here
'   make word visible
objWord.Application.Visible = True

'   execute the mail merge
objWord.MailMerge.Execute
objWord.Close
Thanks!
Signature

Loralee

Douglas J. Steele - 23 Nov 2006 16:20 GMT
Where does strFilePath get set?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> My Access app which ran FINE until yesterday is now throwing a "missing
> DLL"
[quoted text clipped - 28 lines]
> objWord.Close
> Thanks!
Loralee - 23 Nov 2006 19:16 GMT
In the area I cut out (for clairity).  When I step-through the file name is
passing fine.  The WHOLE sub is:

Private Sub cmdMergeLetter_Click()
'   this is an attempt to open a Word document and start a mail merge
Dim strFilePath As String
Dim objWord As Word.Document
Dim strPatientID As String '   use to link to patient
Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
Dim strSQLMerge As String

strPatientID = Forms!frmpatientdata.txtPatientID
strSELECT = "SELECT tblPatient.[PatientID], tblPatient.[PtFirstName],
tblPatient.[PtLastName], tblPatient.[PtBD], tblCity.[City],
tblPatient.[PtAddress], tblPatient.[PtZip], tblReferral.[ReferBy]"
strFROM = " FROM (tblCity INNER JOIN tblPatient ON tblCity.[CityID] =
tblPatient.[PtCityFK]) INNER JOIN tblReferral ON tblPatient.[PatientID] =
tblReferral.[PtFK]"
strWHERE = " WHERE ((([PatientID])= " & strPatientID & "))"
strSQLMerge = strSELECT & strFROM & strWHERE

CurrentDb.QueryDefs("qrymailmerge").SQL = strSQLMerge

Me.cmdlg.DialogTitle = "Choose File Name and Location"
Me.cmdlg.ShowOpen
strFilePath = Me.cmdlg.FileName

Set objWord = GetObject(strFilePath, "Word.document")

'   make word visible
objWord.Application.Visible = True

'   execute the mail merge
objWord.MailMerge.Execute
objWord.Close

Signature

Loralee

> Where does strFilePath get set?
>
[quoted text clipped - 30 lines]
> > objWord.Close
> > Thanks!
Loralee - 23 Nov 2006 19:43 GMT
A few other things:  OS = XP Pro; running Office 2K,  have no idea about
anything else as the system is part of a network.  But prior to yesterday it
ran fine.  
Signature

Loralee

> In the area I cut out (for clairity).  When I step-through the file name is
> passing fine.  The WHOLE sub is:
[quoted text clipped - 68 lines]
> > > objWord.Close
> > > Thanks!
Douglas J. Steele - 23 Nov 2006 21:51 GMT
Scrap the Common Dialog. Use the API approach demonstrated in
http://www.mvps.org/access/api/api0001.htm at "The Access Web".

Far more reliable...

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> In the area I cut out (for clairity).  When I step-through the file name
> is
[quoted text clipped - 71 lines]
>> > objWord.Close
>> > Thanks!
Daniel - 24 Nov 2006 12:45 GMT
Douglas,

May I inquiries as to why one should use the API instead of the common dialog?

Thank you,

Daniel

> Scrap the Common Dialog. Use the API approach demonstrated in
> http://www.mvps.org/access/api/api0001.htm at "The Access Web".
[quoted text clipped - 76 lines]
> >> > objWord.Close
> >> > Thanks!
Douglas J. Steele - 24 Nov 2006 22:07 GMT
The major reason is that the common dialog is prone to versioning problems,
the API calls aren't.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Douglas,
>
[quoted text clipped - 88 lines]
>> >> > objWord.Close
>> >> > Thanks!
Loralee - 26 Nov 2006 15:49 GMT
I'll give that a try-  but it doesn't appear that the call to the common
dialog that is causing the problem.  In fact, the common dialog launches and
takes user's selection as strFilePath.

It is the line GetObject that is highlighted.  Is that not a VBA Function
call?
Signature

Loralee

> Scrap the Common Dialog. Use the API approach demonstrated in
> http://www.mvps.org/access/api/api0001.htm at "The Access Web".
[quoted text clipped - 76 lines]
> >> > objWord.Close
> >> > Thanks!
Loralee - 26 Nov 2006 16:08 GMT
It's probably wise for me to find another way to talk to WORD from ACCESS,
(another was to Mail Merge or similiar) as undoubtedly there will be other
incompatabilities coming.
Signature

Loralee

> I'll give that a try-  but it doesn't appear that the call to the common
> dialog that is causing the problem.  In fact, the common dialog launches and
[quoted text clipped - 83 lines]
> > >> > objWord.Close
> > >> > Thanks!
Douglas J. Steele - 26 Nov 2006 16:56 GMT
Hold on. Since you've started another thread (seldom a good idea when you've
got an active one going), I would suspect that you're running into problems
with your References collection, since you've got users with different
versions of Word.

Try removing the reference you have to Word, and changing

Dim objWord As Word.Document

to

Dim objWord As Object

If you're using any intrinsic Word constants (they'll all start "wd"),
you'll need to get the values of those constants and define them in your
code once you've removed the reference.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> I'll give that a try-  but it doesn't appear that the call to the common
> dialog that is causing the problem.  In fact, the common dialog launches
[quoted text clipped - 87 lines]
>> >> > objWord.Close
>> >> > Thanks!
Loralee - 26 Nov 2006 20:19 GMT
I apologize for the multiple threads!  I'm new to this forum and used to one
that is fragmented.  

So so I understand correctly that I can create an Object for Word, and
totally remove any reference to Word?  Can I expect GetObject to work against
the dimmed Word.Object when it isn't currently working against the dimmed
Word.Document?
I am not referencing any other Word constants anywhere else.  At the moment
both users have Word 2K.  It is likely that my next project will include
users that have Work 2003, so I want to make this work in both arenas if
possible.

Thank you again,

Signature

Loralee

> Hold on. Since you've started another thread (seldom a good idea when you've
> got an active one going), I would suspect that you're running into problems
[quoted text clipped - 104 lines]
> >> >> > objWord.Close
> >> >> > Thanks!
Douglas J. Steele - 26 Nov 2006 22:16 GMT
I'm not sure why it's not working now, unless your References collection is
messed up.

Go into the VB Editor, and select Tools | References from the menu bar. Do
any of the selected references (i.e.: the ones at the top with check marks
beside them) have "MISSING: " in front of their name?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I apologize for the multiple threads!  I'm new to this forum and used to
>one
[quoted text clipped - 132 lines]
>> >> >> > objWord.Close
>> >> >> > Thanks!
Loralee - 27 Nov 2006 02:49 GMT
Yeh, I did check that and nothing was missing.  I even unchecked, then
rechecked Word and got the same result.  I'll change the code to your
suggestion using Object tomorrow when I get into the office and see if that
makes any difference.   I really want to get this back working as it saves me
and my co-worker alot of time.  And I've been planning on using something
similliar in my current project.

Thanks
Signature

Loralee

> I'm not sure why it's not working now, unless your References collection is
> messed up.
[quoted text clipped - 139 lines]
> >> >> >> > objWord.Close
> >> >> >> > Thanks!
Loralee - 29 Nov 2006 04:36 GMT
Well, your suggestion to
"Try removing the reference you have to Word, and changing

Dim objWord As Word.Document

to

Dim objWord As Object ", along with removing the reference to Word worked.  
So we're back running.  I plan to follow up on changing the Common Dialog
Call to an API call (I haven't done an API call yet!) and probably implement
Allen Kallel's suggestion in my current project.

Again, thanks! I appreciate everyone's help.

Signature

Loralee

> Yeh, I did check that and nothing was missing.  I even unchecked, then
> rechecked Word and got the same result.  I'll change the code to your
[quoted text clipped - 148 lines]
> > >> >> >> > objWord.Close
> > >> >> >> > Thanks!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.