Qlik Community

Ask a Question

QlikView Administration

Discussion Board for collaboration on QlikView Management.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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

View solution in original post

15 Replies
Not applicable
Author

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

Not applicable
Author

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

View solution in original post

saurabh_pandit
Creator
Creator

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
Author

Hi Saurabh,

Did you paste the entire macro?

saurabh_pandit
Creator
Creator

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

Please find attached the qvw.

Let me know if you can solve the error.

Not applicable
Author

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

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
Author

pls share your file