Export Image to insert in ppt

[#1]

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

[IAMDV]

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

[#3]

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

[IAMDV]

Hi Marco,

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

Cheers - DV

[#5]

Thank you! Great!

[IAMDV]

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

    Next   

End Sub

I hope this helps!

Cheers - DV

[#7]

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"

            Next

    Next   

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

[IAMDV · Accepted solution]

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

    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

[#9]

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)!

[IAMDV]

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