Results 1 to 10 of 10
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Fill Access table from Word doc content:must do's? (Access 97)

    After looking at the document, I think I might decline the project. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    But seriously, doesn't this seem like a perfect application for XML? In either case, you could have issues with non-alphanumeric characters and rich text, but the seductive part about XML is that you can define a tailor-made schema independent of the vagaries of a particular data management product, and then use the data management product's import tools to deal with everything else.

    At least that's the theory. I have no idea whether Access or Excel or whatever will like this data.

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

    Re: Fill Access table from Word doc content:must do's? (Access 97)

    FYI - I moved your thread to the Access forum so it would get broader exposure. A couple of folks who frequent these parts have info on their websites that might be of value to you. In particular, Bryan Carbonnell has a sample Access database that demonstrates taking info from a Word fill-in form at http://www3.sympatico.ca/carbonnb/bryan/utilities.html. Also Drew Wutka has some tutorial information on his site (http://www.wolfwares.com) about automation between Access and Word both ways - his site may be offline frequently however, as his apartment was apparently involved in a fire. I am also involved in a similar project at the moment - the client is currently debating the worth of it, so stay tuned.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fill Access table from Word doc content:must do's? (Access 97)

    There isn't any native support for XML in Access 97, but you can use ADO to open a recordset from an XML document and work with the data.
    Charlotte

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

    Re: Fill Access table from Word doc content:must do's? (Access 97)

    I just got a goahead on my project, so the fats in the fire. I'll keep you abreast of the progress.
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Fill Access table from Word doc content:must do's? (Access 97)

    Wendell - thanks!!!! Jscher2000 & Charlotte, thanks for your info too.
    And for all your information: some more background...

    About the process (probably similar to many common administration processes...)
    Before (but mostly at) a certain date, our clients send in files (official documents,...). These are distributed (post+mail) to different departments, which might distribute it further to other persons. Those people evaluate their piece of the cake, making one evaluation for the (reporting of) previous year, one for the (planning of) this year. Herefore, they (should) use template based Word documents which are sent to us by the end of the evaluation period (name = client_pieceofcake_PlanOrReportYearevaluationperio d, e.g. John_Energy_P03E1.doc). We gather the most important pieces' global decisions in Excel, mail merge them (together with a number of derived describing default sentenses) into a global evaluation Word document and print the whole to be sent to the client. This must be done within a period of 3 months. This year, there'll be +- 250 clients * 2 evaluations (planning+reporting) * 10 pieces of the cake = +- 5000 Word documents to be processed, which all tend to come in some days or for some even <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> the day before we need to send the whole evaluation to the clients again. Clients that 'didn't pass' have a number of weeks to send in more information for a second & final evaluation period (identical workflow).

    The Word processing will - hopefully - only have to be done once or twice. We are in the middle of an outsourced database & workflow development project which should offer us, among others, the opportunity to enter the evaluations in a web application (Informix db, Java app). As it has been delayed, however, we continue working like before using good old Word documents (actual evaluation period = april - june).

    But now I have to anticipate on what the future might bring.
    - 'best' (?) scenario: application ready soon, but with not all problems solved yet & just elementary testing done. At that moment, all our users should copy-paste the content they already have entered in Word into the web application, and continue their work on the web app too. This scenario is in doubt... considering the actual progress, the risks and the fact that it's no fun forcing people in such scenario, certainly as they already have a lack of time <img src=/S/nosleep.gif border=0 alt=nosleep width=27 height=15> to finish their job right now. (UPDATE 030426: scenario outdated!)
    - 'worst' (?) scenario: we continue using our Word documents & import them into the database one way or another (probably before second evaluation period). Copy and paste might be an option, but not a very cost effective one nor easy to convince the users... SO here's the issue: if I manage to process the Word files to Access, we might clean & prepare it for conversion into Informix,... saving us a lot of time (?!?) & 'monkey' work (!!)...

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Fill Access table from Word doc content:must do's? (Access 97)

    Charlotte & Jscher2000: I won't be able to work out the XML scenario: I'm not familiar with XML (yet...) & will need probably far too much advice (e.g. for the needed conversion Word => XML, I guess!?) in order to succeed. But thanks anyway.
    Wendell: thanks for keeping me up to date. In the meanwhile, I've made my previous posts a bit more accurate: the example attached to my first post is more informative now + some details in the posted text.
    Hasse

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Fill Access table from Word doc content:must do's? (Access 97)

    <P ID="edit" class=small>(Edited by hasse on 26-Apr-03 21:07. More accurate Word document structure, field description & improved example in attachment.)</P>Hey,

    anticipating on a job that might fall into my hands... Suppose you have 5000 Word documents with all content stored in tables. Suppose you want to write in Access a VBA-procedure to load the content of those Word tables' cells into a 'real' table, what troubles (from bugs to methodological traps,...) might I expect?

    Some more information

    1. Word documents
    (Attachment: empty example. Useful 'data' in blue between <brackets>.)
    These documents are evaluation forms. They have besides contextual header information (client, file, author,...) a global evaluation text and decision up front, followed by named groups of criteria. Each group has a name & decision. Each criterium has a name & decision too, followed by one or more argument(s). The number of arguments may vary.
    The document contains a number of tables, one for each criterium group. The first row has two columns - cells describing the 'main argument' and the decision concerned. The other rows of the table all have four columns: name, decision, argument type (+/0/-) & argument text. When the evaluator fills in more than one argument behind a criterium, the first two cells of these argument row(s) following that criterium will be empty.
    Evaluations have started already. Unfortunately, the formats used don't contain any fields or bookmarks.

    2. Access
    Tables to be filled with the Word documents might be (not normalised yet, for simplicity's sake):
    tblWordDocument = wd_ID, wd_client, wd_file, wd_Author, wd_MainDecision, wd_MainText
    tblArguments = ar_ID, ar_IDwd (referring key), ar_CritGroup, ar_CritGroupDecision, ar_Criterium, ar_CriteriumDecision, ar_Nr, ar_Text

    3. VBA
    Word automation procedure(s) process documents in order to
    (a) fill tblWordDocuments with header info & global decision content
    ([img]/forums/images/smilies/cool.gif[/img] fill tblArguments with the other tables' content
    by scrolling through all tables' cells (row by row, column by column) and while identifying their content, selectively copying their content (range?) to the proper Access table fields.

    I hope this question is clear enough to be asked on this forum. Anyway, thanks for any feedback,

    Hasse

    p.s. Content controll in Word and/or Access & error logging is evident, but for clarity's sake better not discussed here (yet).
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Fill Access table from Word doc content:must do's? (Access 97)

    Hey, just a short update: right now, I'm exploring with a collegue the 'other way round' which might also be an alternative in our context: populating Word-documents from Access. I'll check out Wendell's link-tip about automation between Access and Word both ways (though I didn't find any of such information at wolfares' site immediately) and maybe post with a sample database of my own as well, if I don't find a usefull add-in from elsewhere. (Just wondering if I should do this here or elsewhere (starting a new thread or dig up an suitable old one from the search engine)

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

    Re: Fill Access table from Word doc content:must do's? (Access 97)

    On our project after several weeks of intermittent work, and at a point where we were ready to go live, the client got a new VP who blew the whole thing away and had us start over! Also, it looks like Drew has reworked his site considerably (thanks to the fire, I think he actually had to rebuild it from scratch), and there doesn't seem to be much on automation of Word. Another resource on the topic is the Access Developer's Handbook - it's where we really got started with it. With more Lounge horsepower (maybe that should be WolfmanPower), it's fine to continue this thread.
    Wendell

  10. #10
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Fill Access table from Word doc content:must do's? (Access 97)

    OK, Wendell. Thanks four your reply. I'll check out the Developers Handbooks too (got all three of them on my shelf - for XP instead of for 97 though :-p).
    FYI I don't know yet if I'll work out both ways (Word => Access and/or vice versa). Have to list pro's and con's once. I'll keep you posted.

Posting Permissions

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