OpenOffice Macro

[jmadero]

It seems to me like it's far and few between that are able to help with OpenOffice macros I posted over in OOo forums and so far, I've gotten one reply and it doesn't seem to be what I'm looking for.

So first off, I'm REALLY new to macros, I did C++ in high school years ago, I'm pretty good with layouts and logic of code so...with that

All I want to do is make a macro that changes the Formatting of a cell (most importantly change cells that currently have number values into Currency values with 2 numbers after the decimal.

I believe that this should be relatively simple, the other person from OOo forums suggested I use another macro that's built by someone else that makes it so you can create your own format styles, but this isn't what I'm looking for, the format already exists, I just want to implement it....seems like it should be easy


I found some stuff on setNumberFormat but it won't work for me, then I looked at what was being imported into another example macro that I found online and I saw

import com.sun.star.beans.PropertySet

I put that in mine and I immediately get an error. Where am I supposed to put the import function? Inside of sub Main, before it??

I thought that this would be the least of my worries with this macro....lol

[peteh]

I've found the easiest way to start with a macro is to record what you want and then tweak the instructions once you've got the basics.
I've written some pretty complicated macros this way.

[jmadero]

EXCELLENT IDEA! I hadn't even consider that Thank you much! If it doesn't work I'll be back

[jmadero]

okay so that helped a lot but I have one last issue, here is the macro to change a cell from number to currency

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelpe r")


rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue

args2(0).Name = "NumberFormatValue"
args2(0).Value = 104

dispatcher.executeDispatch(document, ".uno:NumberFormatValue", "", 0, args2())



end sub

I'm not seeing where it's getting position from. It is currently just changing the current cell, I need to be able to manipulate the location regardless of what cell is selected. So if A10 is selected, I need to be able to change B30 (or whatever, this is actually going inside of a loop so it'll be changing a lot of cells).

Any ideas?

[peteh]

If you want to run down Column A changing every entry you would need something like this:

Mysheetx = ThisComponent.Sheets.getbyname("sheet1") #or the name of your sheet
MyInteger = 0 #the count starts at zero not 1
Do until MySheetx.getcellbyposition(0,MyInteger).Value = "" #start at A1 and go until reaching a blank cell
The macro action you want
MyInteger = MyInteger + 1
Loop

That may also need some tweaking but with that and openoffice help you should be off to a good start.

[peteh]

Incidentally, which OOo forum did you go to. I find the best one is this one.

[jmadero]

Thanks for the advice. So far, no good but I did post on the other forum as well. Here is my code now:

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
dim MyInteger as integer

rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelpe r")

dim args2(0) as new com.sun.star.beans.PropertyValue

args2(0).Name = "NumberFormatValue"
args2(0).Value = 104

Mysheetx = ThisComponent.Sheets.getbyname("sheet2") '#or the name of your sheet
MyInteger = 0 '#the count starts at zero not 1
Do until MySheetx.getcellbyposition(0,MyInteger).Value = "" '#start at A1 and go until reaching a blank cell
dispatcher.executeDispatch(document, ".uno:NumberFormatValue", "", 0, args2())
MyInteger = MyInteger + 1
Loop




rem ----------------------------------------------------------------------


end sub

I'm pretty sure it has to do with the third value in my action that I want done which is currently set to "". It is only applying the change to the current active cell and then it goes into an inifinite loop. Doesn't do anything until I push stop.

Any other suggestions would be appreciated. Also, do you know how to clear a sheet within a macro? Right now every time I rerun the macro I'm having to manually go into the sheet and delete anything that gets put in there, it'd be nice if the first command was something like "Clear everything in sheet2", thanks!

[peteh]

I saw you when I was lurking in the OOo forum so I posted some additional info. You may have already seen it.
Post back if it works and just in case anyone else is interested from this forum perhaps you could quote the additional code (if it works).
Good luck.

[jmadero]

lol didn't even connect the two Hi Pete. Yes the code did work after I manipulated it a bit, now I'm having to put it in some nested for's and if statements to take care of all the technicalities but it is working. I still don't know why it wasn't working the other way but....oh well as long as it's working now

After I'm done with the whole macro (probably going to take several more weeks). I'll post the entire code and see if anyone has suggestions to make it more efficient.

Is there an easy to command to "clear all" for a sheet? Thanks again, you've been a huge help

[peteh]

I'm glad I could help. Basically what was happening with your first attempt at a loop was that although the macro was looping it wasn't actually moving the cursor to a new cell. That's where the "oCell.Select()" instruction comes in.