[Solved] Saving a sheet as text file (not csv) in Calc
[Solved] Saving a sheet as text file (not csv) in Calc
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.
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.
Reason: Tagged Solved, TheGurkha.
OpenOffice 3.3.0
Windows 7 Professional
Windows 7 Professional
- 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
Welcome to the forum!
What do you mean by format "not csv"?
Plain text, each filled cell in a line?
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
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
Re: Saving a sheet as text file (not csv) in Calc
Yes; already I have a sheet that A column contains data.
OpenOffice 3.3.0
Windows 7 Professional
Windows 7 Professional
Re: Saving a sheet as text file (not csv) in Calc
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.
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.
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.
Re: Saving a sheet as text file (not csv) in Calc
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.
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
Windows 7 Professional
Re: Saving a sheet as text file (not csv) in Calc
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.
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.
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.
- 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
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
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
Re: Saving a sheet as text file (not csv) in Calc
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.
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
Windows 7 Professional
Re: Saving a sheet as text file (not csv) in Calc
You may find this thread of use
http://user.services.openoffice.org/en/ ... 45&t=34074
http://user.services.openoffice.org/en/ ... 45&t=34074
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Saving a sheet as text file (not csv) in Calc
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Saving a sheet as text file (not csv) in Calc
Thanks to JohnSun-Pensioner.
I attached his solution.
I attached his solution.
- Attachments
-
- Saving a sheet as text file in Calc-By JohnSUN-Pensioner.ods
- (12.02 KiB) Downloaded 1365 times
OpenOffice 3.3.0
Windows 7 Professional
Windows 7 Professional