Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
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
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
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
Hi Marco,
I'll create an example this afternoon and I'll get back to you ASAP.
Cheers - DV
Thank you! Great!
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
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
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...
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
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)!
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