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

Export multiple object to multiple sheets - Excel

Mon, 09 Jun 2014 13:01:57 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

whiteline — Mon, 09 Jun 2014 13:12:14 GMT

Hi.

There is a list of objects to export in the line

chartArray = Array("CH421","CH11", "CH12")

You can simply add the id of objects that you want to export.

To get the object id, open object properties general tab, the top right corner.

Re: Export multiple object to multiple sheets - Excel

Mon, 09 Jun 2014 13:18:23 GMT

I know that, but I want to add object ("CH13","CH14","CH15") on sheet 2

Re: Export multiple object to multiple sheets - Excel

whiteline — Mon, 09 Jun 2014 15:36:33 GMT

The sheet is assigned by number:

Set curSheet = curWorkBook.WorkSheets(1)

You can set the sheet #2 as curSheet and repeat (copy) the loop with appropriate objects.

Re: Export multiple object to multiple sheets - Excel

Mon, 09 Jun 2014 15:49:22 GMT

I tried this but he didnt copy object to another sheet

Re: Export multiple object to multiple sheets - Excel

Mon, 09 Jun 2014 15:50:29 GMT

You can try and send me macro if you succeed

Re: Export multiple object to multiple sheets - Excel

whiteline — Tue, 10 Jun 2014 06:44:55 GMT

Hi.

You have to make the sheet 'active' to perform copypaste.

Add curSheet.Activate before the loop.

It's really a VBA question.

You can find more information in MSDN, StackOverflow and other related resources.

Re: Export multiple object to multiple sheets - Excel

Tue, 10 Jun 2014 06:56:13 GMT

Tnx whiteline

Now it works, and you are right this is VBA question, but I assumed that someone did something like this