Macro to Copy Range of data to new spreadsheet

Creating a macro - Writing a Script - Using the API

Macro to Copy Range of data to new spreadsheet

Postby Kenneth08 » Mon Sep 25, 2017 11:24 am

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
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
User avatar
Kenneth08
 
Posts: 12
Joined: Fri Aug 25, 2017 4:02 am

Re: Libreoffice Macro Copy Range of data to new spreadsheet

Postby JohnSUN-Pensioner » Mon Sep 25, 2017 12:26 pm

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.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Libreoffice Macro Copy Range of data to new spreadsheet

Postby Kenneth08 » Mon Sep 25, 2017 12:30 pm

JohnSUN-Pensioner wrote:Target cell (start of range) is A1? Or A3?

I revisedr

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
User avatar
Kenneth08
 
Posts: 12
Joined: Fri Aug 25, 2017 4:02 am

Re: Libreoffice Macro Copy Range of data to new spreadsheet

Postby Zizi64 » Mon Sep 25, 2017 12:33 pm

Use the API functions for crating a new empty Calc document (or for opening an existing one)
https://forum.openoffice.org/en/forum/v ... entfromURL

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8213
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macro to Copy Range of data to new spreadsheet

Postby JohnSUN-Pensioner » Mon Sep 25, 2017 1:39 pm

It can be something like as
Code: Select all   Expand viewCollapse view
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.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 1 guest