## [Solved] Need help with export macro to plain text with Calc

Creating a macro - Writing a Script - Using the API

### [Solved] Need help with export macro to plain text with Calc

HI.

I am newbie with openoffice.
i am trying to do a macro to export a range cells (a5:a100) to a plain text file.

For example:

a5: server 340
a6: green 580
a7: blue 100
..
..
a100: black 1000

and out file is: (example.txt) (an extra is getting name from another cell)

server 340
green 580
blue 100
..
..
black 1000

I found several examples for excel, but dont run on Openoffice.

Anybody know (or got) a code to do this?

Any help will be apreciatted.

Roman.
Last edited by electric_gt on Thu Jan 19, 2012 10:20 pm, edited 1 time in total.
OPENOFFICE 3.3 ON WINDOWS 7
electric_gt

Posts: 4
Joined: Tue Jan 17, 2012 9:23 pm

### Re: Need help with a export macro to plain text with CALC

Welcome to the forum!
Something like that - viewtopic.php?f=20&t=46486#p214782 ?
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

JohnSUN-Pensioner
Volunteer

Posts: 824
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

### Re: Need help with a export macro to plain text with CALC

It depends a bit what your range could look like. If the data is all in one column a4:a304, you could as well select that range, copy it to the clipboard and paste it into notepad. You can do is also if you want the data of 3 or more columns, but in that case you will have a TAB between the content of A4 and B4 in your file, that is opened in notepad.
In general the "Save As .csv" functionality of Calc has all what you need to create such plain text files that you want to have. You can choose an appropriate field separator (TAB, semicolon, comma, etc.) and it is fast compared to a macro with a loop. The only problem is that always the complete sheet will be exported. But that's not a real drawback, if you have the nice Unix tools that are made for post processing structured data:
Code: Select all   Expand viewCollapse view
sed -n 5,120p exported.txt > post-processed.txt

