send entire report to excel

[#1]

Hi,

Inside a .qvw document, I have created a document report. (Reports --> Edit Reports)

Is there any option to send this report to excel?

Regards,

Anju

[#2]

Is there any way to send the entire report to an excel sheet?

[#3 · Accepted solution]

Hi All,

This is the code I used.

I got the basic code from this post by  Miguel Angel Baeyens

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

sub test

'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

     'first chart object

     Set curSheet = curWorkBook.WorkSheets(1)

'loop

chartArray = Array("CH06","CH09", "CH10")

usedRows=0

For Each chart In chartArray

   Set i = ActiveDocument.GetSheetObject(chart)

    chartCaption = i.GetCaption.Name.v

    curSheet.Cells(usedRows+1, 1)=chartCaption

     i.CopyTableToClipboard true

     curSheet.Cells(usedRows+3, 1).Select

     curSheet.Paste

     usedRows=curSheet.UsedRange.Rows.Count+3 --->function to get the first unused row

Next

'loop end

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

Regards,

Anju

[Anonymous]

Hi,

Thanks for the code.

I am getting following error.

Object required: 'i'

and

Paste method of Worksheet class failed

Can you share me the qvw file itself.

thanks.

[#5]

Hi Saurabh,

Did you paste the entire macro?

[Anonymous]

Yes.

I had to comment '-->function to get the first unused row

I get the error as Copy to Clipboard failed. And "Object required 'i'"

I cannot share the qvw as it has customer data. I will try and see if I can share the qvw.

[Anonymous]

Please find attached the qvw.

Let me know if you can solve the error.

[#8]

the problem is with this line..

chartArray = Array("CH06","CH09", "CH10")

CH06...etc are names of sheet objects...the qvw you shared does not have them.

So,

in your case..the statement should be..

chartArray = Array("CH01","CH02", "TB01").

Hope this helps.

Regards,

Anju

[Anonymous]

Sorry to have not included that in code which I shared.

I had done that correction after which I got the "Copy to Clipboard failed" and above two errors also.

There is no consistency in the error, that is, sometimes I get them and sometimes I dont...

What other parameters can affect Qlikview performance?

[#10]

pls share your file