Periodically calling a macro

[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

[Villeroy]

Implement a timer macro in some professional language and let it call your Basic code (or better transfer everything to some real language).

[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

[Villeroy]

Do
ThisComponent.recalculateAll()
wait 3000
Loop

[Charlie Young]

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.

[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...

[karolus]

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.