Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2009
    Location
    Switzerland
    Posts
    4

    Unanswered: Preview MS Word documents in MS Access

    Hello,

    I'm new in this forum and relatively a newbie in MS Access as well. I know how to create queries, forms and reports but have not yet gotten into macros, modules and VBA. The database I inherited is using MS Access 2000. Now this is my problem:

    What I have:

    (1) Thousands of MS Word documents, each of which contain a 3-page report on one client. The filename is just an automatically generated one and gives no hint on the client name.
    (2) An Excel file (which I know I can import to Access as a table) with client names and filenames of the corresponding Word documents.

    What I would like to do:

    Create a form which shows the client name with a preview of the relevant Word document. I would like to show the Word document in Access the Access form (without opening the Word program), such that when I click on the next record, it shows the next (corresponding) Word document. The ultimate goal is to enable the user to quickly read through each Word document to check if there's any relevant information within the text. The user should also be able to print out the document when necessary.

    What I have already tried:

    (1) I have tried the HYPERLINK() function in Excel. It works for a few Word documents but for thousands, the user can't really quickly read through because every click opens the Word program, and since the documents were saved in 200% view, the user has to resize the view everytime.

    (2) I have tested ready-made programs like WordPlus+ and FileStream Turbo Browser but I can not view the Word documents ordered by client name since these programs can browse only using filenames.

    (3) In Access, I have tried putting an OLE Object Field on the form but it shows only the first page of the Word document. I didn't find a way to install scrollbars either. The user needs to see all three pages.

    (4) I have searched for an answer in this site but my eyeballs are almost popping out now and I still don't have an answer. Maybe I'm just getting blind so I'm starting a new thread at the risk of being flamed.

    My hope:

    ... is that you could help me. If you think MS Access is not the answer to my problem or if you know another off-the-rack program which fulfills our requirements, please let me know. Any help would really be appreciated.

    Thank you and best regards,
    Penelope

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're not going to get flamed here

    I wish I knew the answer. I would have suggested number 3. Perhaps there is another type of OLE object you can get hold of that might let you scroll the pages?

    I'll be interested to see how you get on, if you figure it out, please let us know
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    If you are happy look at the content of the Word docs but without the formatting, for example, any bold type would be lost, centred headings would be the lost then you can do the following: (but you will be able to print with the original formatting:

    1) I have attached a DB with just a module and that is AllenBrowne's function for ListFiles. This will allow you to get all the file/path name into an Access table or tables

    2) A tabular form or forms is made that has one of these tables as its record source.

    3) A table is made that has a memo field and perhaps a couple of fields for and ID system

    4) You make some code that opens the Word doc to match the file name in the tabular form, the code does SelectAll/Copy, it opens a form at a new record for the table with the memo field and pastes into the memo field.

    5) The code to open Word etc is placed in module. Make a simple macro with RunCode action for the function and the second action line being GoToNextRrecord

    6) Make another macro with RunMacro action to run the above the macro and run it for either a specified count or condituions that stop it.

    The macro with RunMacro is placed behind a textbox on the tabular form.

    Remember that every record in the table behind the tabular form will have an entry for file/path name by using AllenBrowne's function. The runmacro will go down the list and open each Word doc and paste the contents to a memo field.

    You would probably also include a couple of Setvalue actions so as to stick what ever data is required into each record that has the memo field. Could be the file/path name for example. These actions would code on the macro that runs the function to open Word etc.

    When all is done you can display the memo field as big as you like and have scroll bars.

    Although the memo field loses the formatting you only need that for the printing. However, that is not a problem because on the same record you will have a field with the file/path name of the Word doc. Remember the Setvalue actions above. A button will open and print the Word doc and close as is required.

    The memo field table will get quite big but will be OK. My own personal data base has such a table with just over 2000 record and no problem. I basically use this system myself for my own correspondence, except it is general done one at a time as a Word doc is produced. This way all my correspone is within the DB and hence backed up every time the DB is backed up. It also means My correspondence does not depend on sticking pilies of Word docs on a disk to travel.
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    PS,

    I forgot something. You said file name has no client ID etc so I assume the Word docs are addressed to someone. Once in the memo fiedl you could experiment with Left() and Mid() to extract the early part of each document. If you do this and experiment make sure you look at the results in a form because looking at in a query can be very misleading.

    With a bit of experiment with Left(), Mid() and Len(0 you might be able to class the documents by how many characters before the client's name starts and then Mid() would be the go

  5. #5
    Join Date
    Jan 2009
    Location
    Switzerland
    Posts
    4
    Thank you, StarTrekker, for the reassurance.

    And thank you, Mike375, for a suggested solution. It looks more complicated than what I have ever done in MS Access so far , but I will give it a go and let you know.

    The saga continues...

    Penelope

  6. #6
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    It is not that hard and it will do what you want.

    For AllenBrownes function you make a table in the DB called Files and give it 3 fields as in

    FileID Autonumber
    FName Text 250 characters
    FPath Text 250 characters.

    The are mianly two things you would need to search on an learn.

    1) How to open Word from Access and once open how to copy the contents of the Word doc, just like SelectAll/Copy and of course have it close Word an dhave it open a new record for an Access form then paste.

    2) How to dynamically use entries in an Access textbox to get file names and paths

    For exampe this runs AllenBrowne's function

    Call ListFilesToTable("c:\" & Format([Forms]![Form1]![Text1]))

    What that is doing is getting the file/path as it is listed in [Text1]

    Similar is

    ("C:\StoreLetters\" & ([Forms]![PrintAndClose]![LNameNoDoc]) + ".doc")

    That is part of coe to open Word from Access and that part is about which Word doc and it is using what is in [LNameNoDoc] which would would be different for each record.

    If it was

    ("C:\StoreLetters\ClientLetter.doc") then the ClientLetter part would need to be changed for every file.

    You need the dynamic system so as to utilise AllenBrown'es funtion filling a table with all your file names.

  7. #7
    Join Date
    Jan 2009
    Location
    Switzerland
    Posts
    4
    Thanks, Mike! You're right. I was able to run this Allen Browne function. Great! Now I have to figure out the rest of your suggestions. This may take a while, since I work only part-time and there are other things on my plate. Patience, s'il vous plaît...

    Pen

  8. #8
    Join Date
    Jan 2009
    Location
    Switzerland
    Posts
    4
    Hmm... this may be a clue to what I need:

    Embedding Microsoft Word as a control into Desktop Forms - Rick Strahl's Web Log

    I just need to figure out how to make it work exactly as I want it.

  9. #9
    Join Date
    Aug 2011
    Posts
    1

    Code to OPen Doc and copy text

    Hi Mike375

    I know this post is going back a while but I've only just stumbled on it.

    Do you have an example of your point 4

    4) You make some code that opens the Word doc to match the file name in the tabular form, the code does SelectAll/Copy, it opens a form at a new record for the table with the memo field and pastes into the memo field
    Would be much appreciated

    Regards
    SmallTime

Posting Permissions

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