[Solved] Copy certain rows from one sheet to another

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Nelomf
Posts: 22
Joined: Wed Nov 06, 2019 4:29 pm

[Solved] Copy certain rows from one sheet to another

Post by Nelomf »

Hello

I have a calc workbook with 2 sheets, first is "encomenda" and second is "saida"
In the first sheet i have a table with the range B3:F300


I want to paste this table to the second sheet, range A2:E299, but only the rows that have values in the E column.
This must be done in OpenOffice basic because this is part of a procedure that i've already created..

I'm not a programmer and i need help.

Thanks in advance

Manuel
Last edited by Nelomf on Wed Apr 14, 2021 2:42 pm, edited 2 times in total.
Openoffice 4, Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy certain rows from one sheet to another

Post by Villeroy »

If you have to do this in VBA then you need MS Excel.
If you are not a VBA programmer, then you have to hire one.
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
Nelomf
Posts: 22
Joined: Wed Nov 06, 2019 4:29 pm

Re: Copy certain rows from one sheet to another

Post by Nelomf »

Sorry i should wrote OppenOffice basic. And this for private use, if someone can help i will appreciate but if not... pacience.
Openoffice 4, Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy certain rows from one sheet to another

Post by Villeroy »

You do not need any macro for this.
Just filter the source range before you copy and paste.
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
Nelomf
Posts: 22
Joined: Wed Nov 06, 2019 4:29 pm

Re: Copy certain rows from one sheet to another

Post by Nelomf »

Yes i Know That.

But as i said before this code will be part of a greater macro.
It should copy and paste and then generate a PDF to send to a specific email all without operator handling.
Only the copy and paste is not made.
The table as 300 entries and only a few rows have all the columns with values.

Sorry for the english
Openoffice 4, Windows 7
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Copy certain rows from one sheet to another

Post by JeJe »

Have you tried the macro recorder - not sure about the second part, but it might do the copy part for you?
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Nelomf
Posts: 22
Joined: Wed Nov 06, 2019 4:29 pm

Re: Copy certain rows from one sheet to another

Post by Nelomf »

Yes i have tried and i have mange to copy the content. Thanks

Now another way of doing what i want is to filter the copied content by not empty results in one of the columns.
I've tried recording a macro for that but this is the result (it made the filter but did not record de args)

sub filtrar
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 ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DataFilterStandardFilter", "", 0, Array())

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

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

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Save", "", 0, Array())
Openoffice 4, Windows 7
mcmurchy1917
Posts: 23
Joined: Fri Feb 22, 2013 2:15 pm

Re: Copy certain rows from one sheet to another

Post by mcmurchy1917 »

I would use something like this. You will have to edit it to suit your own requirements. It's untested.

Code: Select all

Sub Copy_Cells

Dim iEndCol As Integer
Dim dblEndRow As Double
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
Dim CellAddress As New com.sun.star.table.CellAddress

oDoc = ThisComponent
oController= ThisComponent.getCurrentController()
oFirstSheet = oDoc.Sheets.getByIndex( 0 )

iEndCol = getLastUsedColumn(oFirstSheet)
dblEndRow = getLastUsedRow(oFirstSheet)

REM  Copy the row/columns from first sheet to a second sheet

oSecondSheet = oDoc.createInstance("com.sun.star.sheet.Spreadsheet")
oDoc.Sheets.insertByName("Working Sheet", oSecondSheet)

CellRangeAddress.Sheet = 0
CellRangeAddress.StartColumn = 0
CellRangeAddress.StartRow = 0
CellRangeAddress.EndColumn = iEndCol
CellRangeAddress.EndRow = dblEndRow

CellAddress.Sheet = 1
CellAddress.Column = 0
CellAddress.Row = 0

oFirstSheet.copyRange(CellAddress, CellRangeAddress)

End Sub
Slackware user
Nelomf
Posts: 22
Joined: Wed Nov 06, 2019 4:29 pm

Re: Copy certain rows from one sheet to another

Post by Nelomf »

Thanks a lot, i will test it.
Manuel
Openoffice 4, Windows 7
Nelomf
Posts: 22
Joined: Wed Nov 06, 2019 4:29 pm

Re: Copy certain rows from one sheet to another

Post by Nelomf »

I've finally had the chance to test this code but i got an error

Problem: Error in "iEndCol = getLastUsedColumn(oFirstSheet)" Basic execution erro
subprocedure or function not defined

mcmurchy1917 wrote:I would use something like this. You will have to edit it to suit your own requirements. It's untested.

Code: Select all

Sub Copy_Cells

Dim iEndCol As Integer
Dim dblEndRow As Double
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
Dim CellAddress As New com.sun.star.table.CellAddress

