Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

send entire report to excel

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

1 Solution

Accepted Solutions
Not applicable

Re: send to excel option for document/user report

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

15 Replies
Not applicable

Re: send to excel option for document/user report

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

Not applicable

Re: send to excel option for document/user report

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

saurabh_pandit
Contributor

Re: send to excel option for document/user report

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.

Not applicable

Re: send to excel option for document/user report

Hi Saurabh,

Did you paste the entire macro?

saurabh_pandit
Contributor

Re: send to excel option for document/user report

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.

saurabh_pandit
Contributor

Re: send to excel option for document/user report

Please find attached the qvw.

Let me know if you can solve the error.

Not applicable

Re: send to excel option for document/user report

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

saurabh_pandit
Contributor

Re: send to excel option for document/user report

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?

Not applicable

Re: send to excel option for document/user report

pls share your file

Community Browser