I need to export a CALC sheet in cvs format preserving the cells' format. The issue here is that some cells have a the string 0x pre-pended to indicate to the tool that will process the csv file that that value is hexadecimal.
If the sheet is exported with File/save as everything wors fine, but if I use a macro (please find below my export function code) the 0x characters are removed.
Does anyone have an idea on how to solve this?
Thanks in advance.
Andrea Sabatini
Code: Select all
function Export (FileName as string, SheetName as string)
Dim oProp(2) As New com.sun.star.beans.PropertyValue
Dim sPath, sFile, sBase, sUrl, sSep As String
Dim oDoc, oSheet As Variant
Dim iLen, iFieldSeparator, iTextDelimiter As Integer
sSep = "/"
oDoc = ThisComponent
If ( Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools") ) Then
GlobalScope.BasicLibraries.Loadlibrary("Tools")
End If
If ( oDoc.hasLocation() ) Then
sURL = oDoc.getURL()
sPath = DirectoryNameoutofPath(sURL, sSep) + sSep
sBase = FileNameoutofPath(sURL, sSep)
End If
iLen = len(sBase)
sBase = Mid(sBase, 1, iLen - 4)
oProp(0).Name = "InteractionHandler"
oProp(0).Value = ""
oProp(1).Name = "FilterName"
oProp(1).Value = "scalc: Text - txt - csv (StarCalc)"
iFieldSeparator = asc(";") ' ASCII value for ,
iTextDelimiter = asc("""") ' ASCII value for "
oProp(2).Name = "FilterOptions"
oProp(2).Value = CStr(iFieldSeparator) + "," + CStr(iTextDelimiter) + ",,,"
oSheet = oDoc.Sheets.getByName(SheetName)
oDoc.getCurrentController().setActiveSheet(oSheet)
sFile = FileName ' sBase + "_" + CStr(SheetName) + ".csv"
oDoc.storeToURL (sPath + sFile, oProp())
end function