[Solved] Copy certain rows from one sheet to another

Creating a macro - Writing a Script - Using the API

[Solved] Copy certain rows from one sheet to another

Postby Nelomf » Wed Mar 24, 2021 2:28 pm

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

Re: Copy certain rows from one sheet to another

Postby Villeroy » Wed Mar 24, 2021 2:37 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 30107
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy certain rows from one sheet to another

Postby Nelomf » Wed Mar 24, 2021 3:12 pm

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

Re: Copy certain rows from one sheet to another

Postby Villeroy » Wed Mar 24, 2021 3:40 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 30107
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy certain rows from one sheet to another

Postby Nelomf » Wed Mar 24, 2021 5:02 pm

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

Re: Copy certain rows from one sheet to another

Postby JeJe » Wed Mar 24, 2021 9:18 pm

Have you tried the macro recorder - not sure about the second part, but it might do the copy part for you?
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1599
Joined: Wed Mar 09, 2016 2:40 pm

Re: Copy certain rows from one sheet to another

Postby Nelomf » Thu Mar 25, 2021 1:51 pm

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

Re: Copy certain rows from one sheet to another

Postby mcmurchy1917 » Thu Mar 25, 2021 4:19 pm

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

Code: Select all   Expand viewCollapse view
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
mcmurchy1917
 
Posts: 23
Joined: Fri Feb 22, 2013 2:15 pm

Re: Copy certain rows from one sheet to another

Postby Nelomf » Fri Mar 26, 2021 1:16 pm

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

Postby Nelomf » Wed Apr 14, 2021 9:47 am

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   Expand viewCollapse view
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
Nelomf
 
Posts: 22
Joined: Wed Nov 06, 2019 4:29 pm

Re: Copy certain rows from one sheet to another

Postby JohnSUN-Pensioner » Wed Apr 14, 2021 11:58 am

Try this
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 860
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Copy certain rows from one sheet to another

Postby Nelomf » Wed Apr 14, 2021 12:20 pm

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

Re: Copy certain rows from one sheet to another

Postby JohnSUN-Pensioner » Wed Apr 14, 2021 12:35 pm

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 198 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
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 860
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Copy certain rows from one sheet to another

Postby Nelomf » Wed Apr 14, 2021 1:03 pm

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

Re: Copy certain rows from one sheet to another

Postby Villeroy » Wed Apr 14, 2021 1:26 pm

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   Expand viewCollapse view
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
User avatar
Villeroy
Volunteer
 
Posts: 30107
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy certain rows from one sheet to another

Postby Nelomf » Wed Apr 14, 2021 2:39 pm

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


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 1 guest