Results 1 to 11 of 11
  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

    Call XLA from PPT? (Office97/SR2)

    So here I am with a lovely XLA full of chart-manipualtion procedures (formatting chart and plot areas, titles, axes labels etc etc etc.), and it would be simply luvverly to make use of them from PPT.

    (In PPT/VBE Tools) References, Browse, AllFiles doesn't let me hook up.

    Given PPT's weird behaviour, it's probably not doable - pity - but just in case .... has anyone achieved this?

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Call XLA from PPT? (Office97/SR2)

    Hi,Chris. Without knowing what the purpose of the XLA is, I don't think we'd be able to help much. You *should* be able to call a macro from the XLA from PowerPoint, if that's what you want, but I don't understand what you'd want to do with an Excel macro in a PPT slide? Perhaps you want to manipulate a chart in some way? Then why not manipulate it BEFORE it hits Powerpoint? Or are you using MS Chart and want to use Excel macros on it?

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

    Re: Call XLA from PPT? (Office97/SR2)

    Thanks for the response. It's not macros I want to call; it's Functions.

    Consider a library of code to manipulate (in this case) chart objects. Change the appearance of charts, rescale charts, format the text contents and so on. In Excel I can assemble applications to generate molecular bubble charts NOT ONLY from Excel cell ranges, but also from arrays or CSV files. Very useful stuff.

    Now in PPT/VBA I develop a chart and think "I'd like to format the Title string. I recall that I did just this in Excel, and the utility code is sitting as a function in an XLA library". At this point, in PPT/VBA, I'd like to establish a reference to that XLA and call the function, without having to transport the source code into a PPT module (with the attendant problem of maintaining dual copies of the source code).

    Or else, still in PPT/VBA I'd like to build a chart from data in a CSV file; again, there's a nifty procedure sitting fully-tested in an XLA file - if only I could get at it!


    Charting in PPT makes a good example beacuse charts occur in both Excel and PowerPoint, but I could as easily be interested in referring to string/text manipulation functions in a Word/VBA utility template.

    It seems a shame to reinvent the wheel in PPT/VBA when I have such a quantity of tested utility functions in Utils.XLA and Utils.DOT.

    Again, think not in terms of macros (which to me are end-user applications), but of utility functions, with arguments, returning results, and greatly expanding the power of MSoffice and the speed of application development.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Call XLA from PPT? (Office97/SR2)

    I'm not familiar much with the experts here at Woody's anymore, so I'm going to contact Tushar Mehta and see if he can give you a hand. You might want to check out some of the tools at his site: Tushar's Site

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Call XLA from PPT? (Office97/SR2)

    At least two ways:

    1. Compile the functions into a separate DLL using VB 6.
    2. Access the functions from PPT via an Excel object and have the XLA loaded for the Excel project.

    If th efunctions are really independent of both Excel and Powerpernt, then creating a DLL is the best way.

  6. #6
    New Lounger
    Join Date
    Sep 2004
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Call XLA from PPT? (Office97/SR2)

    Do you have this XLA functional in the XL environment? If so, how do you call its functions from XL VBA? What do thosemethods need that is unavailable when called from PP VBA?

  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: Call XLA from PPT? (Office97/SR2)

    Thanks, Howard,

    > separate DLL using VB 6.

    I agree that this seems the route. I tried switching to a DLL about two years ago, with little success. I seem to recall that a recompiled DLL left me with broken references in the application templates. i had to unlock the templates and reset the references. That's not an option for distributed locked templates.

    Maybe it's time for me to try again; perhaps i know more now (grin!)


    >from PPT via an Excel object and

    This sounds like the fastest route - for now - and would allow me to exercise these supposedly common functions before going to the (time) expense of setting up a common library in a DLL.

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

    Re: Call XLA from PPT? (Office97/SR2)

    >how do you call its functions from XL VBA?

    In an Excel VBA project, I set a reference to the Utils.XLA, and then refer to functions by name, or, if I can't remember the name, with a project modifier to make use of Intellisense:
    <pre> Call PlotChartPosition(cht,lngTop, lngLeft)</pre>

    or
    <pre> Call U.PlotChartPosition(cht,lngTop, lngLeft)</pre>



    >What do thosemethods need that is unavailable when called from PP VBA

    Nothing, really, that I can see.

    However, in PPT VBA one can't browse to and Choose an XLA file.

    The Excel library is there and is functioning well as a library of VBA routines; Excel applications make use of it. PPT seems to lack the ability to open the door, so to speak.

    Now I daresay that if I ever DO link this up, I'll discover that I have to modify my ways a little bit - perhaps I'm using Chart as an argument in Excel, and I should switch to Shape, or soemthing like that, but simple data items such as Longs and Strings should be available instantly.

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

    Re: Call XLA from PPT? (Office97/SR2)

    > You might want to check out some of the tools at his site

    Another good site; duly bookmarked, just in case we get another rainy day here in Toronto (grin!)

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

    Re: Call XLA from PPT? (Office97/SR2)

    >Access the functions from PPT via an Excel object

    OK, this worked, at least, it worked using my Personal.XLS:

    <pre>Sub test()
    Dim xl As Object
    Set xl = CreateObject("Excel.Application")
    ' xl.Application.Visible = True
    MsgBox strIncrementString("alpha001")
    xl.Application.Quit
    Set xl = Nothing
    End Sub</pre>


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

    Re: Call XLA from PPT? (Office97/SR2)

    Module UECharts in project UE in workbook UExcel00b.xls contains
    <pre>Public Function wbkCreateWorkbook() As Workbook
    Set wbkCreateWorkbook = Workbooks.Add
    'Sub TESTwbkCreateWorkbook()
    ' MsgBox wbkCreateWorkbook.FullName
    'End Sub
    End Function
    Public Sub TestMacro()
    MsgBox "hello"
    End Sub</pre>

    Module Module2 in project VBEProject in presentation ShowMe001.ppt contains
    <pre>Sub XLTest()
    Dim XL As Object
    Set XL = CreateObject("Excel.Application")
    XL.Application.Visible = True

    Dim wbkLib As Workbook
    Set wbkLib = XL.Workbooks.Open("C:GreavesTrainingSpreadsheetsEx celVbaLibrariesUExcel00B.xls")

    XL.Run "UECharts.TestMacro"

    ' Code below here fails with;
    ' "Run Time Error '438' Object doesn't support this property or method"
    '
    wbkLib.ue.testmacro
    wbkLib.ue.uecharts.testmacro
    wbkLib.testmacro

    Dim wbk As Workbook

    ' Code below here fails with;
    ' "Run Time Error '438' Object doesn't support this property or method"
    '
    Set wbk = wbkLib.uecharts.wbkCreateWorkbook
    Set wbk = wbkLib.ue.wbkCreateWorkbook
    Set wbk = wbkLib.wbkCreateWorkbook

    XL.Application.Quit
    Set XL = Nothing

    End Sub</pre>


    I

Posting Permissions

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