topic Re: Export data to excel - Automatically or in script in QlikView

Export data to excel - Automatically or in script

Tue, 14 Aug 2012 14:49:42 GMT

How would I go about automating exportign data to excel from multiple tables. I have a complex dashboard that profiles customers and say after I selected a specific group of customers I wanted to export the data for all charts to excel, maybe to develop a powerpoint presentation as printed reports are not that good in Qview

Can someone share some sample code,and also where would I type that code into and how would it launch? I see that a button can have actions but the export does not seem to allow excel, it appears the button can trigger code - is this how I would do this?

Re: Export data to excel - Automatically or in script

Tue, 14 Aug 2012 17:08:33 GMT

This link may be a good starting point. You can adjust the code to export your reports as needed.

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

Re: Export data to excel - Automatically or in script

Tue, 14 Aug 2012 18:37:38 GMT

Thanks,

I was able to do it in a test way, as follows which may be similar to what you suggeted (I can't see the qvw as for now I have an evaluation version). The code is modified abit from somethign I found, it adds a prompt for the location to save it

Sub to_excel

Dim wsname

Set XLApp = CreateObject("Excel.Application")

XLApp.Visible = TRUE

Set XLDoc = XLApp.Workbooks.Add

wsname = ""

wsname = "Sheet1"

strFileName = InputBox("Enter Path and File Name (from My Documents)", "Enter Path and Filename", defaultValue)

strFileName = "C:\Documents and Settings\ewnym5s\My Documents\" & strFileName

ActiveDocument.ClearAll

XLDoc.Sheets(wsname).activate

ActiveDocument.GetSheetObject("CH23").CopyTableToClipboard true

XLDoc.Sheets(wsname).Range("A" & 1).Select

XLDoc.Sheets(wsname).Paste

ActiveDocument.GetApplication.WaitForIdle

wsname = "Sheet2"

XLDoc.Sheets(wsname).activate

ActiveDocument.GetSheetObject("CH34").CopyTableToClipboard true

XLDoc.Sheets(wsname).Range("A" & 1).Select

XLDoc.Sheets(wsname).Paste

ActiveDocument.GetApplication.WaitForIdle

XLDoc.SaveAs(strFileName)

XLDoc.Close

end sub

I have a further question that is really a visual basic question,

I do not want to have 25 or 30 blocks of code one for each chart - that is what for next loops were invented for

I do not know in vb if you can and how to cycle through values that are not sequential (from a list like CH01, CH03, CH04) is that possible in your knowledge? can i ask how (as opposed to searching for it - whihc i admit is lazy)

Also, in qview, my charts are in multiple sheets, do I need to switch sheet to reference charts in another one or will qview find CHXX wherever it may be

Thanks