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
