how to make macro execute?

pfeldman · General User Joined: 13 Sep 2008 Posts: 16

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

patel · Power User Joined: 14 Apr 2012 Posts: 54 Location: Italy

tu run your macro add this

B Marcelly · Posted: Tue Jul 10, 2012 3:54 am Post subject:

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:

pfeldman · General User Joined: 13 Sep 2008 Posts: 16

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

B Marcelly · Posted: Thu Jul 12, 2012 5:47 am Post subject:

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.

pfeldman · General User Joined: 13 Sep 2008 Posts: 16

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

B Marcelly · Posted: Fri Jul 13, 2012 11:07 pm Post subject:

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 / OpenOffice.org 3.4.1 en-US + langpacks, MS-Windows XP Home SP3
This forum is unusable, use instead Apache OpenOffice forums

pfeldman · General User Joined: 13 Sep 2008 Posts: 16

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

RPG · Posted: Sat Jul 14, 2012 2:08 pm Post subject:

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

pfeldman · General User Joined: 13 Sep 2008 Posts: 16

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

B Marcelly · Posted: Sat Jul 14, 2012 11:43 pm Post subject:

pfeldman · General User Joined: 13 Sep 2008 Posts: 16

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

RPG · Posted: Sun Jul 15, 2012 11:14 am Post subject:

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

pfeldman · General User Joined: 13 Sep 2008 Posts: 16

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

RPG · Posted: Mon Jul 16, 2012 2:56 am Post subject:

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