3

I'm using XLWINGS to add some functionality to Excel. I want to be able to use a specific audio player with specific codecs to play audio files when the hyperlink in the excel cell is clicked (not when the hyperlink formula is written to its cell).

The xlwings UDF is:

@xw.func(volatile=False)
def play_audio(audiofilepath):
    '''
    Use the audioplayer defined at the top of the module as a
    path to an executable to 
    open the audiofile. The audioplayer auto-closes when the 
    reproduction of the audio file is complete as the timeout 
    parameter is equal to the calculated duration of the file
    '''
    #Get file length in seconds
    with contextlib.closing(wave.open(audiofilepath,'r')) as f:
        frames = f.getnframes()
        rate = f.getframerate()
        length = frames / float(rate)
        '''
        Use the defined audioplayer to play the file and 
        close out the app after the files duration
        '''
        try:
            subprocess.run([audioplayer,audiofilepath], timeout = length)
        except:
            pass

The code XLWINGS uses to write the hyperlink to excel is:

write_cell.value = '=HYPERLINK(play_audio("{}"),"OK")'.format(fullpathandfilename)

I've also tried:

write_cell.add_hyperlink(address =  '=play_audio("{}")'.format(fullpathandfilename),text_to_display ="OK",screen_tip=fullpathandfilename)

The excel hyperlink written looks like this:

=HYPERLINK(play_audio("path\to\audio.wav"),"PLAY")

This works but I have an unwanted behaviour:

  1. Each time xlwings writes this formula to a cell, the play_audio() code referred to by the HYPERLINK formula is executed (meaning the audio is played and bottle necks the writing process). I want excel to evaluate the formula when the user clicks the hyperlink but it seems to be triggered by other events like saving, closing or other events in the workbook.

I reviewed this particular post and reviewed this page of reference but I want to avoid having to code the solution in VBA if at all possible and keep all code coherent in the same py file.

Any ideas?

Thanks!

Community
  • 1
  • 1
user3535074
  • 1,268
  • 8
  • 26
  • 48
  • `play_audio("path\to\audio.wav")` would be being evaluated every time that cell was calculated for any reason - not just when the user clicks the hyperlink. – YowE3K Jun 15 '17 at 22:25
  • Thanks - is there any way to force it to only evaluated when the link is clicked? – user3535074 Jun 15 '17 at 22:51
  • Not too familiar with how hyperlinks actually work, but you could **try** making the link address your `play_audio("path\to\audio.wav")`, rather than saying that the link address is **obtained from** `play_audio("path\to\audio.wav")`. i.e. try `=HYPERLINK("play_audio(""path\to\audio.wav"")","PLAY")` - but I stress that I have no idea whether it will work. – YowE3K Jun 15 '17 at 22:59
  • I tried your suggestion but it's not calling the xlwings udf as expected. There are suggestions in the second link I put in my original post to use ISEMPTY to check that the calculation is complete before starting a new calculation though I'm not having much luck trying to add it as a condition within the play_audio UDF. – user3535074 Jun 16 '17 at 07:09
  • 1
    @user3535074, Can you update your question with a function, where xlwing writes hyperlink to cell? **Offtopic:** Why .wav? Are you a time-traveler? Just curious, no offence! – CommonSense Jun 20 '17 at 07:25
  • Hey @CommonSense, I edited the question to include where XLWINGS writes to the excel worksheet. Hope you can come up with a solution! As for the .wav, no offence taken. The users of the code have to deal with audio formats compatible with client software. And thats wav in this case. – user3535074 Jun 20 '17 at 11:57
  • @user3535074, Did you tried to set calculation to `manual` while you writing? Looks like `xlwing` [can handle it](http://docs.xlwings.org/en/stable/api.html#xlwings.App.calculation). – CommonSense Jun 20 '17 at 14:24
  • Yeah I tried.I even tried adding a condition to the UDF to only trigger given when calculation == 'automatic'. This didn't work but did shed light on the fact that xlwings writes the cell (and it is evaluated) but then excel calls the function too. – user3535074 Jun 20 '17 at 14:41
  • @user3535074, wait a sec, can you confirm that I understands your words right? Even if we set calculation to `manual` just before writing a hyperlink and add a condition to UDF to do stuff only when `calculation == 'automatic'` - UDF is still executed right after writing? Doesn't it looks like that Excel swithing back the calculation to `automatic`? Strange behaviour, isn't it? Also, if you add your own custom flag to UDF condition (very crude option) is it still executed? – CommonSense Jun 21 '17 at 07:53
  • Confirmed. The play_audio function is always called on writing and after writing. By using the calculation = 'manual' and an if statement in the play_audio function, I can avoid that the file is reproduced entirely but the function is still called. I need either an alternative approach or to be able to distinguish between a user click and calculation trigger event from within excel. – user3535074 Jun 21 '17 at 12:24

0 Answers0