OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

how to make macro execute?
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
pfeldman
General User
General User


Joined: 13 Sep 2008
Posts: 16

PostPosted: Sun Jul 08, 2012 6:11 pm    Post subject: how to make macro execute? Reply with quote

I have a macro (see below) that is supposed to copy an existing Excel spreadsheet, open the copy using Calc, change the value in a cell, and then save the modified file. So far, I can't make it even copy the file.

The command that I'm issuing to run the macro is the following:

soffice "modify_Excel.ods" "macro:modify_Excel(in.xls,out.xls)

Any suggestions re. what I'm doing wrong will be appreciated.

Phillip

Option Explicit

Sub modify_Excel(infile as String, outfile as String)
dim sURL as String
dim oDoc as Object
dim oSheet as Object
dim oCell as Object

' Copy input file to output file:
FileCopy(infile, outfile)

' Convert file name to URL representation:
sURL= ConvertToURL(outfile)

oDoc= starDeskTop.loadComponentFromUrl(sURL, "_hidden", 0, Array())

oSheet= thisComponent.Sheets("Sheet1")

oCell= oSheet.getCellByPosition(0, 2)
oCell.String= "testing"

oDoc.store(outfile)
oDoc.Close(true)
End Sub
_________________
Dr. Phillip M. Feldman
Back to top
View user's profile Send private message
patel
Power User
Power User


Joined: 14 Apr 2012
Posts: 54
Location: Italy

PostPosted: Sun Jul 08, 2012 7:17 pm    Post subject: Reply with quote

tu run your macro add this
Code:
sub macrorun
call modify_Excel(in.xls,out.xls)
end sub

_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
B Marcelly
Super User
Super User


Joined: 12 May 2004
Posts: 1453
Location: France

PostPosted: Tue Jul 10, 2012 3:54 am    Post subject: Reply with quote

Hello,
I understand that you want to run the macro (contained in the document) from the command line.
Assuming that the macro is in module Module1 of library Standard of the document you should have a command line like:
Code:
soffice "modify_Excel.ods" "macro://modify_Excel/Standard.Module1.modify_Excel(in.xls,out.xls)"

Of course your OpenOffice configuration must be set so that executing macros from this document is allowed.


Note that your Basic code is incorrect (but works by chance):
Code:
oSheet= thisComponent.Sheets("Sheet1")  ' *** incorrect ***
oSheet= thisComponent.Sheets.getByName("Sheet1") ' correct

It works because Basic has to convert "Sheet1" into an integer, obtains 0, and the API understands : the sheet of index zero, which usually is named Sheet1.
_________________
Bernard

OpenOffice.org 1.1.5 fr / Apache OpenOffice 4.0.1 / LibreOffice 4.1.0
MS-Windows 7 Home SP1
This forum is spammed, use instead Apache OpenOffice forums
Back to top
View user's profile Send private message Visit poster's website
pfeldman
General User
General User


Joined: 13 Sep 2008
Posts: 16

PostPosted: Tue Jul 10, 2012 3:58 pm    Post subject: Reply with quote

Hello Bernard,

Thanks for the correction to my scrip!

