topic Export Graphic Chart to Excel? in QlikView

Export Graphic Chart to Excel?

gradiske — Wed, 16 Mar 2011 13:07:29 GMT

Hi guys, i got a question.

Im trying to do something really simple i guess...

A simple code that its working for me:

sub Printeste

set obj = ActiveDocument.GetSheetObject(Status)

conteudo = Day(Date) & "-" & Month(Date) & "-" & Year(Date)

obj.ExportBiff("c:\qlikview\test_"&conteudo&".xls")

end sub

since "status" is a function that calls the sheet that is not minimized. With normal charts it is working good. But my problem comes when i have to export Graphic Charts...

ive been searching for a while, here in qlikview community also, that i have to use "ExportBitmapToFile"... but, i have to save it as jpg or bmp, right?

So my question is, there's a way to use ExportBitmapToFile and 'merge' this image to a XLS?

This is my first macro actually, so, my next step(at least i was pretending) is to call 2 charts to a XLS file, a normal chart and a graphic chart. It would be awesome if there's a way to do that.

Export Graphic Chart to Excel?

matt_crowther — Wed, 16 Mar 2011 13:41:21 GMT

This process should help you out (not created by me):

http://www.qlikblog.at/971/qliktip-32-exporting-multiple-objects-single-excel-document/

or from the Share Qlikviews:

http://community.qlik.com/media/p/168157.aspx

Hope that helps,

Matt - Visual Analytics Ltd

Export Graphic Chart to Excel?

gradiske — Wed, 16 Mar 2011 14:07:15 GMT

Hi Matt, ive seen this idea before(sorry not to mention it) and i believe that it works for my purpose.

But what i'm trying to do is something automatic... Thats why im using the "status" function, to call the 'active' chart(not minimzed). I know that Qlikview already do that by default, but i want to do the exact same thing with multiple charts. And in this case that you posted, all charts are pre defined.

I was wondering if its possible to do, maybe an 'if' state in my macro... something like

if obj.GetObjectType = "10" or "11"

obj.ExportBiff("c:\qlikview\teste_"&conteudo&".xls")

else

obj.ExportBitmapToFile("c:\qlikview\teste_"&conteudo&".bmp")

and then merge those files

Im not a expert in macros, but i believe i can edit some things on that code to get what i want(it will take some time hehe)...

I dont know if i am being clear... but thanks for your answer =D

Export Graphic Chart to Excel?

gradiske — Thu, 17 Mar 2011 12:57:57 GMT

Ok guys, i've found an example and changed it a little to make it automatic. here it goes:

sub fazerxl

Set Doc = ActiveDocument

Set DocProp = Doc.GetProperties

Directory = DocProp.MyWorkingDirectory

Doc.GetApplication.Refresh

Set AppExcel = CreateObject("Excel.Application")

AppExcel.Visible = False

AppExcel.WorkBooks.Add()

set s=ActiveDocument.ActiveSheet

objs=s.GetSheetObjects

for i=lbound(objs) to ubound(objs)

if not(objs(i).IsMinimized) then

select case objs(i).GetObjectType

case 10,11,12,13,14,15,16,20,21,22,27,28,37

set objInt = objs(i)

id = replace(objInt.GetObjectId,"Document\","")

Set plan = Doc.GetSheetObject(id)

ActiveDocument.ClearAll false

ActiveDocument.getApplication.sleep 5000

AppExcel.Sheets.Add()

AppExcel.ActiveSheet.Name = id

AppExcel.ActiveSheet.Cells(1,1).Activate

select case objs(i).GetObjectType

case 10,11

plan.CopyTableToClipboard True

case 12,13,14,15,16,20,21,22,27,28,37

plan.CopyBitMapToClipboard

end select

AppExcel.ActiveSheet.Paste

AppExcel.ActiveSheet.Columns("A").AutoFit

ActiveDocument.getApplication.sleep 5000

end select

end if

salvar = Directory & "\testes" & Day(Now) & "-" & Month(Now) & "-" & Year(Now) & "-" & Hour(Now) & ".XLS"

AppExcel.ActiveSheet.SaveAs (salvar)

AppExcel.Quit

end sub

So, the idea is... get all charts not minimized, and put every each one in a particular sheet in the Excel.

It was working good so far doing my tests... but, sometimes i got an error on "AppExcel.ActiveSheet.Paste"

Im trying here doing some tests why in some charts i got this error... but if anyone got an idea about it... let me know, ok =D

Re: Export Graphic Chart to Excel?

Wed, 14 Dec 2016 10:42:09 GMT

Hi All,

Just a quick question in the reverse direction. Is it possible to export a chart from EXCEL to QLIKVIEW?

I suppose this is not a place ask this question yet awaiting answers for the same.

Thanks.,

Sam