Formulas of Linked Pictures in Groups

[Legacy 356664]

Hi there,

I am trying to retrieve the formula from all linked pictures so that I can turn on/off updates using defined names.
This is working fine, except for when the linked picture is part of a group.

I have tried
For Each Pic In ActiveSheet.Pictures
Select Case Pic.Type
Case msoGroup
For groupIndex = 1 To Pic.GroupItems.Count
array(x) = Pic.GroupItems(groupIndex).Formula
x = x + 1
Next groupIndex
End Select
Next Pic

But this returns an empty string (in arr(x))

Any suggestions? Thanks for your help

 

[Domenic]

Try something like this...

[color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] GetFormulasFromLinkedPictures()

    [color=darkblue]Dim[/color] aFormulas() [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] oShape [color=darkblue]As[/color] Shape
    [color=darkblue]Dim[/color] oGroupItem [color=darkblue]As[/color] Shape
    [color=darkblue]Dim[/color] PicCnt [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    PicCnt = 0
    [color=darkblue]For[/color] [color=darkblue]Each[/color] oShape [color=darkblue]In[/color] ActiveSheet.Shapes
        [color=darkblue]Select[/color] [color=darkblue]Case[/color] oShape.Type
            [color=darkblue]Case[/color] msoPicture
                PicCnt = PicCnt + 1
                [color=darkblue]ReDim[/color] [color=darkblue]Preserve[/color] aFormulas(1 [color=darkblue]To[/color] PicCnt)
                aFormulas(PicCnt) = oShape.DrawingObject.Formula
            [color=darkblue]Case[/color] msoGroup
                [color=darkblue]For[/color] [color=darkblue]Each[/color] oGroupItem [color=darkblue]In[/color] oShape.GroupItems
                    [color=darkblue]If[/color] oGroupItem.Type = msoPicture [color=darkblue]Then[/color]
                        PicCnt = PicCnt + 1
                        [color=darkblue]ReDim[/color] [color=darkblue]Preserve[/color] aFormulas(1 [color=darkblue]To[/color] PicCnt)
                        aFormulas(PicCnt) = oGroupItem.DrawingObject.Formula
                    [color=darkblue]End[/color] [color=darkblue]If[/color]
                [color=darkblue]Next[/color] oGroupItem
        [color=darkblue]End[/color] [color=darkblue]Select[/color]
    [color=darkblue]Next[/color] oShape
    
    [color=darkblue]If[/color] PicCnt > 0 [color=darkblue]Then[/color]
        [color=green]'Do something with the formulas in the array aFormulas()[/color]
    [color=darkblue]Else[/color]
        MsgBox "No pictures exist.", vbInformation
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Hope this helps!

 

[Legacy 356664]

Thanks Domenic,

You're a lifesaver

 

[Domenic]

You're very welcome!

Cheers!