oDoc = ThisComponent
oController= ThisComponent.getCurrentController()
oFirstSheet = oDoc.Sheets.getByIndex( 0 )

iEndCol = getLastUsedColumn(oFirstSheet)
dblEndRow = getLastUsedRow(oFirstSheet)

REM  Copy the row/columns from first sheet to a second sheet

oSecondSheet = oDoc.createInstance("com.sun.star.sheet.Spreadsheet")
oDoc.Sheets.insertByName("Working Sheet", oSecondSheet)

CellRangeAddress.Sheet = 0
CellRangeAddress.StartColumn = 0
CellRangeAddress.StartRow = 0
CellRangeAddress.EndColumn = iEndCol
CellRangeAddress.EndRow = dblEndRow

CellAddress.Sheet = 1
CellAddress.Column = 0
CellAddress.Row = 0

oFirstSheet.copyRange(CellAddress, CellRangeAddress)

End Sub
Openoffice 4, Windows 7
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Copy certain rows from one sheet to another

Post by JohnSUN-Pensioner »

Try this

Code: Select all

Sub FilterRangeToCell(sDataAddress As String, sTargetAddress As String)
Dim oSheets As Variant
Dim oCellRangesByName As Variant
Dim oFilteredRange As Variant
Dim oTargetCellAddr As New com.sun.star.table.CellAddress
Dim oFilterDescriptor As Variant
Dim oFilterFields(0) As New com.sun.star.sheet.TableFilterField

	oSheets = ThisComponent.getSheets()
	oCellRangesByName = oSheets.getCellRangesByName(sDataAddress)
	oFilteredRange = oCellRangesByName(0)
	oCellRangesByName = oSheets.getCellRangesByName(sTargetAddress)
	oTargetCellAddr = oCellRangesByName(0).getCellByPosition(0, 0).getCellAddress()
	oCellRangesByName(0).Spreadsheet().getCellRangeByPosition(oTargetCellAddr.Column, oTargetCellAddr.Row, _
		oTargetCellAddr.Column + oFilteredRange.getColumns().getCount(), _
		oTargetCellAddr.Column + oFilteredRange.getRows().getCount()).ClearContents(-1)

	oFilterDescriptor = oFilteredRange.createFilterDescriptorByObject(oFilteredRange)
	oFilterDescriptor.OutputPosition = oTargetCellAddr
	oFilterDescriptor.CopyOutputData = True
	oFilterDescriptor.ContainsHeader = False	' If first row is header then set to TRUE
	
	oFilterFields(0).Operator = com.sun.star.sheet.FilterOperator.NOT_EMPTY
	oFilterFields(0).Field = 3	' Column E
	oFilterDescriptor.setFilterFields(oFilterFields)
	
	oFilteredRange.filter(oFilterDescriptor)
End Sub
Call it from your code like as

Code: Select all

Sub TestFilter
	FilterRangeToCell("encomenda.B3:F300","saida.A2")
End Sub
First param is address of source range and second - top-left cell of target range (BOTH AS STRING!)
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
Nelomf
Posts: 22
Joined: Wed Nov 06, 2019 4:29 pm

Re: Copy certain rows from one sheet to another

Post by Nelomf »

First of all, tks a lot.

I've tried and for what i can understand (that's not a lot :) ) this code wilkl act as a filter as i want.

I've placed a form button on my encomenda sheet and call the Sub TestFilter with that.
I've got the following error

Basic sintaxe error (i'm translating from portuguese)
Espected: =.
and highlight "encomenda.B3:F300"

Thanks
Openoffice 4, Windows 7
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Copy certain rows from one sheet to another

Post by JohnSUN-Pensioner »

Hmm... I also added a button on the sheet and assigned the same macro to it. Please check if the filtering works in this spreadsheet.
Attachments
CopyFilteredRange.ods
Example ods
(25.99 KiB) Downloaded 430 times
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
Nelomf
Posts: 22
Joined: Wed Nov 06, 2019 4:29 pm

Re: Copy certain rows from one sheet to another

Post by Nelomf »

Hello

It works perfectly.
I'm going to see why it doesn't work on my flle and get back to you
Tks
Openoffice 4, Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy certain rows from one sheet to another

Post by Villeroy »

Nelomf wrote:Hello

It works perfectly.
I'm going to see why it doesn't work on my flle and get back to you
Tks
Disable VBA on top of the module:

Code: Select all

Option VBASupport 0
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
Nelomf
Posts: 22
Joined: Wed Nov 06, 2019 4:29 pm

Re: Copy certain rows from one sheet to another

Post by Nelomf »

Ok.
I placed only your code separated in module 1, all the other code is in another sheet.
It is great.

Many thanks

Manuel
Openoffice 4, Windows 7
Post Reply