|
|
| Line 1: |
Line 1: |
| − | == Introduction ==
| |
| | | | |
| − | <p>ooo-build contains new VBA model which allows you to run macros from your Microsoft Office documents. This model is under heavy development and is expected that lot of macros wont run for now.</p>
| |
| − |
| |
| − | === How to enable the VBA model ===
| |
| − |
| |
| − | <p>VBA model is enabled by default in the ooo-build now. It's enough to [[Getting_It|get]], [[Building|build]], [[Installing|install]] and [[Running|be able to run]] it.</p>
| |
| − |
| |
| − | === How can I help you ? ===
| |
| − |
| |
| − | <p>We need help in three areas - understanding the VBA usage patterns, implementation and testing; see below.</p>
| |
| − |
| |
| − | == Understanding the VBA usage patterns ==
| |
| − |
| |
| − | <p>VBA model is huge and we need help in understanding the VBA usage patterns for prioritizing as we progress. It's important to receive as many as possible Microsoft Office documents with macros for analysis. '''We are interested in documents from developers and regular people too.'''</p>
| |
| − |
| |
| − | === Dumping the VBA from your MS Office documents ===
| |
| − |
| |
| − | ==== libgsf ====
| |
| − |
| |
| − | <p>If you're worried about the sheet data you can use ''test-msvba'' from the [http://freshmeat.net/projects/libgsf/ libgsf]. Download the latest [http://cvs.gnome.org/viewcvs/libgsf/tests/test-msvba.c test-msvba.c] file and compile it with the following command:</p>
| |
| − |
| |
| − | <pre>gcc `pkg-config --libs --cflags libgsf-1` -o test-msvba test-msvba.c</pre>
| |
| − |
| |
| − | <p>Note: you need to have libgsf-devel package installed.</p>
| |
| − |
| |
| − | <p>Now, you can dump the VBA from your documents with the following command:</p>
| |
| − |
| |
| − | <pre>find -name '*.xls' -exec sh -c 'test-msvba {} >> ~/excel-vba-stream-for-nicel.bas' \;</pre>
| |
| − |
| |
| − | ==== pole ====
| |
| − |
| |
| − | <p>Or you can use [http://pole.berlios.de/ POLE] with GUI. POLE is a portable C++ library to access structured storage. It is designed to be compatible with Microsoft structured storage, also sometimes known as OLE Compound Document.</p>
| |
| − |
| |
| − | === Exporting modules from the Microsoft Excel ===
| |
| − |
| |
| − | <p>Open your document and edit associated macros. You can see list of modules on the left side of your screen. Use the right mouse button and select ''Export'' from the context menu. The whole module will be saved as one huge ''*.bas''.</p>
| |
| − |
| |
| − | === Where to send ===
| |
| − |
| |
| − | <p>If you are going to send pure VBA streams, send them directly to [mailto:mnicel@novell.com Nicel]. Otherwise send all documents to [mailto:rvojta@openoffice.org Robert] and he will dump the VBA streams for the Nicel to save his time.</p>
| |
| − |
| |
| − | == Implementation ==
| |
| − |
| |
| − | === The desired sequence ===
| |
| − |
| |
| − | ==== Add an extension to the IDL ====
| |
| − |
| |
| − | The IDL lives in offapi/org/openoffice/vba/*, the IDL syntax can easily be gleaned from a few other IDL files. Two things
| |
| − | are worth bearing in mind:
| |
| − | * it's important that the argument names are correct, so that the <tt>MsgBox title:="hello"</tt> syntax works correctly.
| |
| − | * for optional arguments, or variant types you need to use the <tt>any</tt> type
| |
| − |
| |
| − | Having edited the IDL extension you need to:
| |
| − | * run: <tt>build && deliver</tt> in offapi (perhaps remove types.db first),
| |
| − | * run: <tt>build && deliver</tt> in the offuh toplevel directory. (This (re-)compiles C++ headers etc. for that interface).
| |
| − |
| |
| − | ==== Add the extension to the implementation ====
| |
| − |
| |
| − | The impl. code lives in <tt>sc/source/ui/vba/</tt> - find the right header & source files.
| |
| − | Add the method (with the correct prototype) to the header - often it's easiest to cut/paste that from the compiled C++ headers in <tt>solenv/*/*/inc/org/openoffice/vba/</tt>
| |
| − |
| |
| − | Then implement the method itself in the .cxx file. Run <tt>build debug=true</tt>
| |
| − |
| |
| − | ==== Add new constants to the IDL ====
| |
| − |
| |
| − | VBA has alot of constants, we need help implementing similar constants in our api, implementing VBA constants is easy and would be a good place to start if you want to make an immediate contribution and learn about the project at the same time. To see the values and names for the VBA constants just open the "Object Browser" from the Visual Basic IDE, search for constants and you'll find 'em. Look in <tt>offapi/org/openoffice/vba/*/*</tt> to see whats missing (lots!!) Create a new IDL file for each "class" of constants, use <tt>offapi/org/openoffice/vba/Excel/XlCellType.idl</tt> as an example. The steps for building and adding the new constants is the same as described above for a extension, the only difference is that there are no source files to create :-)
| |
| − |
| |
| − | ==== Running it ====
| |
| − |
| |
| − | You did use [linkoo] right ? - in which case - just re-run soffice.bin (killall -9 soffice.bin first to ensure there is no lurking factory process).
| |
| − |
| |
| − | Now of course - you want to add a new test to exercise it to ooo-build/test/macro/*. And of course loop back to the begginning.
| |
| − | Once you've got something you want to share - a simple <tt>cvs diff -u | tee /tmp/foo.diff</tt> in ooo-build/scratch will yield a patch you can mail to Nicel / the ooo-build mailing list to get your code folded in.
| |
| − | NB. it's important you sign the [http://www.openoffice.org/licenses/jca.pdf JCA] first.
| |
| − |
| |
| − | === The scratch directory ===
| |
| − |
| |
| − | <p>It's quite difficult to generate patches over already patched tree thus we have the ''ooo-build/scratch'' directory. Read the [http://cvs.gnome.org/viewcvs/ooo-build/scratch/README?view=markup ''ooo-build/scratch/README''] file to understand how the ''scratch'' directory works. Then move the ''scratch/sc-vba'' and the ''offapi-vba'' to proper places and link them back. Hack and generate patches against the ''scratch'' directories.</p>
| |
| − |
| |
| − | == Testing ==
| |
| − |
| |
| − | <p>It's extremely important to test the work which is already done. You can use test cases from the ''ooo-build/test/macro'' directory, you can extend them or you can write your own test cases.</p>
| |
| − |
| |
| − | === Brainstorming ===
| |
| − |
| |
| − | <p>Maybe, it's a good idea to write test cases directly in the Microsoft Excel?</p>
| |
| − |
| |
| − | == Accessing the VBA API from other langauges ==
| |
| − |
| |
| − | <p>The VBA API is implemented by an UNO service, the service is accessable via a singleton. As such it is possible to use the API from any language that has an UNO binding. The main entry points to the API are certain global variables { Application, ActiveWorkbook and ActiveSheet } or methods { WorkSheets, WorkBooks } exported by the service. Using these globals as a starting point the rest of the api can be accessed.</p>
| |
| − |
| |
| − | === Python ===
| |
| − |
| |
| − | <pre>
| |
| − | vba = uno.getComponentContext().getByName("/singletons/org.openoffice.vba.theGlobals")
| |
| − | sheet = vba.ActiveSheet
| |
| − | book = vba.ActiveWorkbook
| |
| − | </pre>
| |
| − |
| |
| − | === Java ===
| |
| − |
| |
| − | <pre>
| |
| − | import org.openoffice.vba.*;
| |
| − | "
| |
| − | // getComponentCtxFromSomewhere is a fictional
| |
| − | // routine representing acquisition of the component context
| |
| − | // from e.g. remote ServiceManage if the code is a remote client
| |
| − | // or from the XScriptContext if this were a java ScriptingFramework script
| |
| − |
| |
| − | XComponentContext ctx = getComponentCtxFromSomewhere();
| |
| − | Object oGlobs = ctx.getValueByName(
| |
| − | "/singletons/org.openoffice.vba.theGlobals");
| |
| − | XGlobals xGlobs = AnyConverter.toObject(
| |
| − | new Type(XGlobals.class), oGlobs);
| |
| − | XWorkBook xWBook = xGlobs.getActiveWorkbook();
| |
| − | XWorkSheet xSheet = xGlobs.getActiveWorkSheet();
| |
| − | </pre>
| |
| − |
| |
| − | === OO Basic ===
| |
| − |
| |
| − | Basic allows access to the global variables automatically just like VBA does, there is no need to create any services or access any singletons to use the API
| |
| − | <pre>
| |
| − | msgbox "Name of the current document is " & ActiveWorkbook.Path
| |
| − | </pre>
| |
