Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My users want a field which links to a Word document.
    If there is nothing in the field, they want the DB to start Word and save the filename when they exit.
    If the field is not null, they want to be able to just open the document.
    I need help on how to achieve all this, please.
    By creating a dummy form, adding a command button and getting the Toolbox Controls wizard to guide me through opening Word, I've generated some VBA to look at; this is

    oApp = CreateObject("Word.Application")

    However, when Word opens, firstly the window does not have focus, plus it is 'blank', which means the user has to click twice on the New Document icon (once to give the window focus and the second time to implement the New Document action). So :

    Firstly, I'd like the window to have focus and a new document present by default.
    Second, when they save the document, I need to know how to do get that filename into my DB field.
    Finally, what is the correct type for the field : Hyperlink or OLE Object?

    Sorry for such a woolly question - but I'm pretty stuck.
    Thanks
    Silverback
    Silverback

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Assuming you want to use Late Binding to Word,
    which is oftem good because you do not have to have the Word Libary loaded,
    then the code below will work in Word 2007

    When Word is opened with CreateObject it is normally opened as a Hidden window.
    That is why you need to make it visible.
    If it is a New Doc you ought to be able to use AppActivate to set focus.
    If it opens an existing documenr you would need the document name rather than Document1.

    However once Word is opened, Access has handed control over to word.
    It is no longer monitoring Word for changes.

    You could have code in the Word Template to handle saving and maybe set a flag.
    But not sure quite how.
    Maybe an API that I know nothing about.
    Someone with lot's of Word or other skill may be able to advise.

    As to OLE or Hyperlink.
    Depends if you want to view the document inside Access.
    If not Hyperlink because there are database size implications with OLE Objects.

    Code:
    
    Dim oWD As Object, owDoc As Object
    Set oWD = CreateObject("Word.Application")
    Set owDoc = oWD.Documents.Add
    oWD.Visible = True
    AppActivate "Document1 - Microsoft Word"
    Andrew

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Finally, what is the correct type for the field : Hyperlink or OLE Object?
    I always use a plain text field. I don't store the Word document in the database just the filename or, occasionally, the full path.
    Usually I have a standard folder location for the document collection, which gets added in code to stored filename when I want to open the document.

    This is the same strategy as is normally used for storing images.

    when they save the document, I need to know how to do get that filename into my DB field.
    The code that creates the document could immediately Save the (still empty) document and then write the name back to the database.
    This assumes there is some naming convention you can agree on in advance so the users don't get to just make up a name.

    After they have written the text users still need to save again.

    Here are a couple of lines from a job where I do that stuff.


    Code:
    strSaveName = fnGetProjectProformasPath & "proforma_" & lngYear & "_" & lngProjectID & ".doc"
     wrd.ActiveDocument.SaveAs strSaveName
    Here fnGetProjectProformasPath retrieves the location.

    You could then have:
    Code:
    me.txtfilename = "proforma_" & lngYear & "_" & lngProjectID & ".doc"
    Regards
    John



  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Jolly good idea of Johns.
    But save it before you use AppActivate.
    Andrew

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello Andrew and John
    Thanks for your tips. I have constucted a (very crude) solution to my problem using a combination of Andrew's tips plus some code I plundered from the Northwind DB (the bit behind selecting an employee photo). John assumed a far greater level of skill for me than I have!
    DB is attached in case it's any use to anyone.
    NOTES
    I used a hyperlink to hold the filename.
    For this demo DB, I've used "HistoryNotes" to be the hyperlink display code; in the real DB I'll probably use the actual filename.
    The VBA line Me![txtFilename].Text = "HistoryNotes#" & fileName is the one which manipulates the hyperlink to add the display text; if the actual filename is required, make the line
    Me![txtFilename].Text = fileName
    You will note that the VBA says "with Application.FileDialog (3)" - that's because msoFileDialogFilePicker does not seem to be declared in VBA.
    Also, msoFileDialogSaveAs cannot be used inside Access, which is why it's somewhat crude, viz asking the user to save the document and then making them navigate to the file in order to pick up the filename. See here
    Still, it works.
    After this initial setup, the user can just click on the link to open the document, and it allows them to store the document wherever they want (which might be mixed blessing!).
    Many thanks
    Silverback
    Attached Files Attached Files
    Silverback

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I attach a modified demo that demonstrates the sorts of things I was talking about.

    I had to replace # with _. # seemed to creates problems in the filename.

    [attachment=87926:OpenWordJH.zip]

    Unzip the mdb and 3 word files to the same folder.
    Attached Files Attached Files
    Regards
    John



  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wow - as I said; you assume a level of knowledge that is flattering but incorrect! No wonder I couldn't get it going as you originally suggested.

    Thank you very much for this; I can use this with minimal alteration to meet my particular DB requirements.

    The reason for the # characters is because I declared the field as a hyperlink and this is the format Access uses to hold a hyperlink. Your solution uses a text field (as you suggested in your original reply) but follows it as a hyperlink in the VBA; I would never have found that method.
    I really like the SaveAs line as well.
    All in all - terrific.

    Thank you again
    Silverback
    Silverback

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Glad you found it useful.

    One addition.

    I used CurrentProject.Path as a quick way to get a path. but if you have a split database it will return the location of the Front End, not the Back end.
    Depending on how you db is installed that may be a problem.

    (And if it is not split read this: Why Split a database)
    Regards
    John



Posting Permissions

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