Macro to generate PPT file (synchonization problem)

Anonymous — Tue, 16 Mar 2010 07:27:07 GMT

I have written a macro in order to generate a PPT file.

In the macro I have a Loop to change Qlikview selection application & to re-export a sheet copy in PPT.

It's working but I have to insert a msgbox command in the code in order to have synchonization. If I delete the msgbox command I have the same copy screen in the PPT (the change selection is not updated in the PPT)

do you have any idea ?

thanks in advance


sub exportToPPT()
 dim i
 dim loopDimensionValues
 dim loopDimensionValue
 dim pLoopDimensionField

 pLoopDimensionField = ActiveDocument.Variables("v_loop_dimension").GetContent().String
 'pLoopDimensionField = "VACCINE"

 ' PPT Template to use for export
 ' TODO template as a parameter
 vPPTTemplatePath = "C:\Mon Espace Disque\jbeausseron\10 - chantiers\Business Planning\Leo\Roadmap\prototypes\QV\PPT-prototype-JBN\Report_Template.ppt"

 ' QV object to export to PPT document
 ' TODO ID of the object to export as a parameter
 vObjectToCaptureId = "SHEET_MAIN"

 Set PPApp = CreateObject("Powerpoint.Application")
 PPApp.Visible = True

 ' Creation of the PPT document
 ' OPTION 1: From scratch : new document
 'Set PPPres = PPApp.Presentations.Add
 ' OPTION 2: Creation from an existing PPT template
 Set PPPres = PPApp.Presentations.Open(vPPTTemplatePath, -1) ' -1 = read only
 ' Loop on appropriate dimension to generate One PPT slide per item
 set loopDimensionValues = ActiveDocument.fields ( pLoopDimensionField ).GetPossibleValues
 for i=0 to loopDimensionValues.Count - 1
 loopDimensionValue = loopDimensionValues.Item(i).text

 ' Selecting the item in the appropriate control to filter data
 ActiveDocument.Fields ( pLoopDimensionField ).Select loopDimensionValue

 ' Creation of the slide in the PPT document
 ' TODO optimize the slide layout
 Set PPSlide = PPPres.Slides.Add(PPPres.Slides.Count + 1, 3)

 ' Updating the slide with appropriate data
 PPSlide.Shapes(1).TextFrame.TextRange.Text = pLoopDimensionField & ": " & loopDimensionValue

 ' TODO: find a clean way to avoid this ; here the msg box is used to let QV refreshing the sheet
 ' an thus capture the sheet image correcly
 msgbox("Exporting slide " & (i+1) & "...")

 ' Capture of the appropriate sheet as an image
 ActiveDocument.Sheets(vObjectToCaptureId).CopyBitmapToClipboard
 ' Paste in the PPT slide and position
 ' TODO : generic position
 PPSlide.Shapes.Paste
 PPSlide.Shapes(PPSlide.Shapes.Count).Width = 0.9 * PPSlide.Master.Width
 PPSlide.Shapes(PPSlide.Shapes.Count).Left = 0.05 * PPSlide.Master.Width
 ' PPSlide.Shapes(PPSlide.Shapes.Count).Height = 0.75 * PPSlide.Master.Width
 PPSlide.Shapes(PPSlide.Shapes.Count).Top = (0.25 / 2) * PPSlide.Master.Width
 next

 Set PPSlide = Nothing
 Set PPPres = Nothing
 Set PPApp = Nothing
end sub

Macro to generate PPT file (synchonization problem)

rwunderlich — Wed, 17 Mar 2010 08:51:03 GMT

Instead of the msgbox try waiting on QV with:

ActiveDocument.GetApplication.WaitForIdle

-Rob

topic Macro to generate PPT file (synchonization problem) in QlikView

Macro to generate PPT file (synchonization problem)

Macro to generate PPT file (synchonization problem)