0 Replies Latest reply: Mar 12, 2012 5:53 AM by Phill Gilchrist RSS

    Export To Excel - 1 Field / Tab

    Phill Gilchrist
      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