Export to Excel

[#1]

Hi All,

I am exporting straight table data to excel.

Based on the dates entered in input box, data is displayed in straight table.

Is there any way to export the dates also?

[rahulgupta]

Hi,

Add that Date Field as the First Column of the Straight Table.

Regards

[#3]

Hi,

As Rahul suggested, if you don't have date field in your straight table then add the date field to you table and export.

Your description on problem is not so clear, above solution is purely on guess.

It would be nice if you could give us more details on you requirement.

- Sridhar

[#4]

Hi ..

Thanks for the replies..

I have an input box for entering start date and end date and displaying records in staraight table

between the date ranges(inputted dates).

vStartDate and vEndDate are the input box variables.

I am using below expression for displaying the records.

only({<[bkd-dt]={">=$(vStartDate)<=$(vEndDate)"}>}[bkgno])

My requirement is,when i export the straghit table data to excel,

I need to display the date ranges inputted.That is, vStartDate and vEndDate values.

No need to display the date ranges in  table.

Thanks..

[Miguel_Angel_Baeyens]

Hi,

You can label your expressions (or at least one of them) with the date range, using this in the label part in the chart properties:

='Sales from ' & $(vStartDate) & ' to ' & $(vEndDate)

Make sure the $() returns the date in the required format, otherwise you can use Date(vStartDate) instead. Or if you are using a pivot table, set something like the above in the Total label in the Presentation tab in the chart properties.

Is that what you are looking for?

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#6]

Hi Miguel,

Thank you very much for the reply.

It is a  helpful solution.But,I have 40 columns in my straight table.So if i could include date range info as header or footer display(After excel exporting)it would be more good.Could you please let me know if any possible way to incorporate this modification?

I have checked the option Settings--->User Preferences---->Export--->On BIFF Exports,to include the selection criteria.Is there any way to include the input box value along with this?

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

There's not as far as I know. But you can create a macro to export your chart with the caption and save it to a file. Ir requires more code, but it's worth trying

Sub ExcelExpwCaption
     'Set the path where the excel will be saved 
     filePath = "C:\Test.xls"
     'Create the Excel spreadsheet 
     Set excelFile = CreateObject("Excel.Application")
     excelFile.Visible = true
     'Create the WorkBook
     Set curWorkBook = excelFile.WorkBooks.Add
     'Create the Sheet
     Set curSheet = curWorkBook.WorkSheets(1)
     'Get the chart we want to export
     Set tableToExport = ActiveDocument.GetSheetObject("CH01")
     Set chartProperties = tableToExport.GetProperties
     tableToExport.CopyTableToClipboard true
     'Get the caption
     chartCaption = tableToExport.GetCaption.Name.v
     'MsgBox chartCaption
     'Set the first cell with the caption
     curSheet.Range("A1") = chartCaption
     'Paste the rest of the chart
     curSheet.Paste curSheet.Range("A2")
     excelFile.Visible = true
     'Save the file and quit excel
     curWorkBook.SaveAs filePath
     curWorkBook.Close
     excelFile.Quit
     'Cleanup
     Set curWorkBook = nothing
     Set excelFile = nothing
End Sub

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#8]

Hi Miguel,

I am getting error like

ActiveX component can't create object: 'Excel.Application' .

There is no Microsoft Excel installed in my machine.Only Open Office is there. Is it because of that?

Thanks,

Soumya

[Miguel_Angel_Baeyens]

Yes, you need to have Excel in order to make the macro work. I don't know if there are any ActiveX controls to create OpenOffice Calc spreadsheets, and if they do exist, what is their API. I'd try to install Excel if that is the format you want for your documents.

You may change the macro so it saves on a text file comma separated that will be easily imported into Calc, where the first line of that file has the caption and from the second onwards, all your data. However, quotes and other special characters may cause issues when importing.

Regards,

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

EDIT: Actually, saving to CSV instead of XLS should be as easy as changing this line in the macro above

filePath = "C:\Test.xls"

to

filePath = "C:\Test.csv"

Although you need to have Excel installed in order to create the workbook.

[#10]

Hi Miguel,

Thank you very much...The problem has been solved..

Could you please let me know one more thing? Is there any way to split up the caption? Now My captiion looks like

"Daily Summary Report     From  1/1/2007  To  8/5/2011     Product Type : HTL"

If I could include "Product Type : HTL" in the second cell,it would be better..Please suggest

Thanks,

Soumya