How to create and edit timeline slicer using win32com python?

Question

I am trying to create a timeline slicer using win32com python. I am currently using win32com to manipulate excel data but in the data, my client wants me to set the upper limit and lower limit of the timeline slicer to certain month. I have googled a lot and i have came to a conclusion that the only way I could do it is by coding it in VBA and implement it in python like here. I have no experience in VBA and I was wondering if there is a way to use win32com python instead of VBA win32com python.

Edit:

After using "Assign Macro" in Excel, this is the code regarding my timeslicer:

ActiveWorkbook.SlicerCaches("NativeTimeline_Goods_Receipt_Date").TimelineState. _
        SetFilterDateRange "01/01/2020", "30/04/2020"

Now i need to change it into python and assign the start date & end date into variable. So far i have this:

from win32com.client import Dispatch
excel = win32.gencache.EnsureDispatch('Excel.Application')
test_wb = excel.Workbooks.Open(test_file)
date_sl = test_wb.SlicerCaches("NativeTimeline_Goods_Receipt_Date")

Could you add some code snippets around what you're trying to do? This is quite open and it would be easier to help if it were pinned down to some concrete code. — PirateNinjas, Jul 10 '20 at 09:10

ihaveaquestion · Accepted Answer · 2020-07-11T00:43:02.120

Apparently in Excel, there is a program within it that could record anything you click so if you want to manipulate the filter/slicer, you can right click the element, and then choose "Assign Macro". Then you can click away as it records your clicks. Once youre done, you can view it by again choosing "Assign Macro" and a pop-up window will be available and you can choose your_filter/slicer_name_Click and it will provid you the VBA code. All you have to do is change it so it fits python format.

Updated answer for converting the VBA into python code:

By referring to this link, i was able to convert the VBA into python code and adjust the date based on your choice of date.

So the VBA code is this:

ActiveWorkbook.SlicerCaches("NativeTimeline_Goods_Receipt_Date").TimelineState. _
        SetFilterDateRange "01/01/2020", "30/04/2020"

And the python version of it is:

excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.DisplayAlerts = False
excel.Visible = True
test_wb = excel.Workbooks.Open(test_file, None, False)
date_sl = test_wb.SlicerCaches("NativeTimeline_Goods_Receipt_Date")
date_sl.TimelineState.SetFilterDateRange("01/01/2020", "30/04/2020")

In my case, i need to change the date to set based on when i run the code and so on so i can just assign the date to a variable and substitute the hardcoded date with it.

How to create and edit timeline slicer using win32com python?

1 Answers1