Macro copying from data sheet to result sheet

Creating a macro - Writing a Script - Using the API

Macro copying from data sheet to result sheet

Postby haraldthomsen » Sun May 20, 2018 10:28 pm

Hi
I have an Excel macro, but cannot get it to work in OpenOffice.
From the data sheet I need to copy rows with items from one or more suppliers.
Each supplier have a specific number. The number of items differ from each supplier.

I have seen Villeroy's typefilter, but I sure how to modify it to my needs.

Kind regards,
Harald

Sub TopSupplierFilter()

Sheets("Data").Range("A2").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Result").Range("D4:D10"), _
CopyToRange:=Sheets("Result").Range("A20:N20") _
, Unique:=False
End Sub
haraldthomsen
 
Posts: 2
Joined: Mon Jan 30, 2017 10:18 pm

Re: Macro copying from data sheet to result sheet

Postby FJCC » Mon May 21, 2018 3:44 am

Without seeing how your data are laid out and without knowing anything about Excel, I had to make some guesses about what your macro does. Something like this might work for you.
Code: Select all   Expand viewCollapse view
oDataSheet = ThisComponent.Sheets.getByName("Data")
oResultSheet = ThisComponent.Sheets.getByName("Result")

'Find what cells are used in the region starting at Data.A2
oCellA2 = oDataSheet.getCellrangeByName("A2")
oCurs = oDataSheet.createCursorByRange(oCellA2)
oCurs.collapseToCurrentRegion()

oDataRng = oDataSheet.getCellrangeByName(oCurs.AbsoluteName)
oCritRng = oResultSheet.getCellrangeByName("D4:D10")
oOutCell = oResultSheet.getCellrangeByName("A20")
OutCellAddr = oOutCell.CellAddress

Desc = oCritRng.createFilterDescriptorByObject(oDataRng)
Desc.CopyOutputData = True
Desc.OutputPosition = OutCellAddr

oDataRng.filter(Desc)
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6714
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro copying from data sheet to result sheet

Postby haraldthomsen » Mon May 21, 2018 9:28 am

Hi FJCC
I will try and be more specific.

Datasheet
Sup Item
1 1a
1 1b
1 1c
2 2a
2 2b
3 3a
3 3b
3 3c
4 4a
4 4b

Resultsheet
If I write 1 in D4 and 4 in D5 and leave cells D6 to D10 blank
the result should be (Starting in cell A20)
1 1a
1 1b
1 1c
4 4a
4 4b

Thank you very much for your help.

Kind regards,
Harald
OpenOffice 4.1
Win7
haraldthomsen
 
Posts: 2
Joined: Mon Jan 30, 2017 10:18 pm

Re: Macro copying from data sheet to result sheet

Postby Villeroy » Mon May 21, 2018 10:46 am

haraldthomsen wrote:I have seen Villeroy's typefilter, but I sure how to modify it to my needs.

Nobody but me understands what you've seen without providing a hyperlink. It is this one with an attached document and a detailed description https://forum.openoffice.org/en/forum/v ... 4531#p4531 In fact, this was my first attachment on this forum site.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25709
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro copying from data sheet to result sheet

Postby Villeroy » Mon May 21, 2018 1:36 pm

That code can be used in many different ways. All it does is that it refreshes a named database range (menu:Data>Define...) by the filter criteria in a named cell range (menu:Insert>Names>Define...).
A text box provides a "text changed" event which is triggered with every key stroke. In the example document, the database range is filled from a true database. As a side effect, the same code pulls up-to-date data from the database into the sheet with every refresh. But it works as well with database ranges that are not linked to a true database.

The text goes to a linked cell, the linked cell is referenced by a formula in the criteria range and the database range is set up with an "advanced filter" which reads filter criteria from a cell range. See menu:>Data>Filter>Advanced... where you can set up the details, such as an output range where to copy the filtered results and a "no duplicates" option. Excel has the same feature.
With every keystroke into the text box the macro does the same as you would do calling menu:Data>Refresh with the cell cursor in the database range.
The linked cell (D2 or "linked_cell" in my document) does not need to be named.

Copy the code to your own document.
Modify the 2 names of the database range and the named criteria range in the Basic editor. The rest of the code remains untouched.
Apply an advanced filter and adjust the advanced filter settings to your needs, e.g. output to other sheet and pattern matching by "regular expressions".
Add text boxes with linked cells and concatenate the entered criteria in your named criteria range. The regex equivalent of a * wildcard is .* (dot and asterisk)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25709
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests