Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old March 8th, 2004, 06:50 PM
Authorized User
 
Join Date: Feb 2004
Location: Edwards AFB, California, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default Manipulating Word from MSAccess 2002

I am exporting data via module to a word doc.
Mailmerge would be the better word for it.
Sometimes it works and times it doesn't.

Function oReportWord()
    Dim db As Object
    Set db = CurrentDb()
    Dim wrdApp As Word.APPLICATION
    Dim wrdDoc As Word.Document
    Dim wrdMailMerge As Word.MailMerge
    ' Create an instance of Word and make it visible

    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True
    Const DocPath = "C:\DATA\Report\"

    On Error GoTo oReport_Err

''Data Source is "MyoReprts.mdb!QUERY qryoExport
    Documents.Open FileName:=(DocPath & "oData.doc")
    With ActiveDocument.MailMerge
        With .DataSource
        End With
        .Execute Pause:=False
       End With
    ActiveDocument.SaveAs FileName:=(DocPath & "oData2.doc")ActiveDocument.CLOSE
ActiveDocument.CLOSE SaveChanges:=wdDoNotSaveChanges
    wrdApp.QUIT SaveChanges:=wdDoNotSaveChanges
  MsgBox "Report was Downloaded Successfully"
      Set wrdApp = Nothing
oReport_Exit:
        Exit Function

oReport_Err:
    MsgBox Error$
    Resume oReport_Exit
End Function

Errors is as follows:
Can't merge document is not open.
Remote server is unavailable or ..........
Helpppppp

In matters of style, swim with the current. In matters of principle, stand like a rock. Thomas Jefferson

-------\\\|///
--------(oo)
--oOOo-(_)-oOOo--
__________________
In matters of style, swim with the current. In matters of principle, stand like a rock. Thomas Jefferson

-------\\\\\\|///
--------(oo)
--oOOo-(_)-oOOo--
  #2 (permalink)  
Old March 8th, 2004, 11:54 PM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Have a look at Helen Feddema's site:

http://www.helenfeddema.com/CodeSamples.htm

Among the samples are Access >>> Word automation code. One of the sample files (#24) shows four ways of merging data from Access to Word. No's 2, 3 and 9 are relevant as well.

Being unable to get a remote server (RPC server I believe in Office 97) usually is a sign that a procedure ended while failing to close a reference to an automation you set. It may also be a sign of an unavailable reference but in that case, the failure is not intermittent but consistent, at least in so far as the reference is available or not set on a particular machine.

I note you have dimensioned Word.Document and Word.MailMerge variables but have not used either. They are unnecessary with the code you have written. If you use them, destroy them in reverse order of 'Set'ting them.

Your error handler reads:

      Set wrdApp = Nothing
oReport_Exit:
        Exit Function

whereas it should read:

oReport_Exit:
      On Error Resume Next
      'Set wrdDoc = Nothing 'if you use it
      Set wrdApp = Nothing
        Exit Function

On the happening of an error, the error handler executes the way you have it written and the wrdApp object is not properly closed out. This could be the cause of the Remote server being unavailable. If you shut down Access and restart it, your code should probably create Word correctly at least the first time if this is the case, but not subsequent times.

Hardcoding the file names is a suspect practice. You'd do better to create a new doc from a template each time and then use the Dir function to determine if the file already exists in that location, and either kill it if it does, or change the name by incrementing a counter or appending a date/time as a suffix to the name or something of that nature. I'm not sure about wdDoNotSaveChanges being used for both the document and the application, particularly as you've previously saved changes but making sure you destroy all objects you create should prevent the remote server availablity error, assuming of course your reference to Word objects is valid.


Ciao
J├╝rgen Welz
Edmonton AB Canada
jwelz@hotmail.com
  #3 (permalink)  
Old March 9th, 2004, 12:38 PM
Registered User
 
Join Date: Mar 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Where did "oData.doc" come from? The application has been dimmed, and the DocPath is set, but how were you able to open oData.doc? Does it already exist, and, if not, is this the cause of the error?

  #4 (permalink)  
Old March 9th, 2004, 01:34 PM
Authorized User
 
Join Date: Feb 2004
Location: Edwards AFB, California, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I will do this, but I am getting this message at the start of the process, when MSAccess opens the document. The document opens then it stops and I receive this message: [This command is not available because no document is open]. after I close the document and try again, I receive this message: [The remote server machine does not exist or is unavailable]. Then I close the document then MSAccess to restart the process, then the process runs.

In matters of style, swim with the current. In matters of principle, stand like a rock. Thomas Jefferson

-------\\\|///
--------(oo)
--oOOo-(_)-oOOo--
  #5 (permalink)  
Old March 9th, 2004, 01:51 PM
Authorized User
 
Join Date: Feb 2004
Location: Edwards AFB, California, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your response,

oData.doc is a pre-existing link document located on one of my drives. This document has a form already formatted. The code merges data into this document. The document path is ("C:\DATA\Reports\oData.doc")

Const DocPath = "C:\DATA\Report\"
    'Data Source is "MyoReprts.mdb!QUERY qryoExport
    Documents.Open FileName:=(DocPath & "oData.doc")


In matters of style, swim with the current. In matters of principle, stand like a rock. Thomas Jefferson

-------\\\|///
--------(oo)
--oOOo-(_)-oOOo--


Similar Threads
Thread Thread Starter Forum Replies Last Post
Manipulating strings dzisaacs XSLT 2 December 27th, 2005 10:52 AM
Manipulating Processes ? kush28 C# 1 October 26th, 2005 04:49 AM
Manipulating DataColumn BSkelding VB.NET 2002/2003 Basics 1 January 28th, 2005 12:11 PM
Manipulating Windows bjackman Access VBA 1 November 19th, 2003 02:26 PM
Manipulating data in ASP sri2004 Classic ASP Databases 0 June 17th, 2003 07:19 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.