Export To Excel - 1 Field / Tab

Anonymous — Mon, 12 Mar 2012 09:53:25 GMT

Hi,

I have got a table that I need to export to excel splitting it over workbooks by a group.

I've thankfully picked up a bit of code to do this, as below.

But, what I also need is to split those worksheet by a sub field by tab and rename the tab appropriately.

Any ideas?

Thanks in advance.

sub Export

set f = activedocument.Fields("FE_SUB_GROUP")

if IsEmpty(f.GetSelectedValues) then
set pv = f.GetSelectedValues 'adds selected values if any
else
set pv = f.GetPossibleValues(1000) 'adds possible values if none selected
end if

set Array1 = f.GetNoValues 'empty array
for i = 0 to pv.count-1 'adds values from the first array
Array1.Add
Array1(i).Text = pv.item(i).Text
Array1(i).IsNumeric = false 'if text in your field
next

'Creates a path and a filename of the output xls-file in the same folder as the current document
'Path = ActiveDocument.Evaluate("left(DocumentPath(), index(DocumentPath(), '\', -1))")
Path = "C:\Team_Audit\" 'your static path if you dont want a dynamic path
set obj = ActiveDocument.GetSheetObject("CH97") 'the ID of the object you want to export

'Loop through each selection in your field
for i = 0 to Array1.count-1
f.Select Array1(i).Text 'Selects one value in your field at a time

'Sets the FileName to be the same as the selection in your chosen field
FileName = Array1(i).Text & ".xls"

' Starts Excel
set XLApp = CreateObject("Excel.Application")
' Makes it run in background
XLApp.Visible = False
set XLDoc = XLApp.Workbooks.Add

' Set the cell to start in at A1
Set rngStart = XLDoc.Sheets(1).Range("A1")

' Copies the chosen object contents to clipboard
obj.CopyTableToClipboard true
' Paste it into excel
XLDoc.Sheets(1).Paste()
' Save the excel-file with the dynamic path and filename
XLDoc.SaveAs Path & FileName
' Exits the current running Excel
XLApp.Quit

next 'goes to the next value in the selection in the chosen field

end sub

topic Export To Excel - 1 Field / Tab in QlikView

Export To Excel - 1 Field / Tab