Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81

    Unanswered: Using Access Report/chart in MS Word Document

    I have a bunch of data in Access that I report on regularly traditionally we have taken this into Excel to make charts and then put the charts into Word Documents (reports to client).

    I am trying to figure out if I can bring an Access Report into my Word Document. I don't need it to be linked, or able to be refreshed, just want to be able to run the report and easily put it, cut ane paste or whatever into Word without going through Excel.

    This especially applies to charts and graphs. For tables I can run a query and cut and paste that straight into Word. Still have to sort out the fonts and column widths, etc. but it saves going via Excel. Running Access 2000 format mdbs with a copy of Access 2002 available, on Win98 and win2000. Word 2000 only.

    Thanks for any advice.
    Mark

  2. #2
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81

    Arrow

    Does no one use data from Access in MS Word? Amazing.... :-) :-)

  3. #3
    Join Date
    Dec 2003
    Posts
    172
    You want to use automation.

    With automation, Access will be able to run Microsoft Word remotely and with the rich library of commands available in Word, you can do almost anything from Access, including passing data from tables and queries right into Word.

    I frequently use Word for Access reporting and printing for several reasons:

    1. Word has a powerful set of commands for formatting and layout.
    2. It is by its very design made for printing all kinds of things.
    3. The command set is easy to master.
    4. Word lets you create templates that can be stylized and used over and over again.
    5. You have many ways to insert the data from Access into Word. For example, you can use:
    a) Bookmarks/TypeText
    b) MailMerge (and other fields)
    c) Document variables
    d) OLE links to charts, images or other objects

    For these reasons, I suggest creating a Word report and then brining the data into it from Access directly.

    I can help you with some sample code and steps but maybe you could tell me a little more about the data you are placing in Word.

    JoeG

  4. #4
    Join Date
    Dec 2003
    Posts
    172
    here's a very basic example. to run it, first place the report demo word template in the root of your c drive, c:\ (of course you can change the code to read any folder)

    open the mdb

    open module1

    you will see the sample code to print the report from an access table, tblContacts, into a Word document

    place your mouse cursor anywhere in this procedure, press F5 to run the demo

    the sample will open word, open a new blank document, based on the report demo template, type the first and last names from the contacts table into the Word document you just opened until end of file

    print the document

    close the document (no changes will be saved)

    close word


    JoeG
    Attached Files Attached Files

  5. #5
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81
    JoeG

    Thanks for your response. I appreciate it. I've been caught up with resolving emergency issues, hence the late reply.

    Most of what I need is to be able to put charts into the a Word file. We prefer cut and paste, not a live link, since sometimes you have to go back and reprint the report from however many months ago (and the links would all update & mess it up). I also need to stay away from VBA as much as possible, partly I am squeezed for time to learn but also I have to pass these things off for others to do, people who can work with some macros but not ready to work with VBA.

    I had hoped there was a way to cut and paste an Access object, just like you can grab an Excel chart and paste it into Word.

    Can you suggest any books that might help me get started learning some of these features of word you mention (1-5) in your first message?

    ALso I was unable to get the demo to work: error message "Your MS Access database or project contains a missing or broken reference to the file msword.olb version 8.3"

    Thanks,
    Mark

  6. #6
    Join Date
    Dec 2003
    Posts
    172
    Hi Mark,

    "Most of what I need is to be able to put charts into the a Word file. We prefer cut and paste, not a live link, since sometimes you have to go back and reprint the report from however many months ago (and the links would all update & mess it up). I also need to stay away from VBA as much as possible, partly I am squeezed for time to learn but also I have to pass these things off for others to do, people who can work with some macros but not ready to work with VBA."

    On the charts-in-Word issue, you might consider two items related to links. First, you could cut and paste the Excel charts into Word as images. This way, they would never change.

    As OLE objects, you have the option in Word to not update the links when the document opens. (And this option can be set by some VBA code automatically.) I can provide the code for you.

    As far as staying away from VBA entirely, I don't recommend it for lots of reasons. First, the learning curve is not as bad as you may think. The power of VBA way overshadows doing things manually. And as you have found, cutting and pasting can be time consuming and has its own issues.

    "I had hoped there was a way to cut and paste an Access object, just like you can grab an Excel chart and paste it into Word."

    Yes, there is a clipboard programming object but again these are VBA methods - not manual cut and paste methods. Access' objects are self contained and a little different from say, a Word document's text or pictures.

    "Can you suggest any books that might help me get started learning some of these features of word you mention (1-5) in your first message?"

    Yes lots of recommendations. Stan Getz (among other co-authors) makes an excellent Access Developer book series published by Sybex. There are also excellent books published by Que Publishing for Access developers and Access users (such as the Platinum Edition/Special Editions of Using Access). And the Microsoft Press books are great also. Sometimes you will find bookstores sell the Microsoft book sets individually.

    I would also try: http://www.google.com/microsoft
    This is a fully indexed set of all types of articles on Access including tips & tricks, programming articles, how-to articles and articles on what to do when things go wrong.

    And don't forget to post here on dbForums! You will find a lot of answers here (as well as a reality check sometimes as to what works and what doesn't work

    "Also I was unable to get the demo to work: error message Your MS Access database or project contains a missing or broken reference to the file msword.olb version 8.3"

    Open the database. Do Alt-F11 to go into VBA. Go to tools references on the toolbar. Look for any references that are checked but say missing next to them. You may need to uncheck the missing reference and then scroll down the list and find the proper reference and check that one instead. For example if you are missing 8.3 you might need to scroll down and find 9.0 or 10.0.

    If you have 8.3 you may be using an older copy of Microsoft Office - maybe Office 97/Access 97? It could also be that you don't have the required files installed on your computer.

    I'm available by chat or phone if you are still stuck and want to ask me a few questions. Just send me a private message if you want to do this.

    Good luck.

    Joe G

  7. #7
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81
    On the charts-in-Word issue, you might consider two items related to links. First, you could cut and paste the Excel charts into Word as images. This way, they would never change.
    This is pretty much what has been happening, and other than resizing font problems the cut/paste part worked pretty well. Since the data is mostly kept in Access ideally would remove the extra step Access > Excel > Word needed to get a chart into the Word document.

    As OLE objects, you have the option in Word to not update the links when the document opens.
    Yes ... unfortunately not everyone who needs to edit theise documents understands the concept well enough to be relied on to click no consistently.

    As far as staying away from VBA entirely, I don't recommend it for lots of reasons. First, the learning curve is not as bad as you may think. The power of VBA way overshadows doing things manually. And as you have found, cutting and pasting can be time consuming and has its own issues.
    I agree, in theory. I am using/teaching myself some VBA.
    However, for things where crosstraining is necessary and procedures change occasionally I wouldn't be able to delegate the work. They can edit most queries/macros for example but even then a portion of the changes come back to me (ie passthrough & crosstab queries) because the database schema is too difficult, etc. The only other staff are program staff, not technical/reportinig staff, so motivation is low for them to learn and since the company isn't paying for books much less classes beyond "intro to word" type ..... ugg ;(

    It is helpful to know there are no manual clipboard for Access just the programming clipboard object.

    For this project I've decided to use a pivot tabel in Excel (via ODBC) to pull in my data from Access. I can then cut and paste the charts into Word. Tables can come straight from Access or possibly through an Excel pivot table. This project had previously been done in a patchwork using older versions of Access, Crystal Reports and Excel -- all then going into Word. There was some problem with the server and old version of Access (97, and some with 2000) that kept the person who originally set this up from using an ODBC link. Support from IS is not always efficient partly due to corporate bureaucracy that is more concerned with whether you broke the rules than with solving the problemt, and limited staffing. Which all means there is a lot for me to clean up. I obviously can't clean up as good as I'd like if there is to be some hope of approximating deadline! We have a backlog of about 1.5 - 2 years of software development needed here so things won't be tidy for a while yet. :|

    Thanks for your help! I'll try to pass the favor on to someone else. A forum like this is great and it has saved me hours and hours of time. I still find it hard to believe that for a long time the company would not give me internet access because I had no business need for it! The much faster rate of advances and efficiencies, and savings I have been able to bring the company since that changed speak for themselves.
    Last edited by mdr02125; 01-30-04 at 19:19.

  8. #8
    Join Date
    Oct 2002
    Posts
    88

    Copying Charts into Word

    I'm surprised there isn't an easy way to do this to. I'd like to learn more of the copy from clipboard VBA method. I could see the code in the example but I had an error when I pressed F5

    I'm doing it using Snagit to select the area of the screen and then pasting this it.

    It works but there are limitations. I have to have the chart maximized to get good image quality and when I resize the picture the font quality and readabilty degrades. I can save the picture as a variety of formats and havn't really played around with that to see if that would help the resizing quality issue.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •