GetObjectId

[Anonymous]

I have a sheet with several pivot charts that are all set to auto-minimze so that only one is open at a time. I have a macro that creates an Outlook message and attaches an Excel file of the currently open pivot. But right now I am doing this by hard-coding the object ID. What I'd like the macro to do is loop through all the pivot chart objects on the sheet, determine which one is open (not IsMinimized) and grab its ObjectID. That way the macro won't need to have Object ID hard-coded and it will work for whichever pivot is open. But I'm having troubl getting this part to work. Can anyone help?

[#2 · Accepted solution]

Hello Brian

I put together the following quick macro that worked for me in terms of providing all objects, with their ID's, so I'm sure you can incorporate it:

SUB Objects
Shtobj = ActiveDocument.ActiveSheet.GetSheetObjects
for i = lBound(Shtobj) to uBound(Shtobj)
msgbox(Shtobj(i).GetObjectId)
msgbox(Shtobj(i).IsMinimized)
next
END SUB

' FURTHER INFO
' YOU MIGHT WANT TO WRAP THE FOLLOWING IF STATEMENT INTO THE ROUTINE ABOVE (IT DEALS WITH PIVOTS)
' If Shtobj(i).GetObjectType = 10 Then 'pivot tables
' set Pivot = Shtobj(i)
' set y = Pivot.GetProperties
' etc, etc

Hope it helps you out.

Nigel.

[Anonymous]

Perfect, thanks!

Here is my final macro if anyone is interested:

SUB SendFile' identify the open pivotShtobj = ActiveDocument.ActiveSheet.GetSheetObjectsfor i = lBound(Shtobj) to uBound(Shtobj) If Shtobj(i).GetObjectType = 10 Then set Pivot = Shtobj(i) If not Shtobj(i).IsMinimized Then vCurrentPivotID = (mid(Shtobj(i).GetObjectId,10)) vCurrentPivotName = Shtobj(i).GetCaption.Name.v end if end ifnext 'Create Fileset obj = ActiveDocument.GetSheetObject(vCurrentPivotID)vFilename = "C:\" & vCurrentPivotName & ".xls"obj.ExportBiff vFilename'Open a new Outlook e-mail form and pre-populate.'The user can further customize the e-mail before sending.vBody="Please review the attached file." & vbCrLfSet vOlApp = CreateObject("Outlook.Application")Set vMessage = vOlApp.CreateItem(olMailItem) vMessage.Subject = "QlikView Data Question" vMessage.To = "" vMessage.Body = vBody & vbCrLfvMessage.Attachments.Add (vFilename)vMessage.DisplayEND SUB

[blaise]

You also have the IsActive function, but if you click on a button to run the macro that button will be the activated object...

I solved this by assigning the macro to onToolbarMacro1 (yiou can add macros to 3 buttons in the toolbar.