Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    3 Star Lounger
    Join Date
    Jun 2004
    Location
    Oxford, Oxfordshire, United Kingdom
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    opening Access from Word (2002 SR 3)

    I'm trying to integrate Word with a database in Access. 2 issues:
    a) is it possible somehow, perhaps with a Macro, to create a button in Word that opens a specific form in a specific Access database.
    [img]/forums/images/smilies/cool.gif[/img] is it possible to get an Access Macro and its RunApp command NOT to open a new instance of Word, but rather to find an instance already open if there is one.

    thanks in advance

    Philip

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

    Re: opening Access from Word (2002 SR 3)

    a) You can create a Word macro to open a database with the /x switch to specify an Access macro to run. Assign this Word macro to a button:
    <code>
    Sub OpenDatabase()
    Shell """C:Program FilesMicrosoft OfficeOffice10MSAccess.exe"" ""CatabasesMyDatabase.mdb"" /x MyMacro"
    End Sub
    </code>
    [img]/forums/images/smilies/cool.gif[/img] If the user has Word 2002, RunApp won't start a new instance of Word if it is already running. It will create a new document, though. If you want to display an already open document, you'd have to use VBA code, with GetObject.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2004
    Location
    Oxford, Oxfordshire, United Kingdom
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: opening Access from Word (2002 SR 3)

    Dear Hans

    thanks for this. A) works beautifully. But with [img]/forums/images/smilies/cool.gif[/img] (I don't know enough VBA to do GetObject), I'm still constantly getting a new instance of Word 2002 running from RunApp in Access.

    2 more questions if I may before I go to bed ...

    a) can the Shell command to open Access from within Word be modified so that Access actually takes the focus, as opposed to just staying on the taskbar.
    [img]/forums/images/smilies/cool.gif[/img] can I build a pause into a Word macro so that I can enter data half way through the operation of the macro and then let the macro resume?

    many thanks

    Can I put a pause in a Word Macro, while I enter some new data and

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

    Re: opening Access from Word (2002 SR 3)

    You've probably long since turned in, but hopefully this will still be useful in the morning. The shell command doesn't give you very many options in terms of starting another application, so we usually use what is called Automation (it's also been called OLE Automation and ActiveX in previous incarnations). With Automation, it is possible to manipulate almost the entire Access object model, and you can do the same thing in reverse using Access to control Word. I think you might find our small Automation Tutorial a useful starting point. It references a number of MSKB articles that are available on the subject - however it focuses on automating other applications using Access. <!mskb=296586>Microsoft Knowledge Base Article 296586<!/mskb> gives an example of doing the reverse - starting Access from Word and printing a report. It should give you some clues as to how to work with forms as controlling reports is similar. Hope this helps.
    Wendell

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

    Re: opening Access from Word (2002 SR 3)

    In addition to Wendell's reply:

    a) The Shell instruction has a second argument that specifies the window style. It can be one of the following constants: vbHide, vbNormalFocus, vbMinimized`Focus, vbMaximizedFocus, vbNormalNoFocus, vbMinimizedNoFocus. If you want the application to come to the foreground, use vbNormalFocus or vbMaximizedFocus:

    Shell "...", vbMaximizedFocus

    [img]/forums/images/smilies/cool.gif[/img] There are several examples of using GetObject with Word in the Lounge, e.g. <post#=467259>post 467259</post#>, <post#=467171>post 467171</post#> and <post#=466184>post 466184</post#>. A search will turn up more.

  6. #6
    3 Star Lounger
    Join Date
    Jun 2004
    Location
    Oxford, Oxfordshire, United Kingdom
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: opening Access from Word (2002 SR 3)

    thanks to both, and my apologies for silence--I've had a family bereavement and am only just getting back to this project.

    This is perhaps a question to which the real answer is 'sit down with a book'. But I'm getting compile errors as soon as I try to put in the vbMaximizedFocus argument in the shell line--and it seems to happen no matter what I do. It works fine without the argument. Can you please help me on this. This is the line I have.
    Shell """C:Program FilesMicrosoft OfficeOffice10MSAccess.exe",vbMaximizedFocus" ""Y:Bibliography Filesbibliography.mdb"" /x OpenArticle"

    many thanks in advance

  7. #7
    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: opening Access from Word (2002 SR 3)

    Just judging from the example posted above, it appears you have inserted the window style argument in the middle of something else. Try putting it all the way at the end of the command line (the command line being what one might type in at a command prompt).

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

    Re: opening Access from Word (2002 SR 3)

    Philip,

    My condolences for your bereavement. I wish you strength.

    As Jefferson said, the vbMaximizedFocus ended up in the wrong place. Try

    Shell """C:Program FilesMicrosoft OfficeOffice10MSAccess.exe"" ""Y:Bibliography Filesbibliography.mdb"" /x OpenArticle", vbMaximizedFocus

  9. #9
    3 Star Lounger
    Join Date
    Jun 2004
    Location
    Oxford, Oxfordshire, United Kingdom
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: opening Access from Word (2002 SR 3)

    Me again. I've now been trying to work out some programming solutions, drawing a bit on one of Hans's routines.

    One question, perhaps an obsessive one, the answer to which might make everything a bit more elegant.

    What I have got so far is a two-click method of getting the record from the form of an Access bibliographical database into a Word document in format.

    In Access, I have the following routine:

    Private Sub Command27_Click()
    Dim stDocName As String
    stDocName = "Macro1"
    DoCmd.RunMacro stDocName 'THIS IS AN ACCESS MACRO WHICH ISOLATES THE DISPLAYED RECORD INTO A QUERY
    Dim oWd As Object 'NOW I'M ADAPTING ONE OF HANS'S ROUTINES
    Dim oDoc As Object
    Dim fNotActive As Boolean
    On Error Resume Next
    Set oWd = GetObject(, "Word.Application")
    If oWd Is Nothing Then
    Set oWd = CreateObject("Word.Application")
    fNotActive = True
    End If
    On Error GoTo 0 ' or On Error Goto errorhandler if you have an error handler in this procedure
    Set oDoc = oWd.Documents.Open(Filename:="Y:Bibliography FilesArticle Standard.doc") 'THIS IS A MERGE DOCUMENT WHICH PICKS UP THE ABOVEMENTIONED QUERY
    End Sub


    THIS MUCH GIVES ME AN OPEN PRIMARY MERGE DOCUMENT. I THEN RUN THE FOLLOWING WORD MACRO THAT TRANSFERS THE DATA INTO THE DOCUMENT ON WHICH i HAVE BEEN PREVIOUSLY WORKING, AND CLOSES THE PRIMARY MERGE FILE, UNALTERED.

    Selection.WholeStory
    Selection.Cut
    ActiveDocument.Close SaveChanges:=False
    Selection.PasteAndFormat (wdPasteDefault)

    IS THERE ANY WAY I CAN INTEGRATE THESE TWO? 'SELECTION.WHOLESTORY' AND 'SELECTION.CUT' ARE WORD MACRO COMMANDS THAT ACCESS DOESN'T RECOGNISE. CAN THEY BE REFORMULATED IN SUCH A WAY THAT ACCESS CAN DO THE JOB?

    many thanks for whatever is possible

    Philip

  10. #10
    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: opening Access from Word (2002 SR 3)

    In Word, the Selection object is relative to the ActiveWindow object, which is relative to the ActiveDocument (or a particular document) object. So... if the selection you want to manipulate is in oDoc, you could use:

    With oDoc.ActiveWindow.Selection
    .WholeStory
    .Cut
    End with
    oDoc.Close SaveChanges:=False

    Does that work???

  11. #11
    3 Star Lounger
    Join Date
    Jun 2004
    Location
    Oxford, Oxfordshire, United Kingdom
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: opening Access from Word (2002 SR 3)

    it does--it works beautifully. Now all I have to do is press control-V to get the merged version off the clipboard.

    Is there anything that can be done to automate that last move? I've experimented with

    Set oWd = GetObject(, "Word.Application")
    Dim pDoc As Object
    With pDoc.PasteAndFormat(wdPasteDefault)
    End With

    in other words, I'm running GetObject again, hoping it will pick up the document in which I have landed, and trying to name that document pDoc. But the macro as I've written it returns an error in the 'With pDoc.PasteAndFormat' line.

    many thanks indeed

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

    Re: opening Access from Word (2002 SR 3)

    You don't set pDoc and you don't use With ... End With correctly. Where does this code run, in Access or in Word?

  13. #13
    3 Star Lounger
    Join Date
    Jun 2004
    Location
    Oxford, Oxfordshire, United Kingdom
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: opening Access from Word (2002 SR 3)

    thanks--I rather thought that was the case ...

    This code is meant to be running in Access--what I'm needing is something which takes formatted text from the clipboard and places it in an already open Word document.

    best

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

    Re: opening Access from Word (2002 SR 3)

    There are probably more straightforward ways of doing this, either by using ADO to retrieve the data in Word without starting Access, or by using Automation as suggested by Wendell in <post#=487937>post 487937</post#>.

    In the code you are using now, you have already created a Word.Application object, so there should be no need to create yet another one. Where are you pasting the text from the clipboard? In a document opened/created from Access, or in a document that was already open in Word?

  15. #15
    3 Star Lounger
    Join Date
    Jun 2004
    Location
    Oxford, Oxfordshire, United Kingdom
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: opening Access from Word (2002 SR 3)

    Thanks again. I've had a look at Wendell's material once more, but still can't quite see what to do.

    The idea of the procedure is this:
    I'm starting it from a Word document, typically an academic paper that I'm writing.
    I use a shell command to open the Access database (YBibliographical Filesbibliography.mdb) on a particular form
    From a button on that form, I run this code:

    Dim stDocName As String

    stDocName = "Macro1"
    DoCmd.RunMacro stDocName THIS GENERATES THE QUERY WITH WHICH THE WORD DOCUMENT OPENED BELOW IS MERGED

    Dim oWd As Object
    Dim oDoc As Object
    Dim fNotActive As Boolean
    On Error Resume Next
    Set oWd = GetObject(, "Word.Application")
    If oWd Is Nothing Then
    Set oWd = CreateObject("Word.Application")
    fNotActive = True
    End If
    On Error GoTo 0 ' or On Error Goto errorhandler if you have an error handler in this procedure
    Set oDoc = oWd.Documents.Open(Filename:="Y:Bibliography FilesArticle Standard.doc")
    With oDoc.ActiveWindow.Selection
    .WholeStory
    .Cut
    End With
    oDoc.Close SaveChanges:=False
    End Sub

    This at present lands me back in the document with which I started--what I need to do manually at present is to paste in the contents of the clipboard (a nicely formatted bibliographical reference)
    The question is: can the paste operation be done with another line or lines of Access code?

    many thanks for your patience with my obsessiveness

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
  •