topic Re: Help - Scrot to export a Graphic (Pivot Table) in QlikView

Help - Script to export a Graphic (Pivot Table) automatically

Fri, 21 Dec 2012 10:21:25 GMT

Hello,

Is there any script to export an specific Graphic (Pivot Table) in a specified workspace?

Thanks!

Dom

Re: Help - Scrot to export a Graphic (Pivot Table)

Fri, 21 Dec 2012 12:12:26 GMT

you mean

how to export object (for example pivot table) to Excel via Macro and save file to specific directory?

Re: Help - Scrot to export a Graphic (Pivot Table)

Fri, 21 Dec 2012 12:31:42 GMT

Yes!

The dashboard I'm bulding will get data from one sorce, traeat in in form of a pivot table, export it to a new table, then load this new table and use it with other sources... it's kinda messy, Iknow.

There's a way to do this?

Thanks!

Re: Help - Scrot to export a Graphic (Pivot Table)

chematos — Fri, 21 Dec 2012 17:13:38 GMT

Try something like this:

sub ExportExcel

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add

ActiveDocument.GetSheetObject("CH01").CopyTable True

objExcel.ActiveSheet.Paste

objExcel.Export 'C:/path'

end sub

Re: Help - Scrot to export a Graphic (Pivot Table)

Wed, 26 Dec 2012 14:20:26 GMT

I can't make it work... I changed the path* inserted the code on "Main" script and put a CALL function right after it... nothing happens.

I tought it was b/c the script wasn't understanding the end of the function, so I put a ; befoe end sub. Nothing happens.

Any idea what I'm might be doing wrong?

*Path: C:\Users\re09083g\Desktop\EOG Qlikview_12-12\test.xls

Re: Help - Scrot to export a Graphic (Pivot Table)

chematos — Wed, 26 Dec 2012 14:39:42 GMT

Hi,

you have to do the macro in the Module Editor and change the path and the Obejct ID CH01 by your object ID.

Try this and let me know,

regards

Re: Help - Scrot to export a Graphic (Pivot Table)

Wed, 26 Dec 2012 17:30:38 GMT

Ops... now it makes a lot more sense, but I get the error below when I hit the "Test" button:

"O objeto não dá suporte para a propriedade ou método: 'ActiveDocument.GetSheetObject(...).CopyTable' " (something like: Object dosn't support proprety or method "ActiveDocument.GetSheetObject(...) Copy Table)

I'm using QV11, Excel 2010. Already put "Requested Module Security" and "Current Local Security" to allow System Acccess.

Any ideas?

Re: Help - Scrot to export a Graphic (Pivot Table)

chematos — Wed, 26 Dec 2012 17:40:23 GMT

Sorry, is CopyTableToClipboard True

You need to Allow Local Security to run macros

Re: Help - Scrot to export a Graphic (Pivot Table)

Wed, 26 Dec 2012 17:59:53 GMT

No support to .Export... also tried SaveAs with he same effect; even tought Save works but it its kind'a pointless to macro.

Re: Help - Scrot to export a Graphic (Pivot Table)

chematos — Wed, 26 Dec 2012 18:04:06 GMT

Look at User Poperties, Security menu, one of that options have to allow access to filesystem, also look at your user provileges of the folder you want to use to export

Re: Help - Scrot to export a Graphic (Pivot Table)

Wed, 26 Dec 2012 18:31:09 GMT

Don't ask me why, but when I use a Workbook instead as a object instead of Excel, it works:

---

sub ExportAnual

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set WB = objExcel.Workbooks.Add
ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard True

WB.ActiveSheet.Paste
WB.SaveAs "C:\path\Ranking\test3.xls"
WB.Close

End Sub

---

Just need to know how overwrite in Qlikview's VB Script.

Thanks!

Re: Help - Scrot to export a Graphic (Pivot Table)

chematos — Wed, 26 Dec 2012 18:46:01 GMT

This is not working to export??

objExcel.ActiveSheet.Paste

objExcel.Export

I use that to overwrite the excel everytime, may be exists a function to clear ActiveSheet before copy the table... I really don´t know.

You're welcome, I'm glad you get a solution but I also don't know why is working like that...