topic Re: Export Image to insert in ppt in QlikView

Export Image to insert in ppt

Fri, 23 Sep 2011 08:08:45 GMT

Good Mornig,

i need your help to export a table not in excel format but as an image.

My table identificative name is "Table_1"

Until today I Use this macro to export in excel Table1:

sub ExportToExcel()

set Table_1 = ActiveDocument.GetSheetObject("Table_1")

Table_1.ExportEx "G:\document\Table_1.xls", 5

end sub

How can i write a macro to export Table_1 as image (.png, or btm or jpeg... high quality is preferred)

(I need an image to insert in ppt. I think that is the better solution to connect qlik to ppt...)

Thank you

Marco

Re: Export Image to insert in ppt

IAMDV — Fri, 23 Sep 2011 09:35:58 GMT

Hi Marco,

Here is the code to export Sheet Object as image...

Sub Export_Image()

'The next 3 lines will set the user preference to enable Caption & Border while exporting

set up = ActiveDocument.GetApplication.GetUserPreferences

up.ChartUseCapNBorder = True

ActiveDocument.GetApplication.SetUserPreferences up

'The next 2 lines will export the chart object

set obj = ActiveDocument.GetSheetObject("CH512")

obj.ExportBitmapToFile "C:\MyLocation\MyImage.bmp"

End Sub

You can export the image in different formats : JPEG, BMP & PNG Etc.

I hope this helps!

Cheers - DV

Re: Export Image to insert in ppt

Tue, 27 Sep 2011 10:20:54 GMT

Thank you!

It works for the active sheet.

How can i do if i want to export an object as image and this object is in another sheet?

I have 10 object in 10 shett and i want to export all with a macro.

I try to find macro that can export all the object of all sheets but i haven't found a solution.

Thank you a lot!!

Marco

Re: Export Image to insert in ppt

IAMDV — Tue, 27 Sep 2011 11:42:03 GMT

Hi Marco,

I'll create an example this afternoon and I'll get back to you ASAP.

Cheers - DV

Re: Export Image to insert in ppt

Tue, 27 Sep 2011 12:07:16 GMT

Thank you! Great!

Re: Export Image to insert in ppt

IAMDV — Tue, 27 Sep 2011 16:27:45 GMT

Hi Marco,

Here is the code to export all the Chart Objects.

Sub Export_All_Chart_Images()

For i = 0 To ActiveDocument.NoOfSheets - 1

Set MySheet = ActiveDocument.GetSheet(i)

MyCharts=MySheet.GetGraphs

For X =lbound(MyCharts) to ubound(MyCharts)

ActiveDocument.Sheets(i).Activate

msgbox("Chart: " & MyCharts(X).GetObjectId)

Set obj = ActiveDocument.GetSheetObject(MyCharts(X).GetObjectId)

obj.ExportBitmapToFile "C:\MyLocation\Images_" & Right(MyCharts(X).GetObjectId, 4) & ".bmp"

Next

End Sub

I hope this helps!

Cheers - DV

Re: Export Image to insert in ppt

Wed, 28 Sep 2011 07:53:13 GMT

Hi DV,

thks for the macro!! As you've written it works only for the charts but i have also table.

So i modify your macro to export all object:

Sub Export_Objects()

For i = 0 To ActiveDocument.NoOfSheets - 1

Set s = ActiveDocument.GetSheet(i)

Objects=s.GetSheetObjects

For X =lbound(Objects) to ubound(Objects)

ActiveDocument.Sheets(i).Activate

msgbox("Chart: " & Objects(X).GetObjectId)

Set obj = ActiveDocument.GetSheetObject(Objects(X).GetObjectId)

obj.ExportBitmapToFile "G:\Document\Images_" & Right(Objects(X).GetObjectId, 4) & ".bmp"

End Sub

This macro Export correctly all the objects in the folder as image.

There are only two little problem:

1 - The macro export all objects (button, text box, ...) and i don't need them. But i can connect in my ppt only important image... It coul'd be perfect to export only tables and charts

2- After every export of single object, appears a msg box and i've to press "Enter" several times.. (i've 15 sheets and every sheet has 3-5 objects...) Is it possible to exclude this msg box? I try to delate or modify the part of the macro : msgbox("Chart: " & Objects(X).GetObjectId)... but it doesn't work!!

Thank You!!

Marco

Re: Export Image to insert in ppt

IAMDV — Thu, 29 Sep 2011 10:18:37 GMT

