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

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Ordoss
Posts: 7
Joined: Thu Dec 29, 2011 1:17 pm

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

Post 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.
Last edited by TheGurkha on Thu Dec 29, 2011 10:43 pm, edited 2 times in total.
Reason: Tagged Solved, TheGurkha.
OpenOffice 3.3.0
Windows 7 Professional
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Saving a sheet as text file (not csv) in Calc

Post by JohnSUN-Pensioner »

Welcome to the forum!
What do you mean by format "not csv"?
Plain text, each filled cell in a line?
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Ordoss
Posts: 7
Joined: Thu Dec 29, 2011 1:17 pm

Re: Saving a sheet as text file (not csv) in Calc

Post by Ordoss »

Yes; already I have a sheet that A column contains data.
OpenOffice 3.3.0
Windows 7 Professional
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Saving a sheet as text file (not csv) in Calc

Post 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. 
Last edited by rudolfo on Thu Dec 29, 2011 2:18 pm, edited 1 time in total.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
Ordoss
Posts: 7
Joined: Thu Dec 29, 2011 1:17 pm

Re: Saving a sheet as text file (not csv) in Calc

Post 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.
OpenOffice 3.3.0
Windows 7 Professional
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Saving a sheet as text file (not csv) in Calc

Post 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.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Saving a sheet as text file (not csv) in Calc

Post 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
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Ordoss
Posts: 7
Joined: Thu Dec 29, 2011 1:17 pm

Re: Saving a sheet as text file (not csv) in Calc

Post 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.
OpenOffice 3.3.0
Windows 7 Professional
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Saving a sheet as text file (not csv) in Calc

Post by RoryOF »

Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Saving a sheet as text file (not csv) in Calc

Post by Villeroy »

The Python lanugage built into this office suite has a whole library of functions to read and write csv.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ordoss
Posts: 7
Joined: Thu Dec 29, 2011 1:17 pm

Re: Saving a sheet as text file (not csv) in Calc

Post by Ordoss »

Thanks to JohnSun-Pensioner.
I attached his solution.
Attachments
Saving a sheet as text file in Calc-By JohnSUN-Pensioner.ods
(12.02 KiB) Downloaded 1355 times
OpenOffice 3.3.0
Windows 7 Professional
Post Reply