[Solved] Macro to enable auto refresh of advanced filter

Creating a macro - Writing a Script - Using the API

[Solved] Macro to enable auto refresh of advanced filter

Postby Msasso » Sun Dec 09, 2018 7:27 pm

Hi all,

First time posting here so apologies if i haven't followed any rules/etiquette!

Essentially what i am trying to do is hide rows based on a given cells value (todays date), i am able to do this manually by selecting the range (A25:F61) then advanced filter (A21:F22). However what i would like is that the date changes automatically using today() then the filter reapply's itself so that only today's information is displayed. I attempted to record a macro of this but it wouldn't work, i have no grasp of macros, i have even been looking at other examples online and attempting to modify them for my sheet but to no avail, would someone be kind enough to write one for me?

Kind Regards
Marco
Attachments
Untitled 2.ods
(12.33 KiB) Downloaded 27 times
Last edited by Hagar Delest on Mon Dec 10, 2018 9:06 am, edited 1 time in total.
Reason: tagged solved
Open Office 4.1.5 - Windows 10
Msasso
 
Posts: 6
Joined: Sun Dec 09, 2018 7:15 pm

Re: Macro to enable auto refresh of advanced filter

Postby Villeroy » Sun Dec 09, 2018 8:01 pm

ThisComponent.DatabaseRanges.getByName("your_db_range_name").refresh() sorry, will not read new values form cells.

Look at this: download/file.php?id=136
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27583
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to enable auto refresh of advanced filter

Postby Villeroy » Sun Dec 09, 2018 8:32 pm

Code: Select all   Expand viewCollapse view
ThisComponent.DatabaseRanges.getByName("your_db_range_name").refresh()

should work since this action also recalculates volatile functions such as TODAY or NOW.

menu:Data>Refresh does the trick without macro.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27583
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to enable auto refresh of advanced filter

Postby Msasso » Sun Dec 09, 2018 9:01 pm

Hi Villeroy,

I am looking at the first post you sent, would prefer it to be entirely automated as i want to make it as fool proof as possible (End users will vary in levels of competency) I have followed the instructions step by step but this message appears upon entering anything in the text box;

Image

I'm not sure what it means or what i have done wrong.

Thank you for your help so far
Attachments
Untitled 2.ods
(13.48 KiB) Downloaded 33 times
Open Office 4.1.5 - Windows 10
Msasso
 
Posts: 6
Joined: Sun Dec 09, 2018 7:15 pm

Re: Macro to enable auto refresh of advanced filter

Postby Villeroy » Sun Dec 09, 2018 9:07 pm

There is no database range named "Import1". What is the name of your database range?
menu:Data>Define...
Define a database range, delete all macro bullshit and call menu:Data>Refresh once a day to update your date filter.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27583
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to enable auto refresh of advanced filter

Postby Msasso » Sun Dec 09, 2018 9:46 pm

Hey,

Defined the range, labelled it as data, now the following message is appearing

Image

I appreciate what you are saying about updating once daily manually, but that is not what i want, it has to be automated, please help
Open Office 4.1.5 - Windows 10
Msasso
 
Posts: 6
Joined: Sun Dec 09, 2018 7:15 pm

Re: Macro to enable auto refresh of advanced filter

Postby Villeroy » Sun Dec 09, 2018 9:53 pm

There is no named range named "criteria".

Delete all macro bullshit and call menu:Data>Refresh once a day to update your date filter.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27583
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to enable auto refresh of advanced filter

Postby Msasso » Sun Dec 09, 2018 10:04 pm

There is a range named criteria,

Image

Your proposed second solution doesn't meet my criteria, why should i have to settle if what i would like is possible. I really, really appreciate your help as i cannot do this on my own but please meet the conditions!
Open Office 4.1.5 - Windows 10
Msasso
 
Posts: 6
Joined: Sun Dec 09, 2018 7:15 pm

Re: Macro to enable auto refresh of advanced filter

Postby Villeroy » Sun Dec 09, 2018 10:40 pm

Do not mimic things you do not understand. If you want to filter by today's date, put =TODAY() in the criteria cell (no regular expression required), call Data>Filter>Advanced, select the criteria range. When the next day has come, click any cell in the database range and call Data>Refresh
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27583
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to enable auto refresh of advanced filter

Postby Msasso » Sun Dec 09, 2018 11:08 pm

Thank you for the manual solution, i tried it and it does work well.

I realize what i was doing wrong, and named the criteria range, now this is the error;

Image

Please don't give up on me!
Open Office 4.1.5 - Windows 10
Msasso
 
Posts: 6
Joined: Sun Dec 09, 2018 7:15 pm

Re: Macro to enable auto refresh of advanced filter

Postby Villeroy » Sun Dec 09, 2018 11:10 pm

P.S. If your dates happen to be strings (don't know why Excel users do that), the formula for the criterion would be =TEXT(TODAY();"DD/MM/YY"))

I realize what i was doing wrong, and named the criteria range, now this is the error;

You have zero programming skills. Stop using macros.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27583
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to enable auto refresh of advanced filter

Postby Msasso » Sun Dec 09, 2018 11:20 pm

You are correct, i have zero programming skills.
I do however need this to be automatic not manual via refreshing the data so what else should i do?
I am here politely asking for someone to walk me through this, if that is not you then that's fine, thank you for your time.
Open Office 4.1.5 - Windows 10
Msasso
 
Posts: 6
Joined: Sun Dec 09, 2018 7:15 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests