topic Re: Export to Excel in QlikView

Export to Excel

Mon, 01 Aug 2011 09:18:22 GMT

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?

Export to Excel

rahulgupta — Mon, 01 Aug 2011 09:27:05 GMT

Hi,

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

Regards

Re: Export to Excel

Mon, 01 Aug 2011 09:34:53 GMT

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

Re: Export to Excel

Mon, 01 Aug 2011 10:27:56 GMT

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..

Re: Export to Excel

Miguel_Angel_Baeyens — Mon, 01 Aug 2011 10:44:25 GMT

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

Re: Export to Excel

Tue, 02 Aug 2011 05:11:49 GMT

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?

Re: Export to Excel

Miguel_Angel_Baeyens — Wed, 03 Aug 2011 08:43:29 GMT

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

Re: Export to Excel

Thu, 04 Aug 2011 11:23:11 GMT

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

Re: Export to Excel

Miguel_Angel_Baeyens — Thu, 04 Aug 2011 11:28:58 GMT

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"

filePath = "C:\Test.csv"

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

Re: Export to Excel

Fri, 05 Aug 2011 06:59:31 GMT

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

Re: Export to Excel

Miguel_Angel_Baeyens — Fri, 05 Aug 2011 07:35:07 GMT

Use VBScript string functions to split the caption according to a fixed length, for example

     'Get the caption
     chartCaption1 = Left(tableToExport.GetCaption.Name.v, 10)
     chartCaption2 = Right(tableToExport.GetCaption.Name.v, 10)
     'Set the first cell with the caption
     curSheet.Range("A1") = chartCaption1
     curSheet.Range("B1") = chartCaption2

Now I set two chartCaption variables, the first of which with the first 10 characters starting from left, and the second the 10 starting from right. The I copy these values in the first cell (top left A1) and the second (top, left +1, B1).

Adapt this to your captions.

Hope that helps.

Miguel Angel Baeyens

But, I want to do something more.

I want to paste a second chart after the first one.

So, I used this function to find the next empty cell and paste the chart.

Range("A" & Rows.Count).End(xlUp).Offset(1).Select

curSheet.Paste

But it throws error 'Object Required:Rows'

Re: Export to Excel

Fri, 08 Mar 2013 11:58:24 GMT

Hi All,

I used this.

usedRows=curSheet.UsedRange.Rows.Count

This fetches the count of used rows.

I have put the entire code here:

http://community.qlik.com/message/319742#319742

Regards,

Anju

Re: Export to Excel

Anonymous — Tue, 18 Jun 2013 09:58:20 GMT

Hi Miguel,

I used your above code and it works fine.

Now what I want to do is to export some charts to excel one below the other:

I tried following

dim a,text,tm

a=Array("CH02","CH04","CH06","CH07")

for i = 0 to 4

b=a(i)

i=i+1

But I am not able to add it accurately in the code.

Moreover I want the charts below the previous charts.

Where to add

Range("A" & Rows.Count).End(xlUp).Offset(1).Select

curSheet.Paste

usedRows=curSheet.UsedRange.Rows.Count

Sorry all confused. I am trying at my end.

Thanks for helping out.