Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a requirement to export the qlikview chart in to PPT through macro and its working fine.
Now i am facing a issue, some of the charts are not aligned properly means if i have 1 million records in the chart then while exporting into PPT the chart has to be split and paste into PPT slides.
Looking forward your help and solution.
Thanks in advance.
Sathish
You want really export 1 million records into ppt?
If the export-object is too big to size it properly on one slide - I would wrapp the export-macro in a loop-routine which selects always only so many records which fit on one slide - perhaps you need an additionally field for this through which the routine could loop.
- Marcus
Hi satish,
you could customized like below
Sub ExportPPT
ActiveDocument.ActiveSheet.FitZoomToWindow
Set objPPT = CreateObject("PowerPoint.Application")
objPPT.Visible = True
Set objPresentation = objPPT.Presentations.Add
For i = 0 To ActiveDocument.NoOfSheets - 1
' Set MySheet = ActiveDocument.GetSheet(i)
Set PPSlide = objPresentation.Slides.Add(1,1)
PPSlide.Shapes(1).Text ' removes the title
PPSlide.Shapes(1).Delete ' removes the text box
' ActiveDocument.ActiveSheet.CopyBitmapToClipboard
ActiveDocument.GetSheetObject("CH03").CopyBitmapToClipboard
PPSlide.Shapes.Paste
with PPSlide.Shapes(PPSlide.Shapes.Count)
.left = 0
.top = 60
.width=200
.Height=200
end with
ActiveDocument.GetSheetObject("CH04").CopyBitmapToClipboard
PPSlide.Shapes.Paste
with PPSlide.Shapes(PPSlide.Shapes.Count)
.left = 380
.top = 60
.width=250
.Height=200
end with
ActiveDocument.GetSheetObject("CH05").CopyBitmapToClipboard
PPSlide.Shapes.Paste
with PPSlide.Shapes(PPSlide.Shapes.Count)
.left = 0
.top = 300
.width=200
.Height=200
end with
ActiveDocument.GetSheetObject("CH06").CopyBitmapToClipboard
PPSlide.Shapes.Paste
with PPSlide.Shapes(PPSlide.Shapes.Count)
.left = 380
.top = 300
.width=250
.Height=200
end with
Set PPSlide = objPresentation.Slides.Add(2,1)
PPSlide.Shapes(1).Delete ' removes the title
PPSlide.Shapes(1).Delete ' removes the text box
ActiveDocument.GetSheetObject("CH07").CopyBitmapToClipboard
PPSlide.Shapes.Paste
with PPSlide.Shapes(PPSlide.Shapes.Count)
.left = 0
.top = 40
.width=200
.Height=200
end with
ActiveDocument.GetSheetObject("CH08").CopyBitmapToClipboard
PPSlide.Shapes.Paste
with PPSlide.Shapes(PPSlide.Shapes.Count)
.left = 380
.top = 40
.width=200
.Height=200
end with
Next
Set PPSlide = Nothing
Set PPPres = Nothing
End sub
It is not feasible to export millions of into PPT try to export into CSV and then do whatever you want to do with data.
Vikas
HI Marcus,
Thanks for your reply.
While exporting into PPT, I am applying relevent selections. After that also object size is too big.
So basically i want to split the object based on the object size and it should be exported into ppt slides.
- Sathish
Hi Chauhan,
Thanks for providing the macro. But in the macro will export different objects into different slides.
But in some case, I will not sure how much data will be in the object. Because the data is more then object has to split and export into other slides.
Here i am strucking to split the object and export in to the ppt slides.
Regards
Sathish
I meant not really a normal field-selections - more a field which contained a rowno-cluster from your object, maybe it will be not easy to create that ...
Another possibility could be to read within the macro the first 30 to 50 rows from your object and use these values to make selections - the export and afterwards select the next row-area and then your object has always the same size by the export.
Maybe it is easier to export your data into a csv-file and then build your charts in ppt themselves ... or you used pdf-prints ... I couldn't imagine that people want to look to a ppt with hundreds of slides with the same object.
- Marcus
HI Marcus,
Actually this project will create a set of ppt files with standardised format and distribute across all the management peoples automatically. They don't want any manual work here.
I have to go with macro to 30 (Fitted) rows will export into 1st slide and other rows will export in next slides accordingly.
But i am not able to splitting the object by 30 - 40 rows slide by slide in macro.
-Sathish
Hi Sathish,
you will definitely need some logic to count and identify your rows to create row-clusters through which you could loop. Both above mentioned methods - creating row-cluster as a native field within the script or reading your object(s) --> maybe you have more as ones, hidden help-objects only for these certain reason - will work. But it won't be trivial ...
Therefore it could be easier to export your objects into xls(x) or csv and counting and creating your row-cluster is very simple. Afterwards you could use these row-cluster informations for your final export into ppt - it is a two-step logic.
- Marcus
HI Marcus,
Thanks for providing the solution.
Do you have any macro for that? for spliting the qlikview docment and export into different slids?
-Sathish