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

[electric_gt]

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.

Thanks in advanced.
Roman.

[JohnSUN-Pensioner]

Welcome to the forum!
Something like that - http://user.services.openoffice.org/en/ ... 86#p214782 ?

[rudolfo]

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

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.

[electric_gt]

Welcome to the forum!
Something like that - http://user.services.openoffice.org/en/ ... 86#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

[JohnSUN-Pensioner]

Can you tell me how can i fix it?

Yes, Roman, I can

Code: Select all

Sub WriteFile()
Dim oSheets As Variant
Dim oSheet As Variant
Dim oCell As Variant
Dim sString As String
Const 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")
	Else
REM Error - Sheet with name sheetName not found
	EndIf
End Sub

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

[RoryOF]

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

[electric_gt]

Can you tell me how can i fix it?

Yes, Roman, I can

Code: Select all

Sub WriteFile()
Dim oSheets As Variant
Dim oSheet As Variant
Dim oCell As Variant
Dim sString As String
Const 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")
	Else
REM Error - Sheet with name sheetName not found
	EndIf
End 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

[F3K Total]

Hello,
excuse me, but i think there is a very little bug in the code, you should change

Code: Select all

sString = oCellRangeByName.getString()

into

Code: Select all

sString = oCell.getString()

to read the Url for the new file from Cell A1.

Greats R

[JohnSUN-Pensioner]

Oh, thank you, my friend! You are absolutely right! It was an unfortunate typo...

[electric_gt]

Hi.

Finally i got setting your code in my sheet

Thank you very much for your help and support.

Roman.