LibreOffice Basic Export and Concatenate

REM  *****  BASIC  *****
REM Workhorse function for the concatenate operation
Sub InsertSheetFromFile(sFName, optional sShName, optional tShName, optional pos, optional prot)
   'Brings in a sheet from another document
   '   sFName = file name that can be converted to a url
   '    sShName = name of sheet in source file; default = first sheet (also falls back to this if sheet can't be found)
   '   tShName = name you want for sheet in current document, modified to avoid duplicates; default = sShName
   '   pos = position in current document; if negative count backward from end (-1 = place at end), default = -1
   '   prot: set True if you want the new sheet protected; default = False

   REM target document
   Dim tDoc, tSheets, tSh, sName$, p%
   tDoc = ThisComponent
   tSheets = tDoc.Sheets
   if not isMissing(tShName) then tShName = getUniqueName(tSheets, tShName)
   On Local Error GoTo badPos
      p = cInt(pos)
      if p<0 then
         p = max(0, tSheets.count + 1 + p)
      elseif p>0 then
         p = min(p, tSheets.count)
      end if
   badPos: if err <> 0 then p = tSheets.count

   REM source document
   Dim sURL$, sDoc, shName$
   Dim args(2) As New com.sun.star.beans.PropertyValue
   args(0).Name = "Hidden"
   args(0).Value = True
   args(1).Name = "ReadOnly"
   args(1).Value = True
   args(2).Name = "FilterOptions"
   REM 9 is ASCII value for tab
   REM 0 is for no text delimiter
   REM 76 is unicode (UTF-8), 60 is cp932 (SHIFT-JIS)
   REM 1 is first line to import
   args(2).Value = "9,0,76,1"

   sURL = converttoUrl(sFName)
   sURL = convertToUrl(sFName)
   sDoc = StarDesktop.loadComponentFromURL(sURL, "_default", 0, args())

   On Local Error Goto NoSheet : sShName = sDoc.sheets.getByName(sShName).name
   NoSheet: if err <> 0 then sShName = sDoc.sheets(0).name

   tsheets.importSheet(sDoc, sShName, p)
   sDoc.close(True)

   REM Tidy up the target document
   tSh = tSheets.getByIndex(p)
   if not isMissing(tShName) then tSh.name = tShName
   on local error goto NoProt:
      if not prot then tsh.unprotect("")
   NoProt: tsh.unprotect("")
End Sub

Function getUniqueName(oContainer,sName$)
Dim i%,sNew$
   sNew = sName
   Do while oContainer.hasByName(sNew)
      i = i +1
      sNew = sName &"_"& i
   loop
   getUniqueName = sNew
End Function

function max(a,b) : max = iif(a < b, b, a) : end function
function min(a,b) : min = iif(a > b, b, a) : end function

sub Compile()
    Dim i, strFile, ext_pos
    REM You need the \ at the end of your path (or / for Linux systems)
    path ="YOUR PATH HERE\"
    strFile = Dir(path,0)
    i = 1
    while strFile <> ""
        ext_pos = InStr(strFile, ".")
        if Right(strFile, 4) = ".tsv" then
            InsertSheetFromFile(path + strFile,,left(strFile, ext_pos-1))
        end if
        strFile = Dir ' returns next entry
        i = i + 1
    wend
    thisComponent.getSheets().removeByName("Sheet1")
End Sub

REM  *****  BASIC  *****

REM It exports all sheets in the workbook in .tsv format
REM UTF-8 encoded. File names are taken from sheet names.
REM Default output folder is the folder the workbook is in.

Sub TSV_export(optional outputfolder)
if isMissing(outputfolder) then
    outputfolder = GetPath()
end if
Doc = ThisComponent
Dim Propval(1) as New com.sun.star.beans.PropertyValue
REM 9 is ASCII value for tab
REM 0 is for no text delimiter
REM 76 is unicode (UTF-8), 60 is cp932 (SHIFT-JIS)
REM 1 is first line to import
Propval(0).Name = "FilterName"
Propval(0).Value = "Text - txt - csv (StarCalc)"
Propval(1).Name = "FilterOptions"
Propval(1).Value ="9,0,76,1"   'ASCII  44 = ,  34 = "

for SheetNum = 0 to Doc.Sheets.Count - 1
    Sheet = Doc.Sheets(SheetNum)
    Doc.CurrentController.setActiveSheet(Sheet)
    FileName = outputfolder + Sheet.Name + ".tsv"
    FileURL = convertToURL(FileName)
    Doc.StoreToURL(FileURL, Propval())
next SheetNum
End Sub

Function GetPath as string
path = ThisComponent.getURL()
for i = len(path) - 1 to 1 step -1
    if mid(path, i, 1) = "/" then
        exit for
    end if
next i
GetPath = left(path,i)
End Function