topic Re: Macro to export table to excel in QlikView

Macro to export table to excel

Anonymous — Wed, 19 Feb 2014 16:10:10 GMT

Hi community,

I have a macro to export a table to excel.

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("CH02") ' give the name of your object instead of CH06

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

'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

when I run my test macro, it works well, but it opens module mode at the end.

Why? How can I fix it?

Thanks

Re: Macro to export table to excel

jerem1234 — Wed, 19 Feb 2014 20:34:44 GMT

It means that there's something wrong with macro. My guess is that you might be trying to export a chart that doesn't exist.

Hope this helps!

Re: Macro to export table to excel

Anonymous — Wed, 19 Feb 2014 20:39:01 GMT

Thanks my friend.

I fixed it .

I commented path and it works.

I marked as answered. It should be a couple comments early.

Re: Macro to export table to excel

kris_vliegen — Wed, 26 Nov 2014 11:22:59 GMT

I have a question about this macro.

I've imported this code in my dashboard.

I've implemented it that if I reload my dashboard at the end he wil generate the excel in a folder.

If I reload my dashboard on my laptop it is working fine.

But if I do it on the server it isn't working.

I think I have to install Excel on the server.

But is it working if I schedule the job in the management-console after I installed Excel on the server?

Regards,

Kris