topic Re: How to store report in excel format like we can able to store in csv format. in QlikView

How to store report in excel format like we can able to store in csv format.

Thu, 30 May 2013 15:51:47 GMT

Hi All,

Please help me in this.

Suppose following example:

ABC:

load service,customer,address from ABC.qvd;

Store ABC into ABC.csv;

This will generate the data in csv format.

i want the data store in xcel format.

I tried this Store ABC into ABC.xls;

But this doesnt work.

Please help

Re: How to store report in excel format like we can able to store in csv format.

jonathandienst — Thu, 30 May 2013 15:59:03 GMT

It is not possible use the script STORE command to store data in excel format. However, if you store it in CSV format, but name the file with an XLS extension, Excel will open the file normally (after warning you that this is not a proper Excel file format). As the file is CSV "under the skin", you cannot store any formatting information.

STORE MyTable into Export.xls (txt);

You can, of course, export to Excel through the Export to Excel option, or a macro from the front-end, but not in script.

Hope that helps

Jonathan

Re: How to store report in excel format like we can able to store in csv format.

AlexOmetis — Thu, 30 May 2013 16:14:55 GMT

Unfortunately this is not possible in script.

As Jonathan says, you can do this in the UI or via a macro.

Alternatively you could use something like NPrinting to schedule extracts to Excel from specific charts in your document. It also manages distributing these to different people & locations if you want, applying different selections as per your configuration.

Re: How to store report in excel format like we can able to store in csv format.

Thu, 30 May 2013 16:19:23 GMT

Why dont you use a macro thats its been executed after the reload

Coul be something la this

Sub ExcelExpwCaption

'Set the path where the excel will be saved

Set fso = CreateObject("Scripting.FileSystemObject")

'CAN BE RELATIVE

filePath = "PATH WHERE YOU WANT TO STORE IT"

'Create the Excel spreadsheet

Set excelFile = CreateObject("Excel.Application")

excelFile.Visible = true

'Create the WorkBook

Set WorkBook = excelFile.WorkBooks.Add

'Create the Sheet

Set Sheet1 = WorkBook.WorkSheets(1)

'NAME OF THE SHEET

Sheet1.Name = "NAME OF THE SHEET 1 "

'Get the chart we want to export

Set tableToExport = ActiveDocument.GetSheetObject("NAME OF THE TABLE YOU WANT TO EXPORT")

Set chartProperties = tableToExport.GetProperties

tableToExport.CopyTableToClipboard true

'Get the caption

chartCaption = tableToExport.GetCaption.Name.v

'MsgBox chartCaption

'Set the first cell with the caption

Sheet1.Range("A1") = chartCaption

'Paste the rest of the chart

Sheet1.Paste Sheet1.Range("A1")

excelFile.Visible = true

'Save the file and quit excel

WorkBook.SaveAs filePath, 50

WorkBook.Close

excelFile.Quit

'Cleanup

Set WorkBook = nothing

Set excelFile = nothing

End Sub

Re: How to store report in excel format like we can able to store in csv format.

Mon, 03 Jun 2013 04:30:49 GMT

HI Nacifu,

I tried to use the macro but it is giving me error that:

ActiveX component can't create object:excel application.

i choose the Requested module security as system access and current local security as allow system access.

Please suggest.

Re: How to store report in excel format like we can able to store in csv format.

Mon, 03 Jun 2013 07:57:57 GMT

I have try it an it works good for me, here is an atach of how you have to set the security mode

and the example with the macro.

Re: How to store report in excel format like we can able to store in csv format.

Mon, 03 Jun 2013 12:35:52 GMT

Hi,

I tried by using Exceltest.qvw , also it is also giving me the same error.

Re: How to store report in excel format like we can able to store in csv format.

Mon, 03 Jun 2013 12:56:20 GMT

Wich type of office de do you have?

This .qvw work with office 2007 and 2010 both 32 bits