Copy & Paste Formatted Date is changing format when Pasting from Format>Number>Date to the numeric format for date in both the copied cell and the pasted cell.
Here is the example of what it should be:
| 09/09/24 | 09/10/24 | 09/11/24 | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Mon | Tue | Wed |
Here is the example of what actually appears:
| 09/09/24 | 45,545.00 | 45,546.00 | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Mon | Tue | Wed |
What is really weird is that if copy and paste is done manually the paste works correctly. However, the copy and paste is a subroutnne in a macro that I’ve used for years and this only occurs when I run the complete macro. I can run the subroutine seperately using Paste or Paste Special and the strange formatting does not occur. However when the entire macro is run the strange reformatting of the copied source and the pasted results are always reformatted. I have no idea of any way to solve this, however I have forund a cumbefrson work around by placing the cursor on the row number that the numeric format and selecting Format>Number>Date, but since recording macros does not support mouse selections I can’t put this into a macro subroutine.
FYI: Here is the subroutine macro code:
sub CopyDateDay
rem ----------------------------------------------------------------------
rem define variables
dim document as 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 args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = “By”
args1(0).Value = 3
args1(1).Name = “Sel”
args1(1).Value = false
dispatcher.executeDispatch(document, “.uno:GoLeft”, “”, 0, args1())
rem ----------------------------------------------------------------------
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = “By”
args2(0).Value = 11
args2(1).Name = “Sel”
args2(1).Value = false
dispatcher.executeDispatch(document, “.uno:GoUp”, “”, 0, args2())
rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = “By”
args3(0).Value = 1
dispatcher.executeDispatch(document, “.uno:GoDownSel”, “”, 0, args3())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, “.uno:Copy”, “”, 0, Array())
rem ----------------------------------------------------------------------
dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name = “By”
args5(0).Value = 5
args5(1).Name = “Sel”
args5(1).Value = false
dispatcher.executeDispatch(document, “.uno:GoRight”, “”, 0, args5())
rem ----------------------------------------------------------------------
'dispatcher.executeDispatch(document, “.uno:Paste”, “”, 0, Array())
rem ----------------------------------------------------------------------
dim args6(5) as new com.sun.star.beans.PropertyValue
args6(0).Name = “Flags”
args6(0).Value = “SDFT”
args6(1).Name = “FormulaCommand”
args6(1).Value = 0
args6(2).Name = “SkipEmptyCells”
args6(2).Value = false
args6(3).Name = “Transpose”
args6(3).Value = false
args6(4).Name = “AsLink”
args6(4).Value = false
args6(5).Name = “MoveMode”
args6(5).Value = 4
dispatcher.executeDispatch(document, “.uno:InsertContents”, “”, 0, args6())
end sub
Note that I have left the original Paste line in the code with a ‘ at the biginning of that line and have the Paste Special code active. Both misbehave. This first started with the new 24.2.3 version. It occurs on two Windows 64bit OS. One is Windows 10 and one is Windows 11. It also occurs in LO safe mode.
If desired to work on a solution, I can provide the spreadsheet and complete macro. If this is a bug then can someone get it into a bug report. Doing it in Bugzilla is beyond my limited ability.
Thanks in advance. Copy & Paste Formatted Date is changing format when Pasting from Format>Number>Date to the numeric format for date in both the copied cell and the pasted cell.
Here is the example of what it should be:
| 09/09/24 | 09/10/24 | 09/11/24 | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Mon | Tue | Wed |
Here is the example of what actually appears:
| 09/09/24 | 45,545.00 | 45,546.00 | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Mon | Tue | Wed |
What is really weird is that if copy and paste is done manually the paste works correctly. However, the copy and paste is a subroutnne in a macro that I’ve used for years and this only occurs when I run the complete macro. I can run the subroutine seperately using Paste or Paste Special and the strange formatting does not occur. However when the entire macro is run the strange reformatting of the copied source and the pasted results are always reformatted. I have no idea of any way to solve this, however I have forund a cumbefrson work around by placing the cursor on the row number that the numeric format and selecting Format>Number>Date, but since recording macros does not support mouse selections I can’t put this into a macro subroutine.
FYI: Here is the subroutine macro code:
sub CopyDateDay
rem ----------------------------------------------------------------------
rem define variables
dim document as 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 args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = “By”
args1(0).Value = 3
args1(1).Name = “Sel”
args1(1).Value = false
dispatcher.executeDispatch(document, “.uno:GoLeft”, “”, 0, args1())
rem ----------------------------------------------------------------------
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = “By”
args2(0).Value = 11
args2(1).Name = “Sel”
args2(1).Value = false
dispatcher.executeDispatch(document, “.uno:GoUp”, “”, 0, args2())
rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = “By”
args3(0).Value = 1
dispatcher.executeDispatch(document, “.uno:GoDownSel”, “”, 0, args3())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, “.uno:Copy”, “”, 0, Array())
rem ----------------------------------------------------------------------
dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name = “By”
args5(0).Value = 5
args5(1).Name = “Sel”
args5(1).Value = false
dispatcher.executeDispatch(document, “.uno:GoRight”, “”, 0, args5())
rem ----------------------------------------------------------------------
'dispatcher.executeDispatch(document, “.uno:Paste”, “”, 0, Array())
rem ----------------------------------------------------------------------
dim args6(5) as new com.sun.star.beans.PropertyValue
args6(0).Name = “Flags”
args6(0).Value = “SDFT”
args6(1).Name = “FormulaCommand”
args6(1).Value = 0
args6(2).Name = “SkipEmptyCells”
args6(2).Value = false
args6(3).Name = “Transpose”
args6(3).Value = false
args6(4).Name = “AsLink”
args6(4).Value = false
args6(5).Name = “MoveMode”
args6(5).Value = 4
dispatcher.executeDispatch(document, “.uno:InsertContents”, “”, 0, args6())
end sub
Note that I have left the original Paste line in the code with a ‘ at the biginning of that line and have the Paste Special code active. Both misbehave. This first started with the new 24.2.3 version. It occurs on two Windows 64bit OS. One is Windows 10 and one is Windows 11. It also occurs in LO safe mode.
If desired to work on a solution, I can provide the spreadsheet and complete macro. If this is a bug then can someone get it into a bug report. Doing it in Bugzilla is beyond my limited ability.
Thanks in advance.