topic Re: Export multiple object to multiple sheets - Excel in QlikView

Export multiple object to multiple sheets - Excel

Mon, 09 Jun 2014 16:57:53 GMT

Hi everyone,

I use this macro below to export multiple object to excel. What I need is to add some code to export more object to other sheet in excel. I would appreciate your help

sub test

'Set the path where the excel will be saved

filePath = "C:\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

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

Re: Export multiple object to multiple sheets - Excel

Gysbert_Wassenaar — Mon, 09 Jun 2014 17:00:43 GMT

See Export Qlikview objects to multiple Excel sheet

Re: Export multiple object to multiple sheets - Excel

Mon, 09 Jun 2014 17:04:09 GMT

Tnx man, but I need to calculate number of rows and to set my next object to start two or three rows below previous one. Like in my macro above.

Re: Export multiple object to multiple sheets - Excel

Gysbert_Wassenaar — Mon, 09 Jun 2014 17:14:22 GMT

What I need is to add some code to export more object to other sheet in excel.

So you are trying to find the other sheet by finding the first unused row on the current sheet?

Try using the .End method:

Const xlUp = -4162

curSheet.Range("a1").End(xlUp).Offset(1, 0).Select

Re: Export multiple object to multiple sheets - Excel

Mon, 09 Jun 2014 17:25:05 GMT

I want to create new sheet and to put on him some new object, and then to create new sheet and to put on him some new object....