Re: [libreoffice-users] Confused with Macro results??

Basically, I want the macro to be able to work completely on its own.
Agree that having to do the steps with the F2 and other keys is a problem.
Not sure why the recorder refuses to record those steps??


The original formula is
=IF(DATEDIF(A2,A1,y),IF(DATEDIF(A2,A1,y)=1,1 
Year,DATEDIF(A2,A1,y)Years),)IF(MOD(DATEDIF(A2,A1,m),12),IF(MOD(DATEDIF(A2,A1,m),12)=1,1 
Month,MOD(DATEDIF(A2,A1,m),12)Months),)IF(DATEDIF(A2,A1,md),IF(DATEDIF(A2,A1,md)=1,1 
Day,DATEDIF(A2,A1,md)Days),)


But to use the formula in a different location or with dates in different relative 
locations would require one to manually edit the formula and change all the 
A2,A1 settings to the new settings. Doing it 9 times.


Thus the macro prompts for the new addresses, and changes them all.
Problem is it leaves the formula contents displayed rather than the result??
Requiring the use of the F2 key and other keys.


Have found the copy paste isn't required, but it changes the manual key options 
require to get the results.


sub Z2
rem ----------------------------------------------------------------------
rem define variables
dim documentas object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document= ThisComponent.CurrentController.Frame
dispatcher = createUnoService(com.sun.star.frame.DispatchHelper)


rem ----------------------------------------------------------------------
dim sText
sText = InputBox (Please enter Date Cells Example D2,D1:)


rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name =StringName
args1(0).Value ==SUBSTITUTE(SUBSTITUTE(=IF(DATEDIF(A2,A1,_y_),IF(DATEDIF(A2,A1,_y_)=1,_1 Year 
_,DATEDIF(A2,A1,_y_)_ Years 
_),__)IF(MOD(DATEDIF(A2,A1,_m_),12),IF(MOD(DATEDIF(A2,A1,_m_),12)=1,_1 
Month _,MOD(DATEDIF(A2,A1,_m_),12)_ Months 
_),__)IF(DATEDIF(A2,A1,_md_),IF(DATEDIF(A2,A1,_md_)=1,_1 Day 
_,DATEDIF(A2,A1,_md_)_ Days_),__),A2,A1,chr$(34)sTextchr$(34)),_,CHAR(34))


dispatcher.executeDispatch(document,.uno:EnterString,, 0, args1())
msgbox (To complete process,+chr$(13)+Formula Press F2 then F9 then 
Enter then Enter+ chr$(13)+For Text Result Press F2 then F9 then Enter then 
F9 then Enter then Enter)


end sub


The ideal would be that the macro does the whole thing rether than requiring 
the user to press 4 or 6 additional key strokes??




On 5 Jul 2020 at 1:33, Regina Henschel wrote:


Subject:Re: [libreoffice-users] Confused with Macro 
results??
To:users@global.libreoffice.org
From:Regina Henschelrb.henschel@t-online.de
Date sent:Sun, 5 Jul 2020 01:33:10 +0200


Hi Michael,


your post is hard to understand. Please write, what you want to achieve
with your macro.


Kind regards
Regina


Michael D. Setzer II schrieb am 05-Jul-20 um 00:30:
Been doing a lot of googling, but finding lots of stuff, but nothing that works?
Have done a lot of versions and this is the latest one.
The record process does not allow for the use of the F2 key, so as is, it
results in the original args1(0).Value being in the cell?
After the macro ends, I've found that if I manually press F2 then a space and
enter it puts the formula as I originally wanted? F2 and enter does nothing??
I've also found that if I manually do F2 F9 Enter Enter, it will place just the
final text result in cell.


Seems there use to be a Keypress option, that would allow for simulating
keys, but that has been deprecated?
Also, found pages that talk about API stuff to modify cells, but the two I tried
did nothing?


Am I missing something??


Note: In string I replaced the originals with _ because it kept giving me
messages about unbalanced ()?? That got rid of the errors, and then just
converted them back?


Not and important macro, but was just hoping to find a way to make it work
the way I originally planned. Just playing around with stuff.


Thanks. Perhaps I am overlooking something very simple..




sub Z1
rem ----------------------------------------------------------------------
rem define variables
dim documentas object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document= ThisComponent.CurrentController.Frame
dispatcher = createUnoService(com.sun.star.frame.DispatchHelper)


rem ----------------------------------------------------------------------
dim sText
sText = InputBox (Please enter Date Cells Example D2,D1:)


rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name =StringName
args1(0).Value =
=SUBSTITUTE(SUBSTITUTE(=IF(DATEDIF(A2,A1,_y_),IF(DATEDIF(A2,
A1,_y_)=1,_1 Year _,DATEDIF(A2,A1,_y_)_ Years
_),__)IF(MOD(DATEDIF(A2,A1,_m_),12),IF(MOD(DATEDIF(A2,A1,_m_),1
2)=1,_1 Month _,MOD(DATEDIF(A2,A1,_m_),12)_ Months
_),__)IF(DATEDIF(A2,A1,_md_),IF(DATEDIF(A2,A1,_md_)=1,_1 Day
_,DATEDIF(A2,A1,_md_)_ Days_),__),A2,A1,chr$(34)sText
chr$(34)),_,CHAR(34))


dispatcher.executeDispatch(document,.uno:EnterString,, 0, args1())


rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document,.uno:Copy,, 0, Array())


rem ----------------------------------------------------------------------
REM Cut contents of cell to avoid the overwrite message
dispatcher.executeDispatch(document,.uno:Cut,, 0, Array())


rem ----------------------------------------------------------------------
dim args2(5) as new com.sun.star.beans.PropertyValue
args2(0).Name =Flags
args2(0).Value =SVD
args2(1).Name =FormulaCommand
args2(1).Value = 0
args2(2).Name =SkipEmptyCells
args2(2).Value = false
args2(3).Name =Transpose
args2(3).Value = false
args2(4).Name =AsLink
args2(4).Value = false
args2(5).Name =MoveMode
args2(5).Value = 4


dispatcher.executeDispatch(document,.uno:InsertContents,, 0, args2())
msgbox (To complete process,+chr$(13)+Formula Press F2 then Space
then Enter+ chr$(13)+For Text Result Press F2 then F9 then Enter then
Enter)


end sub


+------------------------------------------------------------+
Michael D. Setzer II - Computer Science Instructor (Retired)
mailto:mikes@guam.net
mailto:msetzerii@gmail.com
Guam - Where America's Day Begins
G4L Disk Imaging Project maintainer
http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+




--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy




-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy