Page 1 of 1
[Solved] Saving a sheet as text file (not csv) in Calc
Posted: Thu Dec 29, 2011 1:19 pm
by Ordoss
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;
http://user.services.openoffice.org/en/ ... 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.
Re: Saving a sheet as text file (not csv) in Calc
Posted: Thu Dec 29, 2011 1:28 pm
by JohnSUN-Pensioner
Welcome to the forum!
What do you mean by format "not csv"?
Plain text, each filled cell in a line?
Re: Saving a sheet as text file (not csv) in Calc
Posted: Thu Dec 29, 2011 1:31 pm
by Ordoss
Yes; already I have a sheet that A column contains data.
Re: Saving a sheet as text file (not csv) in Calc
Posted: Thu Dec 29, 2011 2:06 pm
by 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
http://user.services.openoffice.org/en/ ... =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. |
Re: Saving a sheet as text file (not csv) in Calc
Posted: Thu Dec 29, 2011 2:16 pm
by 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.
Re: Saving a sheet as text file (not csv) in Calc
Posted: Thu Dec 29, 2011 2:27 pm
by 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.
Re: Saving a sheet as text file (not csv) in Calc
Posted: Thu Dec 29, 2011 2:30 pm
by JohnSUN-Pensioner
I'm not sure that this is the final version, but it looks like it will work
Code: Select all
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
Re: Saving a sheet as text file (not csv) in Calc
Posted: Thu Dec 29, 2011 2:50 pm
by 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.
Re: Saving a sheet as text file (not csv) in Calc
Posted: Thu Dec 29, 2011 4:58 pm
by RoryOF
Re: Saving a sheet as text file (not csv) in Calc
Posted: Thu Dec 29, 2011 5:11 pm
by Villeroy
The Python lanugage built into this office suite has a whole library of functions to read and write csv.
Re: Saving a sheet as text file (not csv) in Calc
Posted: Thu Dec 29, 2011 10:05 pm
by Ordoss
Thanks to JohnSun-Pensioner.
I attached his solution.