Hello Friends,
Newbie here in Basic programming.
Can you help me for the code on copying range to new spreadsheet without closing the original file.
The copied file should close and the original file will active.
Hoping you can help.
Some details:
Spreadsheet1 Name = Macro101
Sheet Name = Macro
Range = A3:I100
Spreadsheet2 Name = Savefile_08252017 (08252017 is date today)
Range = A3:I100
Macro to Copy Range of data to new spreadsheet
Macro to Copy Range of data to new spreadsheet
Last edited by Kenneth08 on Mon Sep 25, 2017 12:30 pm, edited 1 time in total.
Ubuntu 15.04
LibreOffice 4.4.2.2
Evolution 3.12.11
LibreOffice 4.4.2.2
Evolution 3.12.11
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Libreoffice Macro Copy Range of data to new spreadsheet
Target cell (start of range) is A1? Or A3?
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: Libreoffice Macro Copy Range of data to new spreadsheet
I revisedrJohnSUN-Pensioner wrote:Target cell (start of range) is A1? Or A3?
Some details:
Spreadsheet1 Name = Macro101
Sheet Name = Macro
Range = A3:I100
Spreadsheet2 Name = Savefile_08252017 (08252017 is date today)
Range = A3:I100
Ubuntu 15.04
LibreOffice 4.4.2.2
Evolution 3.12.11
LibreOffice 4.4.2.2
Evolution 3.12.11
Re: Libreoffice Macro Copy Range of data to new spreadsheet
Use the API functions for crating a new empty Calc document (or for opening an existing one)
viewtopic.php?f=20&t=44142&hilit=loadcomponentfromURL
What you want to copy: texts, numbers, formulas, direct formatting properties, cell styles???
There are many ways to achieve this task by a macro code...
Finally you can save (store) the new document, and you can close it by API functions:
StoreToURL() or StoreAsURL(), oDoc.Close() or oDoc.Dispose()
Please, please, please READ the descriptions that we suggested for you in the past: Andrew Pitonyak's macro books, and the API descriptions...
viewtopic.php?f=20&t=44142&hilit=loadcomponentfromURL
What you want to copy: texts, numbers, formulas, direct formatting properties, cell styles???
There are many ways to achieve this task by a macro code...
Finally you can save (store) the new document, and you can close it by API functions:
StoreToURL() or StoreAsURL(), oDoc.Close() or oDoc.Dispose()
Please, please, please READ the descriptions that we suggested for you in the past: Andrew Pitonyak's macro books, and the API descriptions...
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.
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Macro to Copy Range of data to new spreadsheet
It can be something like as
Code: Select all
Sub StoreRange
Const SHEET_TO_COPY = "Macro"
Const PREFIX_FILENAME = "Savefile_"
Dim cDoc As Variant ' Current document
Dim oDoc As Variant ' Output document
Dim oSheets As Variant ' All sheets of output document
Dim oSheet As Variant ' Single sheet of output document
Dim i As Long
GlobalScope.BasicLibraries.LoadLibrary("Tools")
cDoc = ThisComponent
cDoc.store ' Save document before linking!
REM Output file
oDoc = CreateNewDocument("scalc")
oSheets = oDoc.getSheets()
For i = oSheets.getCount()-1 To 1 Step -1
oSheet = oSheets.getByIndex(i)
oSheets.removeByName(oSheet.getName())
Next i
oSheet = oSheets.getByIndex(0)
oSheet.link(cDoc.getURL(), SHEET_TO_COPY, "calc8", "", com.sun.star.sheet.SheetLinkMode.VALUE)
oSheet.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE)
Rem Delete unnecessary data (if they exist)
Rem If you want data "as is" then just remove this lines
Rem Row of A3 - 2, previous row 1 (last param of getCellRangeByPosition)
oSheet.getCellRangeByPosition(0, 0, oSheet.getColumns().getCount-1, 1).clearContents(1023)
Rem From last row (99) to end of sheet
oSheet.getCellRangeByPosition(0, 100, oSheet.getColumns().getCount-1, oSheet.getRows().getCount-1).clearContents(1023)
Rem and right part of sheet
oSheet.getCellRangeByPosition(9, 0, oSheet.getColumns().getCount-1, oSheet.getRows().getCount-1).clearContents(1023)
Rem Save result
oDoc.StoreAsURL(DirectoryNameoutofPath(cDoc.getURL(), "/") + "/" + PREFIX_FILENAME + _
Format(Now,"DDMMYYYY") + ".ods", Array())
oDoc.close(True)
End Sub
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English