Periodically calling a macro

Java, C++, C#, Delphi... - Using the UNO bridges
Post Reply
saleem145
Posts: 130
Joined: Mon Jul 02, 2012 4:47 pm

Periodically calling a macro

Post by saleem145 »

Hello,

Once I open a spreadsheet I want to periodically call a macro (say once every second). The macro is in OOBasic....

Thanks for your help,

Saleem
OpenOffice 3.4.0
Mac OS X 10.5.8
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Periodically calling a macro

Post by Villeroy »

Implement a timer macro in some professional language and let it call your Basic code (or better transfer everything to some real language).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
saleem145
Posts: 130
Joined: Mon Jul 02, 2012 4:47 pm

Re: Periodically calling a macro

Post by saleem145 »

How can a C++ function implemented in an addin call an OOBasic Macro??

All I actually want to do is a perform a full recalculate every 3 seconds...

Saleem
OpenOffice 3.4.0
Mac OS X 10.5.8
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Periodically calling a macro

Post by Villeroy »

Do
ThisComponent.recalculateAll()
wait 3000
Loop
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Periodically calling a macro

Post by Charlie Young »

saleem145 wrote:How can a C++ function implemented in an addin call an OOBasic Macro??

All I actually want to do is a perform a full recalculate every 3 seconds...

Saleem
I see Villeroy has provided the Basic macro, but here's another version, which accepts the number of seconds as a parameter:

Code: Select all

Sub CalcDoc(secs As Long)
	Do While True
		Wait(1000 * secs)
		ThisComponent.calculateAll()
	Loop
End Sub
Now, you can call a Basic macro from an addin function, but in this case that isn't really viable, as I'll explain below.
Consider this idl

Code: Select all

long RecalcDocFromBasic([in] long secs, [in] ::com::sun::star::beans::XPropertySet docProps);


Then this implementation

Code: Select all

long SAL_CALL MyServiceImpl::RecalcDocFromBasic(long secs,const Reference<XPropertySet> & docProps )
		throw (RuntimeException)
{
	Sequence<Any> a(1);
	Sequence<sal_Int16> b(0);
	Sequence<Any> c(0);
	Reference < XInterface  > Desktop = rOfficeServiceManager->createInstance(OUString::createFromAscii( "com.sun.star.frame.Desktop" ));
	Reference< XDesktop > xDesktop(Desktop, UNO_QUERY);
	Reference< XComponent > xcomponent = xDesktop->getCurrentComponent();
	Reference< XSpreadsheetDocument > oDoc (xcomponent, UNO_QUERY);
	Reference<XScriptProviderSupplier> xSupplier(oDoc,UNO_QUERY);
	Reference<XScriptProvider> xProvider = xSupplier->getScriptProvider();
	Reference<XScript> calcScript = xProvider->getScript(OUString( RTL_CONSTASCII_USTRINGPARAM("vnd.sun.star.script:Standard.Module1.CalcDoc?language=Basic&location=document")));
	a[0] <<= (sal_Int32) secs;
	calcScript->invoke(a,b,c);
	return secs;
}
Here there is a side issue to discuss as well, which I don't think we have ever come across here. This function needs a servicemanager to get the Desktop, so in the private section of MyServiceImpl, put

Code: Select all

static Reference< XMultiServiceFactory > rOfficeServiceManager;
then externally

Code: Select all

Reference< XMultiServiceFactory > MyServiceImpl::rOfficeServiceManager;
In the public part of the declaration we need

Code: Select all

static void getServiceMgr(Reference< XComponentContext >);


Then externally

Code: Select all

void MyServiceImpl::getServiceMgr(Reference< XComponentContext > rComponentContext)
{
	Reference< XMultiComponentFactory > rServiceManager = rComponentContext->getServiceManager();
	rOfficeServiceManager = Reference< XMultiServiceFactory >(rServiceManager, UNO_QUERY);
}
And on down XComponentContext comes in through create_MyServiceImpl, so we do

Code: Select all

Reference< XInterface > SAL_CALL create_MyServiceImpl(
    Reference< XComponentContext > const & xContext )
    SAL_THROW( () )
{
	MyServiceImpl::getServiceMgr(xContext);
    return static_cast< lang::XTypeProvider * >( new MyServiceImpl() );
}

Now this ultimately doesn't work, because if we put in a cell in a document that does contain CalcDoc

Code: Select all

=RECALCDOCFROMBASIC(3)
.
The CalcDoc macro is indeed started, but because it is in an infinite loop, the RECALCDOCFROMBASIC won't return until the macro terminates, which must be forced with Ctrl-Shift-Q (unless one wishes to force-close the document).

I think I know where this is going, and I am playing with a more interesting volatile function, so I'll have more to say at some point.
Apache OpenOffice 4.1.1
Windows XP
saleem145
Posts: 130
Joined: Mon Jul 02, 2012 4:47 pm

Re: Periodically calling a macro

Post by saleem145 »

Code: Select all

Sub CalcDoc(secs As Long)
   Do While True
      Wait(1000 * secs)
      ThisComponent.calculateAll()
   Loop
End Sub
This is good enough for me. I can call the macro when I open the spreadsheet and am good. One question though -- will Wait cause the GUI to freeze up?? I think the macro might need to run in a seperate thread...I am going to try it...
OpenOffice 3.4.0
Mac OS X 10.5.8
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Periodically calling a macro

Post by karolus »

saleem145 wrote:

Code: Select all

Sub CalcDoc(secs As Long)
   Do While True
      Wait(1000 * secs)
      ThisComponent.calculateAll()
   Loop
End Sub
This is good enough for me. I can call the macro when I open the spreadsheet and am good. One question though -- will Wait cause the GUI to freeze up?? I think the macro might need to run in a seperate thread...I am going to try it...
That freezes your Basic-interpreter as long as it runs.
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Post Reply