Creating PowerPoint Files from QV, Sort Order of Objects

[#1]

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

next

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

[#2 · Accepted solution]

Hi

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

/Fredrik

[#3]

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

[#4]

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

[#5]

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

[#6]

Hi,

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

[gainkarthi]

My macro is hanging in the below step,

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

Please help.