topic VBscript: transpose straight table and export to excel? in QlikView

VBscript: transpose straight table and export to excel?

ernstblaauw — Fri, 22 Oct 2010 13:28:45 GMT

Hi all,

I want to automate the export of a straight table to excel. Thus, I wrote a script that exports the straight table to Excel and saves the file. That's no problem.

However, I also want to transpose the table. I see two options:

- transpose with "paste special" in Excel

- transpose the table in QV

The first option does not suceed, as Paste Special needs arguments to transpose, and QV gives an error if this VBscript is used.

The second options looks more promising. Inside the script, I transpose the table with TableProperties.Horizontal=true. However, if I copy the transpose cell to the clipboard and paste in Excel, the data is not transposed anymore. I use "tabel.CopyTableToClipboard True".

Can someone help me to export a straight table and have the table transposed in Excel?

VBscript: transpose straight table and export to excel?

suniljain — Fri, 22 Oct 2010 13:42:30 GMT

'To Excel Generate

set

obj = ActiveDocument.GetSheetObject("CH703"

)

"E:\Qlikview\Prima\PPT\Department_Loadinng.xls"

set

obj = ActiveDocument.GetSheetObject("CH704"

)

"E:\Qlikview\Prima\PPT\Top_5_Project.xls"

set

obj = ActiveDocument.GetSheetObject("CH705"

)

"E:\Qlikview\Prima\PPT\0HrsProject.xl

VBscript: transpose straight table and export to excel?

ernstblaauw — Fri, 22 Oct 2010 13:51:44 GMT

Hi Sunil Jain,

Yhanks for your help. However, I cannot read your post as it seems a part is not shown here. Can you post it again?

Thanks, Ernst

VBscript: transpose straight table and export to excel?

suniljain — Fri, 22 Oct 2010 13:54:44 GMT

'To Excel Generate
set obj = ActiveDocument.GetSheetObject("CH703")
obj.ExportEx "E:\Qlikview\Prima\PPT\Department_Loadinng.xls",5
set obj = ActiveDocument.GetSheetObject("CH704")
obj.ExportEx "E:\Qlikview\Prima\PPT\Top_5_Project.xls",5
set obj = ActiveDocument.GetSheetObject("CH705")
obj.ExportEx "E:\Qlikview\Prima\PPT\0HrsProject.xl

VBscript: transpose straight table and export to excel?

ernstblaauw — Fri, 22 Oct 2010 14:11:31 GMT

The table (transposed in QV using TableProperties.Horizontal=true) is still un-transposed in Excel, so I guess your ExportEx is not a solution. Do you have another idea?

Re: VBscript: transpose straight table and export to excel?

Tue, 03 Sep 2013 08:38:56 GMT

Hi,

I am also facing the same issue.

Can you please help me with the solution incase you gt it correctly?