topic Export into ppt in QlikView

Export into ppt

sathishkumar_go — Tue, 29 Jul 2014 22:43:38 GMT

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

Re: Export into ppt

marcus_sommer — Wed, 30 Jul 2014 10:24:22 GMT

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

Re: Export into ppt

SunilChauhan — Wed, 30 Jul 2014 10:44:31 GMT

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

Set PPSlide = Nothing

Set PPPres = Nothing

End sub

Re: Export into ppt

vikasmahajan — Wed, 30 Jul 2014 10:52:26 GMT

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

Re: Export into ppt

sathishkumar_go — Wed, 30 Jul 2014 17:12:00 GMT

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

Re: Export into ppt

sathishkumar_go — Wed, 30 Jul 2014 17:19:18 GMT

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

Re: Export into ppt

marcus_sommer — Wed, 30 Jul 2014 19:52:05 GMT

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

Re: Export into ppt

sathishkumar_go — Wed, 30 Jul 2014 21:40:10 GMT

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

Re: Export into ppt

marcus_sommer — Thu, 31 Jul 2014 06:20:41 GMT

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

Re: Export into ppt

sathishkumar_go — Fri, 01 Aug 2014 17:18:56 GMT

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

Re: Export into ppt

marcus_sommer — Sat, 02 Aug 2014 08:37:23 GMT

Hi Sathish,

I haven't a macro for that - you could look into APIGuide.qvw in your install-folder. There by search from "getrowcount" and similar in field member you will find examples which loop through objects on cell-level. Then you need a counter for your row-cluster and collect the field-values inside these cluster and use this for selections on these fields, make the export, remove the selection, collect the next row-cluster and so on - but you will need some effort to bring these approach to work + by your amount of data and exports it will be rather (too ?) slow ...

I think the other both solutions - creating the row-cluster within in the sript or using a prepared master-ppt which have a changing data-source (through full-export into xls(x) or csv) - will be easier and faster.

Generall by your amount of reports ( and I assume you will have some more as that) you should consider if ppt is a suitable format and if you could use tools for that, publisher, nprinting or othe third-party-tools.

- Marcus