[Solved]Copy and paste date from one spreadsheet to another
-
- Posts: 52
- Joined: Wed Aug 22, 2018 5:47 pm
[Solved]Copy and paste date from one spreadsheet to another
I used getDataArray() and setDataArray() to copy one column from one spreadsheet to another.
It is copying number and text as it is but not date.
I want date to exactly copied but it is showing some number which is formatted as text.
Please suggest solution for this.
It is copying number and text as it is but not date.
I want date to exactly copied but it is showing some number which is formatted as text.
Please suggest solution for this.
Last edited by Kprogrammer on Thu Sep 06, 2018 1:32 pm, edited 2 times in total.
Open office 3.1 on Windows 7
Re: Copy and paste date from one spreadsheet to another
The number you are seeing is the value of the date. Dates are stored as the number of days since Dec 30, 1899. All you have to do is format the cells to have whichever date format you prefer.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
-
- Posts: 52
- Joined: Wed Aug 22, 2018 5:47 pm
Re: Copy and paste date from one spreadsheet to another
Thanks for your reply.
Date is 26-Jul-2018. when i am pasting it to another file it shows 43307. As you said this is the value of date from 30 Dec 1899. Now i want date exactly like this 26-Jul-2018.Which format is this. How can i code for this.
Date is 26-Jul-2018. when i am pasting it to another file it shows 43307. As you said this is the value of date from 30 Dec 1899. Now i want date exactly like this 26-Jul-2018.Which format is this. How can i code for this.
Open office 3.1 on Windows 7
Re: Copy and paste date from one spreadsheet to another
Replace DataArray with FormulaArray. The formula of a date value is 2018-07-26. Of course, this may lead to problems when the source data have actual formulas starting with =.
If this is not an option, cell styles can help a lot when you need to apply the right formatting to imported raw data.
If this is not an option, cell styles can help a lot when you need to apply the right formatting to imported raw data.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Copy and paste date from one spreadsheet to another
You should insert new cells where you dump your data array. This way you get all the formatting from the preceeding row and all references to the previous target area will automatically expand to the new size.
https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=93099
Merge sheets of spreadsheet documents
https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=93099
Merge sheets of spreadsheet documents
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 52
- Joined: Wed Aug 22, 2018 5:47 pm
Re: Copy and paste date from one spreadsheet to another
The file is opened in comma separated mode, then only dates are not getting copied exactly. when i am not using comma separated mode then it is copying correctly.
FOR COMMA SEPARATED MODE-
FileProperties(0).Name = "FilterName"
FileProperties(0).Value ="Text - txt - csv (StarCalc)"
FileProperties(1).Name = "FilterOptions"
FileProperties(1).value = "44,34,0,1"
opening file in comma seperated mode date is 07/26/18 which is 26-Jul-2018 in the file.
why when opening it in comma separated mode 26-Jul-2018 is converting to 07/26/18. How can i stop this conversion?Please guide
FOR COMMA SEPARATED MODE-
FileProperties(0).Name = "FilterName"
FileProperties(0).Value ="Text - txt - csv (StarCalc)"
FileProperties(1).Name = "FilterOptions"
FileProperties(1).value = "44,34,0,1"
opening file in comma seperated mode date is 07/26/18 which is 26-Jul-2018 in the file.
why when opening it in comma separated mode 26-Jul-2018 is converting to 07/26/18. How can i stop this conversion?Please guide
Open office 3.1 on Windows 7
Re: Copy and paste date from one spreadsheet to another
No conversion takes place.
Calc interpretes your date strings correctly and applies the default format for dates according to your current locale which is English (USA).
With a German number format locale 26-Jul-2018 shows up as 26.07.18 which is the correct value 43307 regardless of formatting.
The one and only thing that matters is that Calc imports the correct values from plain text files.
If this is a problem, you can import database data (csv is a database exchange format) through the Base component into preformatted cell ranges in Calc (or better forget the whole spreadsheet. If you have read-access to the original database which exported the csv file, you may even drop the whole csv thing and use a database query to that database.
[Example] Loading CSV into preformatted spreadsheets
Calc interpretes your date strings correctly and applies the default format for dates according to your current locale which is English (USA).
With a German number format locale 26-Jul-2018 shows up as 26.07.18 which is the correct value 43307 regardless of formatting.
The one and only thing that matters is that Calc imports the correct values from plain text files.
If this is a problem, you can import database data (csv is a database exchange format) through the Base component into preformatted cell ranges in Calc (or better forget the whole spreadsheet. If you have read-access to the original database which exported the csv file, you may even drop the whole csv thing and use a database query to that database.
[Example] Loading CSV into preformatted spreadsheets
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 52
- Joined: Wed Aug 22, 2018 5:47 pm
Re: Copy and paste date from one spreadsheet to another
I am not using German number format locale . when manually i am opening it , it is 26-Jul-2018 .
my my macro code is not opening it in this format 26-Jul-2018 . This is the date the file already has.
I am not aware of importing database data.
This is my code that opens 2 URLs. 1) csv file in comma separated mode 2) xls file.
Dim FileProperties(1) As New com.sun.star.beans.PropertyValue
Dim Dummy() 'An (empty) array of PropertyValues
rem ###############################################
rem for filter options for opening a csv file:
rem ##############################################3#
FileProperties(0).Name = "FilterName"
FileProperties(0).Value ="Text - txt - csv (StarCalc)"
FileProperties(1).Name = "FilterOptions"
FileProperties(1).value = "44,34,0,1"
rem ####################################################
Url1 = "file:///C:\Users\Kk\Desktop\FILE-20180727-092810.csv"
Url2 = "file:///C:\Users\Kk\Desktop\DIVIDEND_PRICE_JULY_27.xls"
Doc1 = StarDesktop.loadComponentFromURL(Url1, "_blank",0, Dummy) rem *********dates are as in the file 26-Jul-2018. format cell->its number **********
rem Doc1 = StarDesktop.loadComponentFromURL(Url1, "_blank",0, FileProperties) rem *****dates are 07/26/18 . format cell->its date should be 26-Jul-2018*********
Doc2 = StarDesktop.loadComponentFromURL(Url2, "_blank",0, Dummy)
1)should i apply some filter option?
2)when not using filter option for comma separated , the dates are exactly as in the the file. SO does it mean by default it is opening in comma separated mode?
3)case 1 - when not applying comma separated filter
observations:- Date 26-Jul-2018 is formatted as number. This is the date in the file. Its all fine and it is copying exactly in another file.
case 1 - when applying comma separated filter
observations:- Date 26-Jul-2018 is formatted as date.Now it becomes 07/26/18 .This date is not in the file. Date value 43307
is copying in another file.
my my macro code is not opening it in this format 26-Jul-2018 . This is the date the file already has.
I am not aware of importing database data.
This is my code that opens 2 URLs. 1) csv file in comma separated mode 2) xls file.
Dim FileProperties(1) As New com.sun.star.beans.PropertyValue
Dim Dummy() 'An (empty) array of PropertyValues
rem ###############################################
rem for filter options for opening a csv file:
rem ##############################################3#
FileProperties(0).Name = "FilterName"
FileProperties(0).Value ="Text - txt - csv (StarCalc)"
FileProperties(1).Name = "FilterOptions"
FileProperties(1).value = "44,34,0,1"
rem ####################################################
Url1 = "file:///C:\Users\Kk\Desktop\FILE-20180727-092810.csv"
Url2 = "file:///C:\Users\Kk\Desktop\DIVIDEND_PRICE_JULY_27.xls"
Doc1 = StarDesktop.loadComponentFromURL(Url1, "_blank",0, Dummy) rem *********dates are as in the file 26-Jul-2018. format cell->its number **********
rem Doc1 = StarDesktop.loadComponentFromURL(Url1, "_blank",0, FileProperties) rem *****dates are 07/26/18 . format cell->its date should be 26-Jul-2018*********
Doc2 = StarDesktop.loadComponentFromURL(Url2, "_blank",0, Dummy)
1)should i apply some filter option?
2)when not using filter option for comma separated , the dates are exactly as in the the file. SO does it mean by default it is opening in comma separated mode?
3)case 1 - when not applying comma separated filter
observations:- Date 26-Jul-2018 is formatted as number. This is the date in the file. Its all fine and it is copying exactly in another file.
case 1 - when applying comma separated filter
observations:- Date 26-Jul-2018 is formatted as date.Now it becomes 07/26/18 .This date is not in the file. Date value 43307
is copying in another file.
Last edited by Kprogrammer on Sun Aug 26, 2018 4:24 pm, edited 2 times in total.
Open office 3.1 on Windows 7
Re: Copy and paste date from one spreadsheet to another
26-Jul-2018 is a text. It is certainly not a date value. If you don't need the date for evaluation/aggregation/filtering/sorting then it will be just fine.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Copy and paste date from one spreadsheet to another
In my template SheetMerger.ots you find a little tool which translates the GUI settings of an already opened csv file to a FilterOptions string.
Open my file then your csv and call setFilterOptions with your csv being the active window. You will find the filter options in the named cell FilterOptions
Open my file then your csv and call setFilterOptions with your csv being the active window. You will find the filter options in the named cell FilterOptions
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 52
- Joined: Wed Aug 22, 2018 5:47 pm
Re: Copy and paste date from one spreadsheet to another
FileProperties(0).Name = "FilterName"
FileProperties(0).Value ="Text - txt - csv (StarCalc)"
FileProperties(1).Name = "FilterOptions"
FileProperties(1).value = "44,34,0,1,2,3,4,5"
FileProperties(0).Name = "FilterName"
FileProperties(0).Value ="Text - txt - csv (StarCalc)"
FileProperties(1).Name = "FilterOptions"
FileProperties(1).value = "44,34,0,1,5,6,7,8"
these values are working fine. with 44,34,0,1 any four values ranging 2-10 is working fine.the date is exactly what the file contains.
Is there any problem to use this?
FileProperties(0).Value ="Text - txt - csv (StarCalc)"
FileProperties(1).Name = "FilterOptions"
FileProperties(1).value = "44,34,0,1,2,3,4,5"
FileProperties(0).Name = "FilterName"
FileProperties(0).Value ="Text - txt - csv (StarCalc)"
FileProperties(1).Name = "FilterOptions"
FileProperties(1).value = "44,34,0,1,5,6,7,8"
these values are working fine. with 44,34,0,1 any four values ranging 2-10 is working fine.the date is exactly what the file contains.
Is there any problem to use this?
Open office 3.1 on Windows 7
-
- Posts: 52
- Joined: Wed Aug 22, 2018 5:47 pm
Re: Copy and paste date from one spreadsheet to another
When I am sending my macro spreadsheet file saved as .ods or lost to another system. Macro code is not there in the file.
As I am sending my file containing macro it should be there when sending to another system.
Why macro is not there? It only shows standard folder and no macros.
As I am sending my file containing macro it should be there when sending to another system.
Why macro is not there? It only shows standard folder and no macros.
Open office 3.1 on Windows 7
Re: Copy and paste date from one spreadsheet to another
[Tutorial] How to install a code snippet
and a snippet for your global Calc library showing the current file's FilterOptions in an input box from where you can copy the string:
and a snippet for your global Calc library showing the current file's FilterOptions in an input box from where you can copy the string:
Code: Select all
Sub showFilterOptions
Dim args(),i%
args() = thisComponent.getArgs
for i = 0 to uBound(Args())
if args(i).Name = "FilterOptions" then inputbox "","",args(i).value
next
End Sub
Last edited by Villeroy on Sun Aug 26, 2018 6:41 pm, edited 4 times in total.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Copy and paste date from one spreadsheet to another
You can save/store your macros in two different places:When I am sending my macro spreadsheet file saved as .ods or lost to another system. Macro code is not there in the file.
As I am sending my file containing macro it should be there when sending to another system.
Why macro is not there? It only shows standard folder and no macros.
- In the documents. In this case the macros are available only for the document containing the macro. And you can send the document together the macros inside it.
- In the "MyMacros" under the Office Suite software. In this case the macros are available for all of documents that you open on that computer. But the macros will not be sent together with the documents.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
-
- Posts: 52
- Joined: Wed Aug 22, 2018 5:47 pm
Re: Copy and paste date from one spreadsheet to another
I understood if I place my macro in "MyMacros" after sharing files macro will not be inside it.
But where to keep it so that while sharing it will be there?
I can see MyMacro and OpenOffice macro folders only.
Please guide.
But where to keep it so that while sharing it will be there?
I can see MyMacro and OpenOffice macro folders only.
Please guide.
Open office 3.1 on Windows 7
Re: Copy and paste date from one spreadsheet to another
and macro folders of loaded documents.Kprogrammer wrote:I can see MyMacro and OpenOffice macro folders only.
Forget "OpenOffice Macros".
Macros that can be used with many documents without document specific preconditions, should be placed in "My Macros". You can distribute this code easily by means of extensions or by the import featue. All this is described in full detail in the link I gave you.
Macros that rely on names, styles, links or document specific layout, should be stored in a document or in a template.
Code: Select all
oNames = ThisCOmponent.NamedRanges
oName = oNames.getByName("MyRange")
oRange = oName.getReferredCells()
oRange.CellStyle = "My Cell Style"
The code is stored in a or template.
The template has a named range "MyRange" (no matter where) and a whole set of formatting attributes called "My Cell Style" and it is a matter of a few lines to apply the whole set of formatting attributes to the right cell range.
ThisComponent always refer to the document embedding the code.
All documents derived from that template share the same code, the same cell styles and the same range names.
If the above code is stored in MyMacros, you may call it when ThisComponent does not fulfill the precondition or when it is not even a Calc document.
The following is the kind of code I store in "My Macros":
Code: Select all
Function getOffsetRange(oRg, nRowOffset&, nColOffset&, nRowResize&, nColResize&)
Dim addr
'calls: getRangeByAddress
addr = oRg.getRangeAddress()
addr.StartRow = addr.StartRow + nRowOffset
addr.EndRow = addr.EndRow + nRowOffset
addr.StartColumn = addr.StartColumn + nColOffset
addr.EndColumn = addr.EndColumn + nColOffset
if nRowResize > 0 then addr.EndRow = addr.StartRow + nRowResize -1
if nColResize > 0 then addr.EndColumn = addr.StartColumn + nColResize -1
getOffsetRange = getRangeByAddress(oRg.getSpreadsheet(), addr)
End Function
'pass a spreadsheet-document, sheet or range together with a c.s.s.table.CellRangeAddress
'return empty if oAddr out of bounds or wrong obj
Function getRangeByAddress(obj, oAddr as com.sun.star.table.CellRangeAddress)
on error goto nullErr:
Dim oSheet
If obj.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
REM use the sheet specified by given address
oSheet = obj.getSheets.getByIndex(oAddr.Sheet)
else
REM use given object (range/sheet) as parent range
oSheet = obj
endif
getRangeByAddress = oSheet.getCellRangeByPosition(oAddr.StartColumn,oAddr.StartRow,oAddr.EndColumn,oAddr.EndRow)
exit function
nullErr:
getRangeByAddress = Null
End Function
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Copy and paste date from one spreadsheet to another
In the macro library dialog, when you see My Macros, OpenOffice Macros, you may need to scroll down to select the name of your spreadsheet. If you have other OpenOffice documents open they will also appear there.But where to keep it so that while sharing it will be there?
I can see MyMacro and OpenOffice macro folders only.
If you click on the + in front of the name of your spreadsheet and it disappears, select New from the buttons on the right, That will create a Standard folder for your document and open a New Module dialog. You can accept the default name or type in a different one if you like, then press OK
Now you can enter your macro code and it will be stored with the document.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11