will cut out the lines 5 to 120 from the exported file. (Depending if you have a header line with the column titles you will need -n 6,121p if you are not interested in these column headers or -n "1p; 6,121p" if you want to have the first header line in the final file.
 Edit: Admitted, this is not a very appropriate approach if you only want some of the columns of the spreadsheet in your output file. You would need another Unix tool cut (which is not so widespread and well known as sed at least not in the MS Windows world) in combination with sed.
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: Need help with a export macro to plain text with CALC

JohnSUN-Pensioner wrote:Welcome to the forum!
Something like that - viewtopic.php?f=20&t=46486#p214782 ?

Yeah...!!!

That's right.

I need modify my sheet because the sub extract full sheet, not a range, but it's a minor task.
Thank you very much!!!!

Finally, for setting the sub on my system, i need change file name created, getting name from another sheet cell.

The code is:

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

I'll try to modify "C\imacros\test.iim" with 'another_sheet'.A1
and another_sheet.A1=c:\text.txt, but really i am too newbie... cant get it run

Obviously i am wrong declaring this argument, but i cant solve it. Can you tell me how can i fix it?

Thanks

Roman
OPENOFFICE 3.3 ON WINDOWS 7
electric_gt

Posts: 4
Joined: Tue Jan 17, 2012 9:23 pm

### Re: Need help with a export macro to plain text with CALC

electric_gt wrote:Can you tell me how can i fix it?

Yes, Roman, I can
Code: Select all   Expand viewCollapse view
Sub WriteFile()Dim oSheets As VariantDim oSheet As VariantDim oCell As VariantDim sString As StringConst sheetName = "another_sheet"   oSheets = ThisComponent.getSheets()   If oSheets.hasByName(sheetName) Then      oSheet = oSheets.getByName(sheetName)      oCell = oSheet.getCellRangeByName("A1")REM Or you can type oCell = oSheet.getCellByPosition(0, 0) - params nColumn and nRow      sString = oCellRangeByName.getString()      Call SaveCurrentSheetAsPlainText(sString, "Base")   ElseREM Error - Sheet with name sheetName not found   EndIfEnd Sub

 Edit: Oh! I've only just noticed your signature! Indeed, "2.1 on 7"? Do you not want to update the office suite?
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

JohnSUN-Pensioner
Volunteer

Posts: 824
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

### Re: Need help with a export macro to plain text with CALC

JohnSUN-Pensioner wrote:
 Edit: Oh! I've only just noticed your signature! Indeed, "2.1 on 7"? Do you not want to update the office suite?

If you do update your version of the suite, be aware that Windows 7 interacts adversely with an "out of the box" install of OOo 3.3. You should do a custom install and select not to auto check for updates, _or_ uninstall Internet Explorer 9 (IE8 is automatically reinstalled).
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP

RoryOF
Moderator

Posts: 31241
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

### Re: Need help with a export macro to plain text with CALC

JohnSUN-Pensioner wrote:
electric_gt wrote:Can you tell me how can i fix it?

Yes, Roman, I can
Code: Select all   Expand viewCollapse view
Sub WriteFile()Dim oSheets As VariantDim oSheet As VariantDim oCell As VariantDim sString As StringConst sheetName = "another_sheet"   oSheets = ThisComponent.getSheets()   If oSheets.hasByName(sheetName) Then      oSheet = oSheets.getByName(sheetName)      oCell = oSheet.getCellRangeByName("A1")REM Or you can type oCell = oSheet.getCellByPosition(0, 0) - params nColumn and nRow      sString = oCellRangeByName.getString()      Call SaveCurrentSheetAsPlainText(sString, "Base")   ElseREM Error - Sheet with name sheetName not found   EndIfEnd Sub

 Edit: Oh! I've only just noticed your signature! Indeed, "2.1 on 7"? Do you not want to update the office suite?

First, thanks for help.

I am doing a protocol cnc with excel, so i need get out plain text. i am very focused on task, and really your help let me breath!

I understand choice lines for A1 or range cells...

But i cant go run this new code on my sheet, because i cant understand it

I'll try to explain me better. Sorry, my english is awfull.

I need save in file txt plain cells a1 to a200, getting name of file from a cell in this sheet, or another.

Your first code save a c:\Imacros\test.iim (constant name) a full sheet. It's a minor problem for me. I can do a sheet with only content to save, so that, your code is ok for me. Thanks again.

My point is, i need file's name get from a cell, in another sheet, because if it's in the same sheet, will be exported too, and my cnc router cant run with this program.

I apologize if i cant explain it, and really, i apreciate your help.

Roman

PD. Sorry for my signature. When i created account, i type bad data . i am ussing 3.3 on win7
OPENOFFICE 3.3 ON WINDOWS 7
electric_gt

Posts: 4
Joined: Tue Jan 17, 2012 9:23 pm

### Re: Need help with a export macro to plain text with CALC

Hello,
excuse me, but i think there is a very little bug in the code, you should change
Code: Select all   Expand viewCollapse view
sString = oCellRangeByName.getString()
into
Code: Select all   Expand viewCollapse view
sString = oCell.getString()
to read the Url for the new file from Cell A1.

Greats R
MMove 1.0.6
• Extension for easy, exact positioning of shapes, pictures, controls, frames ...
my current system
• Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer

Posts: 972
Joined: Fri Dec 16, 2011 8:20 pm

### Re: Need help with a export macro to plain text with CALC

Oh, thank you, my friend! You are absolutely right! It was an unfortunate typo...
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

JohnSUN-Pensioner
Volunteer

Posts: 824
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

### Re: Need help with a export macro to plain text with CALC

Hi.

Finally i got setting your code in my sheet

Thank you very much for your help and support.

Roman.
OPENOFFICE 3.3 ON WINDOWS 7
electric_gt

Posts: 4
Joined: Tue Jan 17, 2012 9:23 pm