Replace cell contents with a formula that is not a string

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Panda47
Posts: 4
Joined: Fri Aug 31, 2018 11:24 pm

Replace cell contents with a formula that is not a string

Post by Panda47 »

Hello.

I have a few cells with forumlas that update from reading a file when opening the document.
But sometimes, I can allow a user to enter a new value (that is not yet available on the file) to get better result.

The problem is if the user saves it, then the formula is lost. I can replace the cell contents with a simple macro string replacment, but that gets treated as text and not a formula. What am I doing wrong?

Code: Select all

sub ReplaceFormula

	dim document as object
	
	document = ThisComponent
	
	'100's   - on the 2nd sheet
	sheet = document.Sheets(1)
	sheet.getCellByPosition(2, 2).String = "=SUMPRODUCT(('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$O$2:INDEX(data.$O:$O,COUNTA(data.$O:$O))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$P$2:INDEX(data.$P:$P,COUNTA(data.$P:$P))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$Q$2:INDEX(data.$Q:$Q,COUNTA(data.$Q:$Q))>=4)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$R$2:INDEX(data.$R:$R,COUNTA(data.$R:$R))>=4)=4)"

	'75's
	sheet = document.Sheets(1)
	sheet.getCellByPosition(2, 3).String = "=SUMPRODUCT(('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$O$2:INDEX(data.$O:$O,COUNTA(data.$O:$O))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$P$2:INDEX(data.$P:$P,COUNTA(data.$P:$P))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$Q$2:INDEX(data.$Q:$Q,COUNTA(data.$Q:$Q))>=4)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$R$2:INDEX(data.$R:$R,COUNTA(data.$R:$R))>=4)=3)"

	'50's
	sheet = document.Sheets(1)
	sheet.getCellByPosition(2, 4).String = "=SUMPRODUCT(('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$O$2:INDEX(data.$O:$O,COUNTA(data.$O:$O))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$P$2:INDEX(data.$P:$P,COUNTA(data.$P:$P))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$Q$2:INDEX(data.$Q:$Q,COUNTA(data.$Q:$Q))>=4)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$R$2:INDEX(data.$R:$R,COUNTA(data.$R:$R))>=4)=2)"

	'25's
	sheet = document.Sheets(1)
	sheet.getCellByPosition(2, 5).String = "=SUMPRODUCT(('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$O$2:INDEX(data.$O:$O,COUNTA(data.$O:$O))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$P$2:INDEX(data.$P:$P,COUNTA(data.$P:$P))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$Q$2:INDEX(data.$Q:$Q,COUNTA(data.$Q:$Q))>=4)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$R$2:INDEX(data.$R:$R,COUNTA(data.$R:$R))>=4)=1)"

	'0's
	sheet = document.Sheets(1)
	sheet.getCellByPosition(2, 6).String = "=SUMPRODUCT(('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$O$2:INDEX(data.$O:$O,COUNTA(data.$O:$O))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$P$2:INDEX(data.$P:$P,COUNTA(data.$P:$P))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$Q$2:INDEX(data.$Q:$Q,COUNTA(data.$Q:$Q))>=4)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$R$2:INDEX(data.$R:$R,COUNTA(data.$R:$R))>=4)=0)"


end sub
Thanks for your time.
-David
LibreOffice 5.3.7.2 on Windows 7
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Replace cell contents with a formula that is not a strin

Post by robleyd »

What am I doing wrong?
Not reading the documentation? https://wiki.openoffice.org/wiki/Docume ... and_Ranges
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Post Reply