Can Python be used as a macro language for excel, Word, etc.?

[CynthiaMoore]

I currently use Visual Basic for Applications (VBA) as the macro language for Excel and Word. It's a horrible language. Can Python be used instead?

[Larz60+]

Yes python can be used.
please post a small excel file with output requirements, for an example.

In the mean time, you can look through excel related packages here: https://pypi.org/search/?q=excel
also look at openpyxl: https://pypi.org/project/openpyxl/
and it's doc page: https://openpyxl.readthedocs.io/en/stable/

[CynthiaMoore]

Yes python can be used.
please post a small excel file with output requirements, for an example.

In the mean time, you can look through excel related packages here: https://pypi.org/search/?q=excel
also look at openpyxl: https://pypi.org/project/openpyxl/
and it's doc page: https://openpyxl.readthedocs.io/en/stable/

I have attached an Excel workbook that uses a VBA function to format a time interval in the smallest units that is <= 1. Sheet 2 has the actual worksheet code. It will not work because the VBA function is in an add-in. Sheet 1 has the literal values.

The VBA function syntax is:

=FmtTime(pTime, [pDP|1], [pInUnits|"Days"])

• pTime is the time interval to be formatted
  
• pDP is the number of decimal places to return
  
• pInUnits is the units of pTime
  

Thanks


  Format Time.xlsx (Size: 12.84 KB / Downloads: 113)

[Larz60+]

The attachment appears to be a php file, not excel.

[CynthiaMoore]

The attachment appears to be a php file, not excel.

How can that be. When I download it, it is a xlsx file.

Here it is in a OneDrive folder:

https://1drv.ms/x/s!ArBLbKVM2K_HiPYwByt_kvi2d3XmGw

[rob101]

Hi @CynthiaMoore

Maybe I'm not fully understanding what it is that you're trying to do?

But surely, given the data from Sheet1, would the output in column F, rows 5 through 13, formatted as 'Days to 2 decimal places' not be this?:

Output:
0.00
0.00
0.08
1.00
1.00
29.35
31.35
365.00
366.00

[CynthiaMoore]

Hi @CynthiaMoore

Maybe I'm not fully understanding what it is that you're trying to do?

But surely, given the data from Sheet1, would the output in column F, rows 5 through 13, formatted as 'Days to 2 decimal places' not be this?:

Output:
0.00
0.00
0.08
1.00
1.00
29.35
31.35
365.00
366.00

Formatted as days, yes. But I don't want it formatted as days. I want it formatted in the smallest units that will be >= 1. Intervals from 0 to 59.5 seconds will be formatted as seconds. Since the default units is days, that means multiplying the value by 24*60*60. Intervals from 1 to 59.5 minutes will be formatted as minutes by multiplying by 24*60. Intervals from 1 to 23.5 hours will be formatted as hours by multiplying by 24. Intervals from 1 to 29.5 days will be formatted as days. No adjustment needed. Intervals between 1 and 11.5 months will be formatted as months by dividing by 30. And intervals greater than 1 year will be formatted as years by dividing by 365.25.

I have added a new column (E) to the table showing the difference (End-Start). The units are days. I want code to convert the values in E to the values in F.



I already VBA that will do it. I am wondering if I can do it in Python. That is, can I write a fmttime function in Python that I can call like I do my VBA function?

Is that any clearer?

[rob101]

Is that any clearer?

Yes, very much so. Thank you.

I would like to be able to offer you a Python based solution, but given that I can't and that I think that you can do what you want to do, using a custom style sheet, I offer this:

You'd need to create a new style sheet, with a new style for each duration.

On the new style sheet, create a style called minutes for which the format code could be (for example):
MM:SS" (Mins:Sec)"

... and a style for hours for which the format code could be (again, for example):

HH:MM" (Hrs:Min)"

... and so on, for each time period.

The standard format code (for any given cell) would be SS" (Sec)" with conditional format, for which the rule is...

Condition 1: Cell value between 0.00069540 and 0.04166605 Apply style minutes
Condition 2: Cell value between 0.04167764 and 0.99999679 Apply style hours

... and so on, again, for each time period. If your cells will only ever hold any given time period, then you need only one condition for said cell, but if your cells can hold any given time period, then you'll need as many conditions as there are time periods.

This could read as more complicated than it is in fact; the real work involves (as with any programming project) designing the rules and applying them, but I'm sure that it can be done.

This is all VBA, behind the curtain, but you're using Excel as a 'front-end', if you will.

n.b: the values I have here are:
0.04166605 = 00:59:59
0.99999679 = 23:59:59

[stevendaprano]

You can also use Python as a scripting language for LibreOffice and OpenOffice.