Hi Marco,

Yes, you are right. My previous code only works for the Chart objects. I could see that you have changed it from GetGraphs to GetSheetObjects.

I have solved the first problem from your post. Here is the updated code...

Sub Export_All_Chart_Images()

For i = 0 To ActiveDocument.NoOfSheets - 1

Set MySheet = ActiveDocument.GetSheet(i)

MyCharts=MySheet.GetSheetObjects

For X =lbound(MyCharts) to ubound(MyCharts)

ActiveDocument.Sheets(i).Activate

msgbox("Chart: " & MyCharts(X).GetObjectId)

Set obj = ActiveDocument.GetSheetObject(MyCharts(X).GetObjectId)

IF Obj.GetObjectType >= 10 AND Obj.GetObjectType =< 16 Then

obj.ExportBitmapToFile "C:\Users\deepak.vadithala\Desktop\All\Test\Images_" & Right(MyCharts(X).GetObjectId, 4) & ".bmp"

End if

Next

End Sub

I have used the GetObjectType Method from SheetObject Class. This method returns a unique value for each object. Below is the list...

0=Unknown
1=ListBox
2=MultiBox
3=StatisticsBox
4=TableBox
5=Button
6=TextObject
7=CurrentSelections Box
8=InputBox
9=Line/ArrowObject
10=PivotTable
11=StraightTable
12=BarChart
13=PieChart
14=ScatterChart
15=LineChart
16=ComboChart
17=CustomObject
18=BookmarkObject
19=Slider/CalendarObject
20=GridChart
21=RadarChart
22=GaugeChart

So, I have used this method and evaluated the object type. This line of code does the trick...

IF Obj.GetObjectType >= 10 AND Obj.GetObjectType =< 16 Then

However, I am unable to fix the MsgBox issue. If I comment out the MsgBox line of code then QV just exports the ActiveSheet objects instead of looping through all the sheets in the document. I have to dissect the code and need to do some testing. Meanwhile, you can still use the above code.

Good luck!

Cheers - DV

Re: Export Image to insert in ppt

Thu, 29 Sep 2011 14:01:16 GMT

Hi DV,

Great idea to use GetObjectType! Now i can export only this kind of object!!

Thank you!!!

Marco

PS:

Then i need to exclude (not export) some table or chart that are not important... so i modify the length of Object.id of the object that i want to export.

For ex all name length = 10

I modify:

obj.ExportBitmapToFile "C:\Users\deepak.vadithala\Desktop\All\Test\Images_" & Right(MyCharts(X).GetObjectId, 10) & ".bmp"

and the macro export only the type of object that you selected and only the table or chart with Object.id=10!

The macro doesn't export other one because the length = 4 ( ex CH01)!

Re: Export Image to insert in ppt

IAMDV — Thu, 29 Sep 2011 14:05:32 GMT

Marco - I am glad it works now. I still need to see the implications of removing MsgBox...

You are welcome. Being honest, I love your questions because I learn a lot trying to answer them. So thank you for this .

Cheers - DV

Re: Export Image to insert in ppt

angelaecheverri — Wed, 18 Apr 2012 19:03:27 GMT

Hi...

There are a way to export all charts in excel in the same book?

or at least export all the charts to excel in the same rutine?

Thanks in advantage.

Re: Export Image to insert in ppt

Sun, 01 Jun 2014 08:13:51 GMT

How can I use ur code in my code.

I want dynamically allocate aSheetObj array(highlighted line in yellow color) all object ids

sub launchXL

set oXL=CreateObject("Excel.Application")

oXL.visible=True

oXL.Workbooks.Add

aSheetObj=Array("TB01","CH01")

for i=0 to UBound(aSheetObj)

oXL.Sheets.Add

Set oSH = oXL.ActiveSheet

oSH.Range("A1").Select

Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))

obj.CopyTableToClipboard True

oSH.Paste

sCaption=obj.GetCaption.Name.v

set obj=Nothing

oSH.Rows("1:1").Select

oXL.Selection.Font.Bold = True

oSH.Cells.Select

oXL.Selection.Columns.AutoFit

oSH.Range("A1").Select

oSH.Name=left(sCaption,30)

set oSH=Nothing

set oXL=Nothing

end sub

i want to use your code in my array here i use manually insertion of objects id into aSheetobj array
but need array should contains all objects id dynamically like your code reading all objects of different sheets.

plz rply ASAP

thanks in advance.........