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

[#1]

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

[jonathandienst]

Hi

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.

eg

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

[AlexOmetis]

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.

[#4 · Accepted solution]

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

[#5]

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.

[#6]

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.

[#7]

Hi,

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

[#8]

Wich type of office de do you have?

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