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

    Addressing a collection 'out of order' (VBA/PPT)

    Here is where my lack of formal programming instruction is letting me down. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    In extracting text from various shapes on a PowerPoint slide, I've discovered that the sequence of Shapes in the Shapes collection does not necessarily match up the order that humans read a slide (top left to bottom right). Whatever the sequence is, and whatever the end user did to generate that sequence, what I need is to iterate through the Shapes collection in the following sort order (or something similar):

    0010x0125 (e.g., .Top x .Left pixels in a nice, consistent, sortable string format)
    0010x0800
    0300x0125
    0300x0575
    etc.

    I know how I would gather and format the .Top and .Left (and of course the .Index back into the Shapes collection), but beyond that, I don't know how to build an array or collection or dictionary that I can easily and reliably access in the desired order. What would be a good way to do that? Thanks!

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

    Re: Addressing a collection 'out of order' (VBA/PPT)

    Would it be feasible to store the data (x, y, index) in a database table? You can then query them any way you want.

  3. #3
    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: Addressing a collection 'out of order' (VBA/PP

    Hmmm. I could build an in-memory ADO recordset on the fly with a client-side cursor; if necessary, it could be persisted to disk as a disconnected recordset. Not sure what kind of overhead that might entail.

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

    Re: Addressing a collection 'out of order' (VBA/PP

    Why not build a collection of shapes and include their x and y positions as indexes? I think you'll run into problems though if you have overlapping shapes, or any kind of fancy effects. I've never figured out what logic any of the Office apps uses in determining sequence, except that it seems to be the order in which the objects were created, which isn't terribly useful. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  5. #5
    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: Addressing a collection 'out of order' (VBA/PP

    I'm familiar with the Key and Value type of collection (a VBA Collection), but I'm not sure how to access such a Collection in the desired sequence. Will For Each automatically use ascending order on the Key, or might the sequence be more mysterious?

    If there is another type of collection that might work better, I'm certainly taking all suggestions.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Addressing a collection 'out of order' (VBA/PPT)

    Why not use a "traditional" array of shapes?

    Some air code:

    Dim oShapes() as Shape
    Dim oShape as Shape
    Dim lShapeCt as long
    lShapeCt=0
    Redim oShapes(1)
    For Each oShape in Activepresentation.Shapes 'Dunno the pwpt syntax
    lShapeCt=lShapeCt+1
    Set oShapes(lShapeCt)=oShape
    Redim Preserve oShapes(lShapeCt+1)
    Next
    'Now sort oShapes() somehow:
    For lShapeCt=1 to ubound(oShapes())
    If oShapes(lShapeCt).Top<........... Then
    'Do something
    End If
    Next
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Addressing a collection 'out of order' (VBA/PP

    Thinking it over, the collection might give you fits, since numeric keys don't work. I remember once writing a VB app that read items in from a text file and created a secondary index on them so I could reverse reference them, but I'll have to rummage about and find what I did. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Addressing a collection 'out of order' (VBA/PPT)

    <P ID="edit" class=small>(Edited by AlanMiller on 14-Sep-05 00:25. This revised code seems to do the trick!)</P>Would it be possible to build a Dictionary object, using the ".Top x .Left" strings you've listed as the Item keys, the Item itself a reference to the appropriate shape, and add the Items in the desired order to the Dictionary? This way, (I think) you could access the Dictionary Items using For Each... Next, in the desired order (in which they were added).

    If I'm wrong about For Each, maybe an adaptation of this code would do it:<code>
    [VBScript]
    Dim WSHShell
    Set WSHShell = WScript.CreateObject("WScript.Shell")

    Function DicDemo
    Dim a, d, i ' Create some variables.
    Set d = CreateObject("Scripting.Dictionary")
    d.Add "0010x0125", "Shape 6" ' Add some keys and items.
    d.Add "0010x0800", "Shape 3"
    d.Add "0300x0125", "Shape 1"
    d.Add "0300x0575", "Shape 4"

    a = d.Keys ' Get the keys & items.
    b = d.Items

    For i = 0 To d.Count -1 ' Iterate the array.
    s = s & a(i) & vbTab & b(i) & vbcrlf ' Return results.
    Next

    DicDemo = s
    End Function

    wscript.echo DicDemo</code>

    Alan

  9. #9
    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: Addressing a collection 'out of order' (VBA/PP

    Alan, my problem is the order in which they are added. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> While I can't be 100% certain, I believe that if I iterate through the Slides(x).Shapes collection, I most likely will get them in the order added. So I changed your code and they remain in that order despite the sort key (illustration attached).

    I may have to learn something about sorting arrays (without the WordBasic.SortArray crutch).

  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: Addressing a collection 'out of order' (VBA/PP

    > 'Now sort oShapes() somehow

    Yes, I'm pretty sure I've ended up at the right place. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Addressing a collection 'out of order' (VBA/PP

    My misinterptetation I'm afraid. When I read
    "what I need is to iterate through the Shapes collection in the following sort order (or something similar)"
    I was thinking of some user(=Jefferson)-defined order, not the order in which the author had created the shapes.

    Alan

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

    Re: Addressing a collection 'out of order' (VBA/PP

    Jefferson,

    There is a QuickSortArray routine in the XP Code Librarian that came with the ODE. It recurses through the array comparing the low and high elements and shifting them around as it works toward the middle. I've included the CodeLibrarian code in an attachment, since I believe that it is freely available. Your problem would seem to be how you could compare the values. I think you would need to store the x, y values separately so that you could compare them separately. There is a somewhat lengthy article here that might give you some ideas. For reference, there is also an article on quicksorting in VBScript here.
    Charlotte

  13. #13
    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: Addressing a collection 'out of order' (VBA/PP

    Thanks, Charlotte. If I create a variant array where I "string" together all three data bits (top, left, shape collection index), I get the correct order:

    Sub Test_QuickSortArray()
    Dim vArr(4) As Variant, intCount As Integer
    vArr(0) = "0010x0125x1"
    vArr(1) = "0300x0550x2"
    vArr(2) = "0300x0050x3"
    vArr(3) = "0075x0975x4"
    vArr(4) = "0650x0001x5"
    QuickSortArray vArr ' ByRef !
    For intCount = 0 To UBound(vArr)
    Debug.Print vArr(intCount)
    Next
    End Sub

    I'm much more familiar with string manipulation than I am with variant arrays, so I might not try to deal with multiple dimension sorting at this time. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Addressing a collection 'out of order' (VBA/PP

    Just as long as you found a method that works, the details are secondary. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Charlotte

Posting Permissions

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