Exporting charts as pic and tables as excel files

[Anonymous]

Helol,

I have a report that contains charts and tables.

I already have a script that is exporting the charts and tables as pictures into ppt. I'm using this one that I found here.

Sub export
'An array with the objects you want to export to powerpoint
Dim myArray(3)
myArray(0) = "CH01"
myArray(1) = "CH02"
myArray(2) = "CH03"
myArray(3) = "CH04"

'Create ppt object
Set objPPT = CREATEOBJECT("Powerpoint.Application")
objPPT.Visible = True
Set objPresentation = objPPT.Presentations.Add

  'For each object in the array create a new slide and paste it.
For each item in myArray

Set PPSlide = objPresentation.Slides.Add(1,11)
ActiveDocument.GetSheetObject(item).CopyBitmapToClipboard
PPSlide.Shapes.Paste

Next
'Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

msgbox "PPT Extract Successful",64,"Complete"

End Sub

However, CH02 and CH04 are actually tables and I'd rather have them exported as Excel and added, as object, into my ppt.

Currently I manually export them from Qlik into Excel, save them on my machine and insert them as object into the ppt.

Is it somehow possible to do this via a macro as well?

Any help welcome.:D

[marcus_sommer]

You could try to split your loop with a further if-loop which switched between the object-types from CopyBitmapToClipboard to CopyTableToClipboard.

- Marcus

[petter]

This code work very well for me with PowerPoint 2013:

[Anonymous]

Hello Petter,

that works partially.. Now the tables are added as tables into the ppt. However, I want them to be added as Excel objects and not as Excel Tables. They are way too big to get them all on one sheet.

So the idea is to have the Excel file in the ppt and the user double click it.

Any solution for that ?

[marcus_sommer]

I think this won't be possible in this way. The table within the clipboard isn't a excel-object.

For this you will need your mentioned way from above but maybe automated. Have a look on this and similar sites:

Using Excel with Other Office Applications

- Marcus

[petter]

Yes - I misunderstood - didn't think that Excel was a wanted format only that you didn't want it as bitmaps of tables. In that case you will have to also get hold of Excel.Application and automate Excel as well as PowerPoint. You will have to orchestrate the copy from QlikView - paste to - excel and then copy from Excel - paste to PowerPoint ... It is quite doable but probably not lightning fast ...

I would personally prefer to do the Automation from PowerPoint VBA and controlling QlikView and Excel from there or from Excel VBA and controlling QlikView and PowerPoint from there. In VBA you have a much much better Integrated Development Environment with intellisense for the Object Models ... not only for PowerPoint and Excel but also for QlikView if you create the objects with Early Binding which gives you better perfomance anyway.

In QlikView VBScript you can only have late binding.

Google this if you want to know more about early vs late binding in Automation:

         using early binding and late binding in automation

[petter]

Actually I have some working code that I could share... That does a copy and embeds the Excel workbook as an editable object displayed as a table....

[Anonymous]

Hello Petter,

would you be able to share that?

[petter]

Yes I certainly can .... just give me about an hour to finish something of ... and I will post a sample

[petter]

Start Excel and press ALT+F11 once you have a blank WorkSheet in a new WorkBook open.

Sub QlikView_PastToExcel_CopyFromExcel_And_EmbedInPowerPoint()

    Dim ppApp As PowerPoint.Application
    Dim ppPres As PowerPoint.Presentation
    Dim ppSlide As PowerPoint.Slide

    Set ppApp = New PowerPoint.Application
    'Set ppPres = ppApp.ActivePresentation

    Set ppPres = ppApp.Presentations.Add
    Set ppSlide = ppPres.Slides.Add(1, ppLayoutBlank)

    Dim xlSheet As Excel.Worksheet
    Set xlSheet = Excel.ActiveSheet
    Dim xlRange As Excel.Range

    Dim qvApp As QlikView.Application
    Dim qvDoc As QlikView.Document
    Dim qvSheet As QlikView.Sheet

    Set qvApp = New QlikView.Application
    qvApp.ActiveDocument.ActiveSheet.Activate

    Set qvDoc = qvApp.ActiveDocument
    Set qvSheet = qvDoc.ActiveSheet

    Dim QVtablebox As QlikView.StraightTableBox

    For Each o In qvSheet.GetStraightTableBoxes
        o.CopyTableToClipboard (True)
        xlSheet.Paste
        Selection.Copy
        ppSlide.Shapes.PasteSpecial DataType:=ppPasteOLEObject
    Next

End Sub

This is how it should look like:

Make sure to have both PowerPoint open and QlikView open with an application with one Straigth Table on the active Sheet.

Then you can single step your way through the code in VBA by pressing F8 repeatedly ...

This code is quite raw - no error checking no structure ... but it shows you the basics how to declare your objects and get references to them etc...