[Solved] Saving a sheet as text file (not csv) in Calc

[TheGurkha]

Hi

I try to save one of Calc sheets as txt (not csv) file (encoding should be UTF-8 ) by using BASIC; I can't.
I searched the forum;

viewtopic.php?f=45&t=34074
http://www.oooforum.org/forum/viewtopic.phtml?t=44907

but I couldn't use this solutions.
Is there a simple code that fix this?
For example I want to save Sheet2 as "Asd.txt".

Thank you.

[JohnSUN-Pensioner]

Welcome to the forum!
What do you mean by format "not csv"?
Plain text, each filled cell in a line?

[Ordoss]

Yes; already I have a sheet that A column contains data.

[rudolfo]

Sorry, but you don't want to save a sheet in a Calc file by using BASIC. I am sure you are not insisting on Basic, you just want to get the job done. Calc files are as all other OpenOffice files ODF (Open Document Format) and a collection of xml files in a zip archive. There is a general concept called XSLT to convert xml data in any appropriate other format. See this thread viewtopic.php?f=45&t=3490 and also have a look at the sample xslt files in Tools -> XSLT Filters ...

Though XSLT is not a technique for beginners it is still a more widespread technology then BASIC. Personally I would still try to export the sheet to csv and post-process the resulting csv file. But I doubt if this is really necessary, because the export option dialog of Calc gives you quite a lot of options for what you can use as field separator.

Edit: After a check of the UTF features of Notepad. I agree with Villeroy that the best idea is to use Notepad.

[Ordoss]

Yes; I dont insist on Basic ; all I need a macro that I can assign to a button; thus to make an Imacros For Firefox macro. This macro is a text file (UTF-8) ; and its extension ".iim" instead of ".txt".
In MS Excel I saved a sheet as "txt" with the code below:

Sub wsToText()
Dim ws As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For i = 1 To 59
Sheets("Sheet4").Cells(1, 1) = i
Sheets("Base").Activate
Sheets("Base").Copy
B = Len(Sheets("Base").Cells(12, 1))
F = B - 1

A = Mid(Sheets("Base").Cells(12, 1), 2, F)


ActiveWorkbook.SaveAs Filename:="C:\Imacros\" & A & ".iim", _
FileFormat:=xlUnicodeText, CreateBackup:=False
ActiveWorkbook.Close
ThisWorkbook.Activate
Next

End Sub


I need something like this , in OpenOffice.

[rudolfo]

Looking at your macro I am highly irritated: It runs through a loop 59 times and in each loop cycle it export the current sheet to a text file. The variable A that goes into the filename doesn't seem to change. So it will overwrite the file in 58 cases!
Can you use the code buttons of the full editor when posting and indentation to make your code (visually) clearer. And it is surely a good idea if you explain why your macro has to run through the loop.

[JohnSUN-Pensioner]

I'm not sure that this is the final version, but it looks like it will work

Code: Select all   Expand viewCollapse view

Sub WriteFile()
   Call SaveCurrentSheetAsPlainText("C:\Imacros\test.iim", "Base")
End Sub

Sub SaveCurrentSheetAsPlainText(aFileName As String, SheetName As String)
Dim oSpreadsheet As Variant
Dim oCursor As Variant
Dim oFinder As Variant
Dim oResult As Variant
Dim allResult
Dim oCell
Dim iNumber As Integer
Dim sLine As String
   oSpreadsheet = ThisComponent.getSheets().getByName(SheetName)
REM Select only UsedArea
   oCursor = oSpreadsheet.createCursor()
   oCursor.gotoStartOfUsedArea(False)
   oCursor.gotoEndOfUsedArea(True)
REM Get not empty cells
   oFinder = oCursor.createSearchDescriptor()
   oFinder.setSearchString(".+")
   oFinder.searchRegularExpression = True
   oResult = oCursor.FindAll(oFinder)
REM Write each cell in single line
   If Not IsNull(oResult) Then
      allResult = oResult.createEnumeration()
      iNumber = Freefile
      Open aFileName For Output As #iNumber
      Do While allResult.hasMoreElements()
         oCell = allResult.nextElement()
         Print #iNumber, oCell.getString()
      Loop
      Close #iNumber
   EndIf
End Sub

[Ordoss]

There is no overwriting, "Sheets("Sheet4").Cells(1, 1) = i" triggers something, it links a cell and a formula.
If you'll ask why , forget it.

The essence is;

"ActiveWorkbook.SaveAs Filename:="C:\Imacros\" & A & ".iim", _
FileFormat:=xlUnicodeText, CreateBackup:=False
ActiveWorkbook.Close
ThisWorkbook.Activate"

not the structure of loop.

It seems, someone other than me "needs" a macro; instead of follow the menu items "File->Save As->File Type->Text CSV ; so:
http://www.oooforum.org/forum/viewtopic.phtml?t=44907

So, "please" ignore the thread, I need help ; from who is willing to give.

[RoryOF]

You may find this thread of use
http://user.services.openoffice.org/en/forum/viewtopic.php?f=45&t=34074

[Villeroy]

The Python lanugage built into this office suite has a whole library of functions to read and write csv.

[Ordoss]

Thanks to JohnSun-Pensioner.
I attached his solution.