topic Export to Excel - Different Sheets in the Same Workbook in QlikView

Export to Excel - Different Sheets in the Same Workbook

Fri, 23 Oct 2009 00:52:14 GMT

Hi,

Suppose I have 10 markets in data and 1 pivot table.

I want to export 10 tables to 10 excel sheets with different market selected.

ie. select Market A export to Sheet 1, select group B export to Sheet 2 etc.

I need a macro that can automatically select each market one by one, and export the pivot to each excel sheet.

I've seen the same question on forum but there was no answer. Does anyone know where I can find examples? THANK YOU!!

Export to Excel - Different Sheets in the Same Workbook

Fri, 23 Oct 2009 02:24:08 GMT

This is what I've got right now : I can export tables to 5 excel files into 5 sheets in the same file.

I feel I'm almost there....can anyone help me with the macro?

THANKS!!

Export to Excel - Different Sheets in the Same Workbook

Fri, 23 Oct 2009 17:52:27 GMT

For you

sub CopyToXLbyDMA
' Set Excel App
set XLApp = CreateObject("Excel.Application") ' Define Object
set XLDoc = XLApp.Workbooks.Add 'Open new workbook
XLApp.Visible = True 'Visible set as true
z = 0
Set LBy = ActiveDocument.GetSheetObject("LB01") 'Per Year
valy = LBy.GetPossibleValues
for y= lbound(valy) to ubound(valy)
Time_BeforeSelection = ActiveDocument.GetApplication.GetElapsedMilliseconds
vSelectedItemy = valy(y)
Set Fieldy = LBy.GetField
Fieldy.Select vSelectedItemy

Time_AfterSelection = ActiveDocument.GetApplication.GetElapsedMilliseconds
ActiveDocument.GetApplication.WaitForIdle

XLDoc.Worksheets.Add
set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted

' Get TB1
set table = ActiveDocument.GetSheetObject("CH01")
' Copy table and paste into Excel
table.CopyTableToClipboard true 'Copy data to Clipboard
XLSheet.Paste XLSheet.Range("A1") 'Paste data into cell
XLsheet.Cells.EntireColumn.AutoFit
XLsheet.Cells.EntireRow.AutoFit
XLsheet.name = "Tabella_"&y
next 'Year
Fieldy.Clear
end sub

Daniela

Export to Excel - Different Sheets in the Same Workbook

Fri, 23 Oct 2009 23:25:52 GMT

Thank you Daniela!!

I've changed your script a little bit and worked very well!!

Please see the attached file - I HAVE ANOTHER QUESTION

I have 5 markets, now I can let the macro run from A to E and rename each tab by A, B,C,D,E.

What if I want them to rum from E to A and rename the tab names as well?

I tried to use for y= ubound(valy) to lbound(valy) but it didn't work, so I created another index column assigning A as 5, ..., E as 1.

So the variable becomes the index number.

How do I rename the tabs as Market E, D, C, B,A?

How to make the code recognize what is being selected in field "DMA" and name tabs as selected DMA?

THANKS!!

Export to Excel - Different Sheets in the Same Workbook

GabrielAraya — Sat, 24 Oct 2009 01:08:56 GMT

Hi, the follwoing example create a XLS file and the name of each tab is changed by SalesMen Name + Year ...

Export to Excel - Different Sheets in the Same Workbook

Sun, 25 Oct 2009 23:02:56 GMT

To make you count from E to A minus!

for y=Ubound(valy) to Lbound(valy) STEP -1

Ciao!

Daniela

Export to Excel - Different Sheets in the Same Workbook

Tue, 27 Oct 2009 20:04:29 GMT

Daniela,

Thank you very much!!

But can you illustrate more clearly about this part?

Do I type "step -1" right after the " for y=Ubound(valy) to Lbound(valy) "?

That didn't work for me, the last part "Fieldy.Clear" was highlighted and the comment was "Object required : 'Fieldy''

Why is that? THANK YOU IN ADVANCE!!

Export to Excel - Different Sheets in the Same Workbook

Tue, 27 Oct 2009 20:05:44 GMT

Thanks gaa,

this is very helpful!

Export to Excel - Different Sheets in the Same Workbook

Wed, 25 Nov 2009 02:49:09 GMT

Grazie, il nome al foglio sono riuscito a darglielo MI ERO INCASINATO PERCHE' E' OBBLIGATORIO DARGLI UN NUMERO NON NE CAPISCO IL MOTIVO MA VUOLE OBBLIGATORIAMENTE UN FOGLIO NUMERICO

XLsheet.name = vSelectedItemy &

"_("&y &")"

Non riesco però ad attribuire il nome al documento xls

Grazie Ancora

Claudio

Export to Excel - Different Sheets in the Same Workbook

Wed, 25 Nov 2009 02:56:13 GMT

Ciao Daniela,

bravissima

bella storia... le mie conoscenze si limitavano all'exportbiff.

E" possibile attribuire un nome al documento XLS ???

Grazie

Claudio

Re: Export to Excel - Different Sheets in the Same Workbook

rohit214 — Wed, 13 Jun 2012 12:03:45 GMT

Hi Dani

your script very helpful for me but i am not able to get dynamic sheet name means i want

that each sheet shold have starting name with their dealer name.

Thanx& Regards

rohit

Re: Export to Excel - Different Sheets in the Same Workbook

rohit214 — Wed, 13 Jun 2012 12:10:00 GMT

can you translate that macro in english

Thanx& Regards

rohit

Re: Export to Excel - Different Sheets in the Same Workbook

Thu, 15 Nov 2012 19:31:18 GMT

hi Gaa,

I am getting the following error El componente ActiveX no puede crear el objeto: 'Excel.Application' -- do you have any idea why?

Thxs, Cecilia.

Re: Export to Excel - Different Sheets in the Same Workbook

Wed, 19 Dec 2012 08:59:10 GMT

Please add PE enabled files.