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

Creating a macro - Writing a Script - Using the API

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

Postby Ordoss » Thu Dec 29, 2011 1:19 pm

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.
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
Ordoss
 
Posts: 7
Joined: Thu Dec 29, 2011 1:17 pm

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

Postby JohnSUN-Pensioner » Thu Dec 29, 2011 1:28 pm

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.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

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

Postby Ordoss » Thu Dec 29, 2011 1:31 pm

Yes; already I have a sheet that A column contains data.
OpenOffice 3.3.0
Windows 7 Professional
Ordoss
 
Posts: 7
Joined: Thu Dec 29, 2011 1:17 pm

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

Postby rudolfo » Thu Dec 29, 2011 2:06 pm

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. 
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.
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

Postby Ordoss » Thu Dec 29, 2011 2:16 pm

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
Ordoss
 
Posts: 7
Joined: Thu Dec 29, 2011 1:17 pm

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

Postby rudolfo » Thu Dec 29, 2011 2:27 pm

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.
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

Postby JohnSUN-Pensioner » Thu Dec 29, 2011 2:30 pm

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
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

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

Postby Ordoss » Thu Dec 29, 2011 2:50 pm

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
Ordoss
 
Posts: 7
Joined: Thu Dec 29, 2011 1:17 pm

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

Postby RoryOF » Thu Dec 29, 2011 4:58 pm

Apache OpenOffice 4.1.6 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29438
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Postby Villeroy » Thu Dec 29, 2011 5:11 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27098
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Ordoss » Thu Dec 29, 2011 10:05 pm

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 1003 times
OpenOffice 3.3.0
Windows 7 Professional
Ordoss
 
Posts: 7
Joined: Thu Dec 29, 2011 1:17 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests