excel macro call from java

[GrahamS]

I would like to run a java program as a front which calls excel to run in the
background, I want to be able to run macros in the excel file and also to
grab data that it outputs

Is this possible? Can you help point me in the right direction?

many thanks!

 

[gimme_this_gimme_that]

If you want to write an Excel file from Java you'd use POI. See here:
http://jakarta.apache.org/poi/

Now, if you want to write a program that reads and writes to an Excel
file I'd recommend that you use Perl.

See my post on MySql/Perl/Excel and my next post on reading an Excel
from Perl.

Yes, it's all possible. The advantage of not using POI is that your
workbook can include Charts, Pivot Tables, and VBA modules.

 

[JE McGimpsey]

I would like to run a java program as a front which calls excel to run in the
background, I want to be able to run macros in the excel file and also to
grab data that it outputs

Is this possible? Can you help point me in the right direction?

It's certainly possible to control XL via Applescript directly from Java:

http://developer.apple.com/documentation/Java/Conceptual/Java14Developmen
t/07-NativePlatformIntegration/NativePlatformIntegration.html

http://www.oreillynet.com/pub/a/mac/2003/02/25/apple_scripting.html

and you can use AppleScript's run VB macro command to call your macros,
though there are significant limitations (you can't pass objects via run
VB macro - only values).

 

[gimme_this_gimme_that]

Most Java developers would opt for something that wouldn't require OS
X to run on, so POI would be the tool of choice.

One advantage of POI is that you can write your spreadsheet to an
output stream from within a web container.

So POI is useful in instances where you want to create a report and
the user needs it in an Excel Workbook.

With a Java/Applescript solution you'd have to have Java create the
Workbook with Applescript, save it to disk using Applescript, and then
you'd have to read the Workbook from the file system to output it from
a web page.

I mentioned this before, but you can't create charts, pivot tables or
VBA macros using POI. A Java/Applescript or Perl/Applescript Excel
Workbook can have anything you want it in. You just have the funky
issue of reading and writing from the file system.

Regarding this ...

and you can use AppleScript's run VB macro command to call your macros,
though there are significant limitations (you can't pass objects via run
VB macro - only values).

This is much of a limitation assuming that you can both read AND write
to spreadsheets using the Java/Applescript interface.

With Perl read and write to spreadsheets.

 

[JE McGimpsey]

Most Java developers would opt for something that wouldn't require OS
X to run on, so POI would be the tool of choice.

Except that POI can't run XL macros, which was the primary specification
of the OP, so it's not even an option.

There are very likely better options for the overall task, which may
involve POI or something else entirely, but it's impossible to know with
the information given.

 

[gimme_this_gimme_that]

There are very likely better options for the overall task, which may
involve POI or something else entirely, but it's impossible to know with
the information given.

Oh right. I didn't pay attention to the VBA Macro part.

Yes, lately M$ has been pushing Sharepoint/ReportServer as the way to
make Excel reports.

Again, no Charts or VBA Modules, but it nicely supports Active
Directory authentication.

 

[gimme_this_gimme_that]

Incidentally the AppleScript you'd call might look something like this
(untested):

tell application "Microsoft Excel"
activate
set ws to open workbook workbook file name "Macintosh
HD:Users:ann_example:MyWorkbook.xls"
run VB macro "MyVBAMacro" of ws
end tell

In MyWorkbook.xls you'd have:

Sub MyVBAMacro
MsgBox "Hello World"
end Sub

 

[GrahamS]

Thank you for all of your speedy replies - I was looking down the java road
as I already had a GUI developed through a java jar file I had created for a
different project - wanted to kill two birds with one stone - I have decided
to use C sharp instead to develop this project - I guess the question now is
-

How can I call a excel macro through C-sharp?

many thanks

 

[JE McGimpsey]

Thank you for all of your speedy replies - I was looking down the java road
as I already had a GUI developed through a java jar file I had created for a
different project - wanted to kill two birds with one stone - I have decided
to use C sharp instead to develop this project - I guess the question now is
-

How can I call a excel macro through C-sharp?

No clue - I've never used C#/.Net framework on the Mac. I've used XCode
to build a few Objective-C programs that used apple events to drive
Office, but that's a very different environment.

 

[gimme_this_gimme_that]

You're kidding right? Create an Excel ActiveX Object and call
Application.Run "MyMacro" on it.
There have to be thousands of examples on the web.

 

[JE McGimpsey]

You're kidding right? Create an Excel ActiveX Object and call
Application.Run "MyMacro" on it.
There have to be thousands of examples on the web.

ActiveX Object for MacXL?

 

[Phillip Jones, CET]

ActiveX Object for MacXL?

I have the same reaction.! Because Active-X is strictly a Microsoft
thing and doesn't even exist in the Macintosh Library hardware or
software wise. Active-X has been found to be so dangerous, so easy with
Active-X knowledge to reek all kinds of havoc on Computers that the fine
time at Apple designed Mac to ignore Active-X.

Reports are The even IE 7 has tuned off as normal Preference, and will
eventually abandon it; as soon as they can get big ticket customers
weaned away from it.

As a rule, I don't like any program stuff like, Active-X, Apple script,
VBA. I only know that PERL exist and wouldn't use it if I could.

Each time you use an outside programing language or script you leave
your self open for all kinds of nasties. The computing world is not a
bit safe especially so in the MS-Windows World. Not quite as much, in
the Mac World.

--
------------------------------------------------------------------------
Phillip M. Jones, CET |MEMBER:VPEA (LIFE) ETA-I, NESDA,ISCET, Sterling
616 Liberty Street |Who's Who. PHONE:276-632-5045, FAX:276-632-0868
Martinsville Va 24112 |[email protected], ICQ11269732, AIM pjonescet
------------------------------------------------------------------------

If it's "fixed", don't "break it"!

mailto[email protected]

<http://www.kimbanet.com/~pjones/default.htm>
<http://www.kimbanet.com/~pjones/90th_Birthday/index.htm>
<http://www.kimbanet.com/~pjones/Fulcher/default.html>
<http://www.kimbanet.com/~pjones/Harris/default.htm>
<http://www.kimbanet.com/~pjones/Jones/default.htm>

<http://www.vpea.org>

 

[JE McGimpsey]

Active-X has been found to be so dangerous, so easy with
Active-X knowledge to reek all kinds of havoc on Computers that the
fine time at Apple designed Mac to ignore Active-X.

Oh, come on Phillip - that's not true. The frameworks required for
ActiveX were not included in MacOS, but there's nothing in principle
that would prevent ActiveX from being ported to Macs (as it was pre-Mac
OSX).

Reports are The even IE 7 has tuned off as normal Preference, and will
eventually abandon it; as soon as they can get big ticket customers
weaned away from it.

If it were *that* dangerous, the "big ticket" customers wouldn't have
hesitated to go cold turkey years ago. Granted, some have. Others have
done a risk assessment and found that the risk is acceptable with some
precautions.

Yes, ActiveX can be a security vulnerability. But with reasonable care,
it's very useful. ESPECIALLY ActiveX controls you develop for yourself
or are developed by trustworthy programmers. Those controls CAN'T be
used for evil by others.

And yes, it's going away, because the OS security frameworks are getting
tighter for all sorts of reasons (only one of which is ActiveX), which
will make ActiveX unlikely to be supportable.

As a rule, I don't like any program stuff like, Active-X, Apple script,
VBA. I only know that PERL exist and wouldn't use it if I could.

It's a good idea not to use tools for which one doesn't feel comfortable
with the limitations. Your not liking program stuff like Applescript
significantly limits your ability to do more than plain-vanilla computer
operations. That's fine for you, and I wouldn't recommend you change.

Others of us, however, need to use our computers in different ways than
some engineers at Apple or Microsoft or wherever decided we should. I
use Applescripts, Automator workflows, shell scripts, PERL, VBA, and a
wide variety of other programming methods to make my computer use
efficient, as well as more pleasant. I configure my machine to do what I
want, rather than adapting my usage to what someone else provides.

Am I careful about running "programming stuff" provided by others? Sure.
I'll sandbox something completely new, and I examine code from those I
don't know. But I'm more efficient for my clients, and have much more
time for myself and my family by using "stuff".

Each time you use an outside programing language or script you leave
your self open for all kinds of nasties. The computing world is not a
bit safe especially so in the MS-Windows World. Not quite as much, in
the Mac World.

If you think you're safe using prepackaged software, you're deluding
yourself. Think about it - if Microsoft packaged, presumably
intentionally, some sort of software bomb in MacOffice, you'd have no
clue until it blew up your machine.

 

[GrahamS]

in case some one wants to know the solution I found here it is:

I found that the Java POI did not satisfy my needs as I needed to run macros
within the excel spreadsheet. I turned my attention to C# - which I had
never used before but it is very easy to do pretty much everything inside of
excel using the following libraries provided (not an activex control):

using Excel = Microsoft.Office.Interop.Excel;
using VBIDE = Microsoft.Vbe.Interop;
using Office = Microsoft.Office.Core;

thank you for all your replies - although they did not slove my problem
directly they did help me find my way to the solution I needed