topic Issue with export to powerpoint macro in QlikView

Issue with export to powerpoint macro

Thu, 02 Dec 2010 08:06:51 GMT

Hi,

I have a macro to export the charts to multiple slides in powerpoint. The export is working fine without any issues for charts which are visible in the sheet.

I have many hidden charts which are restored by a variable selection. I need to export them to powerpoint. I have incuded additional script in macro to change the variable name, so that the hidden charts are restored and then exported to powerpoint slides. This is not working. The variable value are changed correctly by the macro, but the corresponding charts are not exported.

The script is given below:


'The below macro is to set the variable value to hide/display the corresponding charts
sub varSel(varContent)

 Set v = ActiveDocument.Variables("vAnalysisMode")
 v.setContent varContent, true

end sub
sub exportSlide(slideRef)
 select case slideRef

 case "S_1"



 'set the variable value to display the appropriate charts
 call varSel("MAT Financials")

 intSlideNumber = 1

 'Charts to be exported to slide 1
 call exportObject("CH366", intSlideNumber, 32, 31, 80, 90)


 case "S_4"

 'set the variable value to display the appropriate charts
 call varSel("$ per SSU")
 call exportObject("CH411", 4, 200, 100, 80, 90)


 end select

 'call varSel("False()")
end sub
'Function to export the objects to slides
function exportObject(objectId, slideNumber, width, height, posLeft, posTop)
 ActiveDocument.GetSheetObject(objectId).CopyBitmapToClipboard
 ppApp.ActiveWindow.View.Gotoslide slideNumber
 ppApp.ActiveWindow.ViewType=1
 ppApp.ActiveWindow.View.Paste
 ppApp.ActiveWindow.Selection.ShapeRange.Left = posLeft
 ppApp.ActiveWindow.Selection.ShapeRange.Top = posTop
End function

The macro gets debugged without any error and the powerpoint is generated but, the required charts are not copied. Am I missing something? please throw some light on this.

Thanks,

Haneesh

Issue with export to powerpoint macro

Thu, 02 Dec 2010 08:25:09 GMT

Try using Application.Refresh to force redrawing of the objects.

Issue with export to powerpoint macro

Thu, 02 Dec 2010 08:39:41 GMT

try to call this before copying the image to clipboard.

ActiveDocument.GetApplication.WaitForIdle

I hope this helps.

Issue with export to powerpoint macro

Thu, 02 Dec 2010 08:43:01 GMT

Thanks LuciaN...no luck with Application.Refresh 😞

Issue with export to powerpoint macro

Fri, 03 Dec 2010 10:36:46 GMT

Hi Haneesh,
I have an app where I have a tab that contains replica tables & charts that I also have hidden from Users. Like you, when the User presses the button to extract to PowerPoint or Excel, a variable opens the tab & allows the export and then hides the tab from them when finished. Also like you, I found that even though the macro ran there was no output!
I got around it by using the API "Restore" bit of code; sample attached below:

set v = ActiveDocument.Variables("vshow")
v.ForceContent "0",0
set s=ActiveDocument.Sheets("PPT_Extracts")
ActiveDocument.Sheets("PPT_Extracts").Activate
ActiveDocument.ClearCache
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.GetSheetObject("TX140").Restore
ActiveDocument.GetSheetObject("TX140").CopyTextToClipboard
PPApp.Visible = True
Set PPSlide = PPPres.Slides(1)
PPSlide.Shapes(4).TextFrame.TextRange.Paste
PPSlide.Shapes(4).Left = 28.3447
PPSlide.Shapes(4).Top = 204.6485
PPSlide.Shapes(4).Height = 29.195
PPSlide.Shapes(4).Width = 655.6122
Set PPShapes = PPPres.Slides(1).Shapes(4).TextFrame.TextRange
PPShapes.Font.Name = "Tahoma"
PPShapes.Font.Color.RGB = RGB(25,77,109)
Set PPFont = PPPres.Slides(1).Shapes(4).TextEffect
PPFont.FontSize = 28
ActiveDocument.GetSheetObject("TX140").Minimize
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.ClearCache

Hope this helps, or at least sets you on the right path.
Regards,
Jon