Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Include Word Document in Report (A2k)

    Hello,

    Does anyone know how to combine an existing Word Document with an Access Report <dynamically>?


    I have a report that I created in Access, and would like to include information that is already entered in a Word document, stored on the network.


    I have tried creating both bound & unbound object frames. The unbound works ok, but my problem is that i need to change the name of the report depending on which product is being viewed. I.E. when I create reports for more than one record, I need the report to display different documentation for each product when I scroll through the pages. I have tried doing this programmatically, but it still shows the original report that I used to created the link.

    The code that I've tried is as follows - (both bound/unbound):

    Me.boundPLI.OLETypeAllowed = acOLELinked
    Me.boundPLI.Class = "Word.Document"
    Me.boundPLI.SourceDoc = docPath
    Me.boundPLI.Action = acOLECreateLink

    The code then always errors at the .Action line... i think something to the effect of 'the Action can't be carried out right now'. When I comment out the .Action line, the report opens w/nothing in the Frame. This is also the case when trying this sort of code with an unbound OLE.


    Right now, I've managed to programmatically update a 'temp' word document and just link to that. This works ok for one product, but again, when viewing more than one, the OLE object is only showing the first word information.


    I've also tried using a form as a subreport. The form works fine as a form, updates the OLE fine when scrolling through the records. But, when I view it on the report, the OLE just shows up blank.



    Any ideas would be HUGELY appreciated!!!
    Thanks, Janel

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Include Word Document in Report (A2k)

    It seems to me that the data source for your report may be the issue. If the appropriate Word documents were linked to each product record, that might solve your problem, though there are some issues. Probably the nastiest one is if you've got lots of products - say 1000 or so, your database will balloon in size to about the maximum for a /mdb file.

    There are some other ways to do this kind of thing. One approach is to store the current Word documents as rich text in Access - then you need to put a rich text control on your report. That does however have issues with anything graphical in nature. Another approach is to use Automation (aka OLE Automation), and actually create your report as a Word document - this is a very flexible approach and will let you combine Word documents and Access data and do things that are very difficult to do in Access reports. On the other hand, if you want to present tabular data in your report, doing so in Word tables gets pretty involved when doing it with VBA.

    Hopefully this will stimulate some ideas for you, and prompt comments by others.
    Wendell

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Include Word Document in Report (A2k)

    Wendell,

    Thank you for your reply and suggestions.

    As far as linking, I will have thousands of products, so that is probably out of the question. The documents are named by product number though, which is included in the record source of my report.

    Could you expand on the 'storing in rtf' a little further? What kind of control would that be on the report? I'm still not sure if that would help in my case though. Currently, the word documents are created and updated automatically by a different application and stored as .doc. Would a rtf control update if the document is updated? The documents are all text, so graphics would not be an issue.

    I did try writing the report in Word, but honestly gave up. I have about 8 subreports that would require tables and I think that's beyond my abilities right now, especially if I can accomplish this with Access.

    Thanks,
    Janel

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Include Word Document in Report (A2k)

    The rtf approach utilizes what is called a rich text control, but if the documents are created by another application, you may not need rich text - it is only necessary if you want to bold or italicize words, do cute things with paragraph formatting, or do things like multi-column text (and it doesn't support graphics at all). The data for that sort of thing is stored in memo fields.

    It does sound as if Automation using Word from Access is out of the question - subreports is another one of those things Access does very well, and Word doesn't. So perhaps the approach you are using is really the right one - you should be able to trigger code to change the OLE control data source each time the header is changed by using the OnFormat event for the header - that pre-suposes you have a Grouping by product so the report for each new product starts on a new page. I guess I should ask whether the report will normally print the info for all products each time it is run, or will it typically be printed out for just one or a few products at a time?

    The other issue has to do with how frequently the documents change once they have been created? If it's never or very infrequently, then you might want to just store the actual text, again assuming you don't need any fancy formatting that a text box can't handle. That would involve doing an import of the text from the document once, and then again when a document changes. How do you find out about new documents now, and how do you keep track of where the Word document is stored?
    Wendell

  5. #5
    New Lounger
    Join Date
    Oct 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Include Word Document in Report (A2k)

    -- "you should be able to trigger code to change the OLE control data source each time the header is changed by using the OnFormat event for the header "--

    Yes, I do have grouping. I tried putting the following code in my Group Header OnFormat event as you suggested -

    Me.PLI.SourceDoc = "C:minebook work0020889.doc"
    Me.PLI.OLETypeAllowed = acOLELinked
    Me.PLI.Action = acOLECreateLink

    However, when ran the last line still creates the following error:
    MSAccess can't perform the operation specified in the Action property of the VB procedure you're trying to run.
    I'm creating the OLE by going linking to an existing word doc.

    Ultimately, I would like the document information to print in every report, regardless of whether it's just for one product or all products.


    Your other suggestion may work, if I can just figure out HOW. The documents contain product information, and are updated frequently. But it got me thinking, since it's all text, I should be able to grab the text and store it as a memo field in a table. Again, the ? is how. I tried doing this from the Group Header OnFormat as well. I open the existing doc (all docs are stored in same dir, thank goodness.), copy the text. (done with a myRange.copy command)

    Now, the question is how can I get that into a table field? I tried calling the following function from the onFormat (so it does this w/every product in the report):

    Function setDocText(prodCode As Long)
    On Error GoTo err_setDocText

    DoCmd.OpenForm "frmPLIText", acNormal, , "PROD_CODE = " & prodCode, , acHidden
    [Forms]![frmPLIText]![pliText].SetFocus

    DoCmd.RunCommand acCmdPaste

    DoCmd.Close acForm, "frmPLIText", acSaveYes

    exit_setDocText:
    On Error Resume Next
    Exit Function
    err_setDocText:
    MsgBox "setDocText Error: " & Err.Description, vbCritical, CERROR
    Resume exit_setDocText
    End Function


    This errors on the first line, #2501, The OpenForm action was canceled....I'm assuming you can't open a form from a Report maybe????
    Any suggestions?

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Include Word Document in Report (A2k)

    I think the problem you are having with the first approach has to do with the fact that OLE Object linking is intended to be stored in a field in a table. Do you have an OLE Object field in your data source table <img src=/S/question.gif border=0 alt=question width=15 height=15> My comment about the database bloating in size was based on images, but I'm not postive that Word documents would cause that kind of bloat. To test that, you could create a few links in records and compare the size of your resulting database with what it was before. (Be sure to compact and repair both before and after creating the links so you get a true size increase.) If it turns out that it doesn't bloat with Word documents, your simplest solution would be to create bound OLE Object links for each product. However see my comments below.

    When I made the suggestion about importing the text into Access, I was envisioning a process that you would run just before the report to update any of the Word product data that had changed since the last time an update had been run. Trying to do it as you suggested using the OnFormat event is probably too late - at that point the data for the report has been set, and code is expected to be doing formatting chores, not data updating (this is more or less conjecture on my part I guess - I don't have that in-depth knowledge of the reporting engine code).

    Are the Word documents truly Word, created using Automation, or are they text documents saved with a .doc extension <img src=/S/question.gif border=0 alt=question width=15 height=15>
    If they really are Word documents, you would need to write some DAO code to open the product recordset, and step through it. For each record you would need to open the document in Word, select all the text, and then set the memo field contents to be the selected text. If they are really text documents disguised as Word documents, you might be able to do something with the TransferText command that wouldn't involve automation, but the fact that each product document is a separate file makes the process ugly in any event.

    Concern - my experience with this kind of situation suggests there's lots of grief ahead. For one thing, you have to know the full path name of the file for each product, and you have to know when a brand new product is created, and update your database accordingly. The other problem is when someone decides to intervene and move a file, or your server name changes, or any of a hundred other things that can happen when tracking file names that would break your report. I know it may not be possible, but a hard look at the application that creates the documents to determine if it could be modified to use Access as an Automation Server and update the text in an Access table could simplify your life immensely. Otherwise, you or someone else is going to be stuck with a messy maintenance task on an ongoing basis. Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    Wendell

  7. #7
    New Lounger
    Join Date
    Oct 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Include Word Document in Report (A2k)

    Wendell,

    Thank you for all of your suggestions!!!!!

    I have managed to get the document into my table as an OLE object. I had to open a form that does this before the report is opened and set the OLE object there. I have tried both acOLECreateEmbed and acOLECreateLink in my code, and looks like linking will take up less space. I did try the memo field option, also, which I would really have liked to be able to use, but the formatting gets really messed up. If anyone is interested, I found the information in a MSDN Knowledge Base, (How to Load OLE Objects from a Folder into a Table.


    Now, here's my next question. B/C of the formatting of the original document, I can't simply create my OLE. (That would be too easy!!) When I do, part of the original document is cut off due to it having an hanging indentation. So, I've ended up having to open the document, copy & paste the text that is set to one margin, and then do the same with the rest of the text, only with different font and margin settings.

    This all works ok, but I noticed during my tessting that I get a message if the original document is already currently open. I dont know how much of an issue this will be, but would like to know if there's any way to avoid it. The message is something like 'File in Use - Document is currently being edited and locked by user. Click Notify to be notified when it becomes available.'.' There's more to it, but can't remember...the button options are Read-Only, Notify, Cancel. My automation code works fine as long as Notify or Read-only is selected, but bombs if Cancel is selected. I'm wondering if there's anyway to work around this with code, so the message doesn't even appear (and hence won't confuse the user at all, since they won't even be aware that Word is being opened)...or even how to detect that the document is already opened?

    I dont think that i'll have too much trouble as far as where the documents are stored. That is all done automatically by the program that writes them, so they are always in the same path with the same naming conventions, and I dont even think that the user's are aware where the documents are stored. They access them through the program that created them. About the only think I can think of is if the server name changes, and that would just involve changing a Constant in my code.

    Thank you for all your help!!
    Janel

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Include Word Document in Report (A2k)

    Hi Janel,

    This still sounds like a potential nightmare in the long run. For one thing, if the doucment needs to be reformatted, won't that need to happen every time a document is edited or created? For the number of documents involved, that sounds like a big task.

    The second issue is the error message when someone already has the document open - that message comes from Word, and can't be readily disabled other than by opening the document read-only. Are users likely to be editing the document if they open it from your database? In the long run, the use of Word documents in a shared environment will definitely be an issue.

    As long as the text can be stored in a RTF (Rich Text Format) file and still look OK, you should be able to use a memo field, but you would need a RTF control. Microsoft has one that is available, and there are some ActiveX ones available from companies like FMS Inc. and DBI Technologies. Unfortunately there is a learning curve with using that sort of thing. Sorry this doesn't sound more encouraging - I'll keep rolling it around and see if some other ideas occur.
    Wendell

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Include Word Document in Report (A2k)

    I too have had trouble with using this code, which is posted on the MSDN website here, the error occuring at the now almost legendary point:
    OLEWallChart.Action = acOLECreateLink
    I am using Office 2003 with latest service packs and all references are correct.

    Private Sub PropertyIDHeader_Format(Cancel As Integer, FormatCount As Integer)
    OLEWallChart.Class = "Excel.Workbook" ' Set class name.
    ' Specify type of object.
    OLEWallChart.OLETypeAllowed = acOLELinked
    ' Specify source file.
    OLEWallChart.SourceDoc = "D:French CottagesaaaCoreDocumentsCalendar.xls"
    ' Specify data to create link to.
    OLEWallChart.SourceItem = "LBCALJULY"
    ' Create linked object.
    OLEWallChart.Action = acOLECreateLink << doesn't work
    ' Adjust control size.
    OLEWallChart.SizeMode = acOLESizeZoom

    End Sub

    Any ideas if this actually works ever? Can anything be done to make it work?
    Thanks!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Include Word Document in Report (A2k)

    Have you made sure that the Locked property of OLEWallChart has been set to No, and Enabled to Yes? Otherwise, the acOLECreateLink action is not allowed.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Include Word Document in Report (A2k)

    Sorry, where are these properties located? I am using an Unbound object frame and can't see any such properties in the property sheet.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Include Word Document in Report (A2k)

    In the Data tab of the Properties window. In Access 2002, they're the last two properties in that tab.

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Include Word Document in Report (A2k)

    If you mean the Properties window for the unbound object frame then these properties don't appear in that tab or elsewhere that I can see.
    Maybe MS left them out by mistake?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Include Word Document in Report (A2k)

    The properties do appear in Access 2002 (XP). It seems highly unlikely that they would have been omitted in Access 2003, but I don't have that version, so I can't check.

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Include Word Document in Report (A2k)

    See attached jpeg for relevant page of the property sheet in 2003.
    Also see this page on MSDN - it mentions that the locked property is set to true by default for unbound object frames but DOES NOT mention unbound object frames in the "applies to" list at the bottom of the page. I think they forgot to put this in!
    They walked on the moon this day, 36 years ago but I can't even find a simple little property here on earth!
    What else might be the problem do you think?

Page 1 of 2 12 LastLast

Posting Permissions

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