OpenOffice Basic Macro to Create Tab Delimited Text Files

This piece of code has proven most useful today...

It's a OpenOffice Basic macro that creates a tab delimited text file for each sheet in the document. Each sheet tab name is used to name the file.

Sub CreateText

  '----------------------------------------------------------------------
  ' Save document

  Dim document As Object
  Dim dispatcher As Object
  document = ThisComponent.CurrentController.Frame
  dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
  dispatcher.executeDispatch(document, ".uno:Save", "", 0, Array())

  '----------------------------------------------------------------------
  ' Export to text files

  Dim oDoc As Object ' The current Document
  Dim oSheets As Object ' A collection of all sheets
  Dim oSheet As Object ' One specific Sheet
  Dim sNewFilename As String ' New Filename
  Dim sExt As String ' The new extension to be applied
  Dim sURL As String ' Existing URL of file
  Dim sCurrDir As String ' Current Directory
  Dim oSaveSheet As Object
  Dim FileProperties(2) As New com.sun.star.beans.PropertyValue

  oDoc = thisComponent
  oSaveSheet = oDoc.CurrentController.getActiveSheet()
  oSheets = oDoc.Sheets()
  sURL = ConvertFromURL(oDoc.getLocation())
  sCurrDir = Mid(sURL,1,Len(sURL)-Len(Dir(sURL)))

  FileProperties(0).Name = "Overwrite"
  FileProperties(0).Value = True
  FileProperties(1).Name = "FilterName"
  FileProperties(1).Value = "Text - txt - csv (StarCalc)"
  FileProperties(2).Name = "FilterOptions"
  FileProperties(2).Value = "9,0,ANSI,1"
  sExt = ".txt"

  For i = 0 to (oSheets.getCount()-1)
    oSheet = oSheets.getByIndex(i)
    sNewFilename = oSheet.getName() & sExt
    oDoc.CurrentController.setActiveSheet(oSheet)
    oDoc.storeToURL(ConvertToURL(sCurrDir & sNewFilename),FileProperties())
  Next i

  oDoc.CurrentController.setActiveSheet(oSaveSheet) ' restores the original view

  MsgBox("Text Files Created")

End Sub

OOBasic Text File Export...Done