Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Export to Excel

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?

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

21 Replies
rahulgupta
Partner - Creator III
Partner - Creator III

Hi,

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

Regards

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

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

Not applicable
Author

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.

Not applicable
Author

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