But, under both Windows 7 and Linux, the script does not work for me. I have two issues: (1) The Calc GUI opens on modify_Excel.ods and remains open (I'd prefer that the GUI never open at all). (2) The file out.xls is never created.

Any suggestions will be appreciated.

Phillip
_________________
Dr. Phillip M. Feldman
Back to top
View user's profile Send private message
B Marcelly
Super User
Super User


Joined: 12 May 2004
Posts: 1453
Location: France

PostPosted: Thu Jul 12, 2012 5:47 am    Post subject: Reply with quote

Hello again,
Your answer does not give me much info, except that you are adding questions.
It is not clear for me if the document was opened by the call or if only the splash screen was displayed.
Code:
soffice "modify_Excel.ods" "macro://modify_Excel/Standard.Module1.modify_Excel(in.xls,out.xls)"

The above code will work on Windows XP and most probably Win 7. I don't practice Linux, there are some differences; keep Windows for a start.
The above code supposes that soffice.exe and the document modify_Excel.ods are both accessible from the current directory. Otherwise you must provide the path to soffice.exe, or to the document, or both.

For a first try, your test is too much complex.
- create a new Calc document, call it : Test1.ods
- in this document, create a new macro. By default it will be macro Main, stored in Module1 of library Standard of the document. (do not use the library Standard of My Macros")
- in the macro Main, insert only one instruction:
MsgBox("Hello")
- save, close the document, reopen it, execute the macro. Does it display the message?
- close the document.
- now write your command line (or preferably a batch file)
Code:
soffice "Test1.ods" "macro://Test1/Standard.Module1.Main()"

- execute the command line.
The document should display, and the message Hello should appear.
_________________
Bernard

OpenOffice.org 1.1.5 fr / Apache OpenOffice 4.0.1 / LibreOffice 4.1.0
MS-Windows 7 Home SP1
This forum is spammed, use instead Apache OpenOffice forums
Back to top
View user's profile Send private message Visit poster's website
pfeldman
General User
General User


Joined: 13 Sep 2008
Posts: 16

PostPosted: Fri Jul 13, 2012 11:16 am    Post subject: Reply with quote

I followed your instructions to the letter. The document opens (and remains open), but the message is not displayed. I'm surprised that neither the "Hello" message nor any error message is displayed.
_________________
Dr. Phillip M. Feldman
Back to top
View user's profile Send private message
B Marcelly
Super User
Super User


Joined: 12 May 2004
Posts: 1453
Location: France

PostPosted: Fri Jul 13, 2012 11:07 pm    Post subject: Reply with quote

Hi,
Make sure that :
  • the command line is exactly as written
  • the macro is in the document, not in "My Macros".
    When you do Tools > Macros > OpenOffice.org Basic, the opening window shows, at left, three main trees:
    - My Macros
    - OpenOffice.org Macros
    - The name of your document. You must store the macro into this one.

_________________
Bernard

OpenOffice.org 1.1.5 fr / Apache OpenOffice 4.0.1 / LibreOffice 4.1.0
MS-Windows 7 Home SP1
This forum is spammed, use instead Apache OpenOffice forums
Back to top
View user's profile Send private message Visit poster's website
pfeldman
General User
General User


Joined: 13 Sep 2008
Posts: 16

PostPosted: Sat Jul 14, 2012 1:33 pm    Post subject: Reply with quote

I've checked that everything is consistent with your instructions, and tried it on another computer (running Windows XP rather than WIndows 7), with the same result.

Just to make sure-- Where is the "Hello" message supposed to be displayed? (I had assumed that it would be displayed in the same console window from which the command was issued).
_________________
Dr. Phillip M. Feldman
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Sat Jul 14, 2012 2:08 pm    Post subject: Reply with quote

Hello

The example of B Marcelly works perfect for me.
I see the comment in OOo.

Try to reset the user profile. Maybe it can help
http://user.services.openoffice.org/en/forum/viewtopic.php?f=74&t=12426

Romke
_________________
OOo 3.4.5 on openSUSE 12.1
Use this forum : http://user.services.openoffice.org/en/forum
Back to top
View user's profile Send private message
pfeldman
General User
General User


Joined: 13 Sep 2008
Posts: 16

PostPosted: Sat Jul 14, 2012 6:36 pm    Post subject: Reply with quote

If I open the document, go to Tools/Macros/OpenOffice.org Basic, select the macro, and click on the "Run" button, it runs (a dialog box opens and the message is displayed). But, this is not too useful to me because (A) I need to be able to invoke this from a command line, and (B) I need the message to be displayed in the same console window from which the command was issued. Can OpenOffice VBA do something like?

Thanks for the help!
_________________
Dr. Phillip M. Feldman
Back to top
View user's profile Send private message
B Marcelly
Super User
Super User


Joined: 12 May 2004
Posts: 1453
Location: France

PostPosted: Sat Jul 14, 2012 11:43 pm    Post subject: Reply with quote

Quote:
I need the message to be displayed in the same console window from which the command was issued.

The message will not appear in the console window. It appears in its own window, which is a child of the document window. Just like when you click the Run button.
If you did a redirection to standard output, its useless.
Do exactly as I described. Since you have the problem in different computers, you are certainly doing something wrong.
_________________
Bernard

OpenOffice.org 1.1.5 fr / Apache OpenOffice 4.0.1 / LibreOffice 4.1.0
MS-Windows 7 Home SP1
This forum is spammed, use instead Apache OpenOffice forums
Back to top
View user's profile Send private message Visit poster's website
pfeldman
General User
General User


Joined: 13 Sep 2008
Posts: 16

PostPosted: Sun Jul 15, 2012 9:55 am    Post subject: Reply with quote

I believe that I've following your instructions. Here are links to two screen shots:

http://postimage.org/image/lxlq0iu3j/

http://postimage.org/image/jrrf60qn3/
_________________
Dr. Phillip M. Feldman
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Sun Jul 15, 2012 11:14 am    Post subject: Reply with quote

Hello

When I try to run the example of B Marcelly in a text console then I get an error. I think this is normal.

A text console I start up without a window manager. When I start a normal terminal then it is inside the window manager.

Romke
_________________
OOo 3.4.5 on openSUSE 12.1
Use this forum : http://user.services.openoffice.org/en/forum
Back to top
View user's profile Send private message
pfeldman
General User
General User


Joined: 13 Sep 2008
Posts: 16

PostPosted: Sun Jul 15, 2012 2:37 pm    Post subject: Reply with quote

My main issue is that I cannot make any OOO macro code run. I'm not too particular about where messages get displayed, especially because in most cases I don't plan on generating any messages.

Phillip

P.S. On a Windows system, I know of only one way to open a terminal window--by running "C:\Windows\system32\cmd.exe". If there is a way to open a different type of terminal window, I'd be interesting in knowing how this is done.
_________________
Dr. Phillip M. Feldman
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Mon Jul 16, 2012 2:56 am    Post subject: Reply with quote

Hello

I have no idea for a solution for your problem. The reason I post here in this thread is to make clear to both of you that the example B Marcelly is working.

I don't know if there is a different in window when you start up the terminal in an other way. The way I would test it is, to make a new user on the same computer and start testing if it works then.

I have no idea about the reason of your problem. I do assume that you use the same command line as in the example.

Romke
_________________
OOo 3.4.5 on openSUSE 12.1
Use this forum : http://user.services.openoffice.org/en/forum
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group