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
