topic Export to Excel - Macro in QlikView

Export to Excel - Macro

Mon, 09 Jun 2014 09:13:14 GMT

Hi everyone,

I need to to export multiple object from QlikView into multiple sheets in Excel. I know that I need macro for this, and I found some discussion on this subject, but I didnt find how to calculate number of row in each object and to add other object for example two row below?

I'm grateful on any help

Re: Export to Excel - Macro

tresB — Mon, 09 Jun 2014 09:24:35 GMT

If it is multiple objects in multiple sheets, why would you need to calculate rows to place another object below it?

Re: Export to Excel - Macro

Mon, 09 Jun 2014 09:28:42 GMT

Because I dont know in advance number of rows in each object. I need to export 100 object (straight table, pivot table, text object) in more then 10 sheets in Excel, and some of them will dynamicly change over time.

Re: Re: Export to Excel - Macro

tresB — Mon, 09 Jun 2014 09:35:43 GMT

Did you find this?

Re: Export to Excel - Macro

Mon, 09 Jun 2014 09:54:27 GMT

Yes, and this works fine, but he set that first object start at A1 cell and next at A20 cell..., and in my case I want to count number of rows in fisrt object and to set second object to start at two rows below.

Re: Export to Excel - Macro

Mon, 09 Jun 2014 10:01:29 GMT

Code below count number of rows and add next object 3 rows below, but this code add object only on one sheet.

Can someone help me to add some code that will make another sheet and add some object on it?

sub test

'Set the path where the excel will be saved

filePath = "C:\Users\stomasevic\Desktop\Test.xlsx"

'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("CH421","CH11", "CH12")

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

'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

Re: Export to Excel - Macro

tresB — Mon, 09 Jun 2014 10:02:49 GMT

Coming back to your question: how to get no os rows: Use GetNoOfRows like

set chart = ActiveDocument.Sheets("Main").CreateStraightTable

chart.AddDimension "Class"

chart.AddDimension "Member"

chart.AddExpression "only(MemberComment)"

msgbox("number of class members = "&chart.GetNoOfRows)