topic Creating PowerPoint Files from QV, Sort Order of Objects in QlikView

Creating PowerPoint Files from QV, Sort Order of Objects

Thu, 11 Feb 2010 17:21:20 GMT

Hi,

I'm currently working on creating a powerpoint file containing one chart per slide - I used this code from the forum (http://community.qlik.com/forums/p/16749/65774.aspx#65774) and it works just fine. The only issue I have is with the order of the charts on the slides. It seems that QlikView exports the charts in the order they were created. I've changed the ObjectIDs of the charts manually to the order I would like the Charts to be in but QV ignores that. 😕

Any ideas how to change the sort order? (Other than re-creating the charts in the order I would like to.)
A function to sort the array by ObjectID that is returned from GetGraphs would probably solve the issue too.

Thanks! Lukas

sub ppt
Set PPApp = CreateObject("Powerpoint.Application")
PPApp.Visible = True ' Create a presentation
Set PPPres = PPApp.Presentations.Add

set s=ActiveDocument.Sheets("Trend Charts")
charts=s.GetGraphs
for i=lbound(charts) to ubound(charts)
Set PPSlide = PPPres.Slides.Add(1, 1)
PPSlide.Shapes(1).Delete ' removes the title
PPSlide.Shapes(1).Delete ' removes the text box
ActiveDocument.GetSheetObject(charts(i).getobjectid).maximize
ActiveDocument.GetSheetObject(charts(i).getobjectid).CopyBitmapToClipboard
ActiveDocument.GetSheetObject(charts(i).getobjectid).minimize
with PPSlide.Shapes.Paste
.Left = 0
.Top = 30
.Width=720
end with

PPPres.SaveAs "C:\Trend_Charts.ppt"
PPPres.Close
'PPApp.Quit
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
end sub

Creating PowerPoint Files from QV, Sort Order of Objects

Thu, 11 Feb 2010 18:03:03 GMT

I think the following link will solve your issues.
http://diablopup.blogspot.com/2007/03/sorting-arrays-in-vbscript.html

/Fredrik

Creating PowerPoint Files from QV, Sort Order of Objects

Mon, 15 Feb 2010 10:30:20 GMT

Thanks Fredrik, I was hoping to get around this. 🙂

I haven't found time yet to test it, but will let you know.

Cheers, Lukas

Creating PowerPoint Files from QV, Sort Order of Objects

Sat, 06 Mar 2010 11:59:29 GMT

Fredrik,

took a while to have time to test out, but it works with a few changes, thank you. It is a simple bubble sort, in terms performance probably not the best, for 12 charts it does the job... 😉

The final script piece for the sorting looks like below:


 for i = UBound(charts) - 1 To 0 Step -1
 for j= 0 to i
 if charts(j).getobjectid>charts(j+1).getobjectid then
 set temp=charts(j+1)
 set charts(j+1)=charts(j)
 set charts(j)=temp
 end if
 next
 next

Creating PowerPoint Files from QV, Sort Order of Objects

Thu, 20 Oct 2011 16:29:13 GMT

This was a very helpful post for me to learn export to power point functionality. Thank you very much each of you for sharing your knowledge.

I have 33 minimized charts on one tab that i want to export to ppt. I am using the code shared above and everything is working awesome other than sorting part. My charts are numbered (object id) from straight 1 to 33 but stil i am getting as if its downloading randomly.

Below is my Sub that I am using for this. I would really appreciate every help from you guys. Is there something that I am doing wrong?

sub Export

Set PPApp = CreateObject("Powerpoint.Application")
PPApp.Visible = True ' Create a presentation

Set PPPres = PPApp.Presentations.Add

set s=ActiveDocument.Sheets("Export to PPT")
charts=s.GetGraphs

for i = UBound(charts) - 1 To 0 Step -1
for j= 0 to i
if charts(j).getobjectid>charts(j+1).getobjectid then

        set temp=charts(j+1)
        set charts(j+1)=charts(j)
        set charts(j)=temp
        end if
      next
    next

for i=lbound(charts) to ubound(charts)
Set PPSlide = PPPres.Slides.Add(1, 1)
PPSlide.Shapes(1).Delete ' removes the title
PPSlide.Shapes(1).Delete ' removes the text box
ActiveDocument.GetSheetObject(charts(i).getobjectid).maximize
ActiveDocument.GetSheetObject(charts(i).getobjectid).CopyBitmapToClipboard
ActiveDocument.GetSheetObject(charts(i).getobjectid).minimize
with PPSlide.Shapes.Paste
      .Left = 30
      .Top = 70
      .Width=660

end with
next

PPPres.SaveAs "C:\DC-DC.ppt"
'PPPres.Close
'PPApp.Quit
Set PPSlide = Nothing
Set PPPres = Nothing

Re: Creating PowerPoint Files from QV, Sort Order of Objects

Wed, 14 Dec 2011 07:02:31 GMT

Hi,

Can anyone tell me how to export all the Sheet objects in a sheet to excel.thanks in advance for ur help.

Re: Creating PowerPoint Files from QV, Sort Order of Objects

gainkarthi — Mon, 18 Feb 2013 05:02:10 GMT

My macro is hanging in the below step,

set s=ActiveDocument.Sheets("Trend Charts")

Please help.