Unanswered: Preview MS Word documents in MS Access
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.
... 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.
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.
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
For AllenBrownes function you make a table in the DB called Files and give it 3 fields as in
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
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...
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