Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Word/Excel FUNCTION communication (WordXP/ExcelXP)

    ref Woody's Lounge www.wopr.com; <post#=214736>post 214736</post#> ; Hans Vogelaar

    Two files are presented, a Word document and an Excel Workbook.
    Each file contains a simple function that provides evidence of receiving a parameter, operating on it, and returning a result.
    Each file contains a function that communicates with its partner application; thus Word can call the Excel function, and Excel can call the Word function.

    This is NOT the only way to communicate data between applications, but it provides a starting point for communication. I am currently exploring the possibilities of using this, or a similar method, to have Lotus Notes (to name but one example) communicate with a Microsoft Word end-user application.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word/Excel FUNCTION communication (WordXP/Exce

    Here's an essay that sees Word communicate with PowerPoint; same basic idea. Results can be obtained through CustomDocumentProperties. It works.

    I'm puzzled that the array method (cloned from the PPT help files) appears to be successful in passing two data values TOWARDS the PPT function, but appear to be unsuccessful in retrieving the third, result element FROM the PPT function.

    Anyone care to tell me why it ought not be possible?

    Clearly I can define an array in Word.
    Clearly I am passing that array to PPT.
    Clearly PPT is setting the third element with the result (it's what I load into CustomDocumentProperties).
    That third element setting appears NOT to be visible on my return to the (calling) Word.

    Module Code in "Document1.doc"<pre>''' Needs a REFERENCE to PowerPoint 10.0 Object Library
    Sub Post_PPT_214736()
    ' ref Woodys Lounge www.wopr.com; Post: 62785; Dave Hill
    Dim pptApp As New PowerPoint.Application
    Dim PPTWorkbook As PowerPoint.Presentation
    Dim PPTWorksheet As PowerPoint.slide
    ' Do things in Powerpoint
    pptApp.Visible = msoTrue
    Set PPTWorkbook = pptApp.Presentations.Open("C:TempPresentation1.PPT ")
    ' Execute a function
    Dim x(0 To 2)
    x(0) = Now
    x(1) = 500
    pptApp.Run PPTWorkbook.Name & "!WdXl.dtPPTAdvanceDateX", x
    MsgBox "result is " & x(2)
    MsgBox """Result"" is " & PPTWorkbook.CustomDocumentProperties("Result")
    ' Clean up
    PPTWorkbook.Close
    pptApp.Quit
    '
    Set PPTWorksheet = Nothing
    Set PPTWorkbook = Nothing
    Set pptApp = Nothing
    '
    End Sub</pre>

    Module Code in "Presentation1.PPT"<pre>Public Function dtPPTAdvanceDateX(x())
    ' Return the given date advanced by a number of days.
    x(LBound(x) + 2) = x(LBound(x)) + x(LBound(x) + 1)
    Application.ActivePresentation.CustomDocumentPrope rties("Result") = x(LBound(x) + 2)
    End Function
    Public Sub TESTdtPPTAdvanceDateX()
    Dim x(0 To 2)
    x(0) = Now
    x(1) = 21
    Call dtPPTAdvanceDateX(x)
    ' MsgBox "dtPPTAdvanceDateX " & x(2)
    End Sub</pre>


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

    Re: Word/Excel FUNCTION communication (WordXP/Exce

    Apparently, arguments to (application objext).Run are always ByVal when the application object is not the application running the code. This is not just for array arguments.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word/Excel FUNCTION communication (WordXP/Exce

    Agreed. Since in principle any VBA application can modify and return a value to any other VBA application, the limitation must be inspired by a real or imagined fear of the general principles of inter-application communication.

    That is, if my VBA application can interrogate a data descriptor to obtain the value, it ought to be able to interrogate the descriptor to replace the value (especially for the common or garden-variety of data such as Long, String etc). "Here, the value of the Long is seven", "OK. Please make it Eight". No big deal in terms of VBA talking to VBA.

    Presumably the designers weren't sure that application in general, outside of VBA, could cope with this, so excluded it from all inter-application communication. It's a pity.

  5. #5
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Word/Excel FUNCTION communication (WordXP/Exce

    I don't know from PowerPoint, but I'm not getting your result when I call an Access function from Word:
    <pre>Sub CallAccessFunction()

    Dim objAcc As New Access.Application
    Dim aX(1 To 3) As String

    aX(2) = "old string"

    objAcc.OpenCurrentDatabase "CDBTester.mdb"
    objAcc.Run "ChangeArray4Word", aX

    Debug.Print aX(2) 'This prints "new string".

    objAcc.Quit
    Set objAcc = Nothing

    End Sub

    'Procedure in Tester.mdb:
    Sub ChangeArray4Word(astrX() As String)

    astrX(2) = "new string"

    End Sub</pre>

    Also: If PowerPoint, unlike (apparently) Access, is somehow stuck in ByVal mode, you may get an effectively ByRef result if you pass a Dictionary rather than an array. Based on a little experimentation, it appears that passing a Dictionary ByVal doesn't prevent the individual entries from being changed.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word/Excel FUNCTION communication (WordXP/Exce

    Steve, thanks for the reply. I've continued to work on my scheme while warily peeking over my right shoulder to make sure that your Dictionaries don't creep up on me (grin!).

    I've assembled a four-by-four suite of Master/Slave projects and uploaded them to my web site (the ZIP file is >100KB) 20050503.zip.

    I demo this tomorrow at the client site. (later: I just 'phoned the client and discovered that today is Tuesday, not Wednesday, so it's a demo the day after tomorrow (signed) "World's Biggest Nerd")

    After which I want to revise my process by adapting your suggestion of Dictionaries to the business of passing and returning values.

    For anyone vaguely interested, I've attached a short readme.txt to this posting.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word/Excel FUNCTION communication (WordXP/Exce

    Steve, you are a cool genius.

    http://www.chrisgreaves.com/download/20050504.zip Contains an upgraded version of 20050503.zip.

    I have added to each of the 4 slave projects a function dtDictionary, which uses a 3-element dictionary for arguments, returning the result of adding the first two arguments in the third argument.

    I have added to each of the four master projects a set of four calls to each of the new slaves. New code will be found at the foot of each module.

    DICTIONARIES RULE!

Posting Permissions

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