If you could send me both the excel file and the wav file I will try to set it up for you
Hi John D
I have a zipped folder set to send you, containing one .xlsm file and 5 small .wav files. But this interface will only let me attach (using the paperclip above) graphics files.
I can't see an email address for you.
If the file has sensitive information let me know before you upload it
Took a dozen attempts, but here is the ID:
It should unzip into 'ESCI audio test 17 Jun.xlsm' and 'trumpet1.wav', etc (5 small .wav files)
The Excel file runs in Excel 2007 (and 2010) for Windows (macros enabled of course). Click the 'Another experiment' button and you should see a sample generated, various action in the charts, and a little figure just below the buttons. And a sound--one second of trumpet when the simulation gives p<.001 and a bright red leaping figure. Other sounds for the other 4 possible (less happy) figures.
For that to work, the 5 .wav files must be in the same folder as the .xlsm file.
Unclick the checkbox labelled 'Sound' (near cell N23) and all should run, without sound.
In Excel 2011 on Macintosh it all runs (after much tweaking!) until I turn on Sound. In which case the need to use winmm.dll in the Private Function 'PlaySound' causes 'Run-time error 53'. Because, I guess, the .dll is a Windows thing.
What I'm after is a replacement Function, or whatever, so the macro triggered by the 'Another experiment' button, with sound turned on, will play the relevant .wav file on Mac.
(The five small buttons at the bottom of the lower left chart also play the sounds, and trigger the error on Mac. The 'Sound' checkbox does not turn off sound for those buttons. You can safely ignore those buttons.)
BTW, this is all for the Mac version of my ESCI software to go with my book:
PS Not sensitive--I'm giving the software as a free download under GPU GPL.
I got the files but unfortunately due to the complexity of the workbook a lot time will be needed to understand and work on it. That kind of time I do not have at the moment, So I think I will opt out so the question becomes open to all experts.
If however you just want me to set up these wav files on sample spreadsheet so you can play them using buttons let me know and I will be happy to do that
Here's my complete question posed much more simply: You can ignore the files I sent. (Unless you wish to use the 'trumpet1.wav' and 'trom1.wav' sound files I sent, for experimenting.)
In Windows Excel 2007 or 2010, the following allows the user to play one or the other .wav file, according to whether macro 'trumpet()' or 'trombone()' is run.
Note that the two .wav files (i.e. 'trumpet1.wav' and 'trom1.wav') must be in the same folder as the Excel Workbook.
Here's the code:
Dim j, i As Integer
'Private Declare Function PlaySound Lib "winmm.dll" _ Alias "PlaySoundA" (ByVal lpszName As String, _ ByVal hModule As Long, ByVal dwFlags As Long) As Long Const SND_SYNC = &H0 Const SND_ASYNC = &H1 Const SND_FILENAME = &H20000
'Sub trumpet() 'Press the trumpet button and hear a brief blast of trumpet Call PlaySound(ThisWorkbook.Path & "\" & "trumpet1.wav", 0&, SND_ASYNC Or SND_FILENAME)End Sub
'Sub trombone() 'Press the trombone button and hear a brief blast of trombone Call PlaySound(ThisWorkbook.Path & "\" & "trom1.wav", 0&, SND_ASYNC Or SND_FILENAME)End Sub
MY QUESTION: I want to do the same in Excel 2011 on Macintosh. Try to run the above on Mac, and get an error because winmm.dll is not recognised and not available. What macro code will play a wav. sound on Mac??
Hi John D,
I understand. That's reasonable. I considered slimming it all down even further, maybe I should have.
But if you can make for me a worksheet with 5 buttons, each triggering a macro that plays one of the 5 .wav sounds, in Excel 2011 on Mac, then that should solve my problem. Please go ahead. Thank you.
I'd assume that I could then use those 5 macros as subroutines called by other macros, so I could play the sounds in the various situations I need for the full complex worksheet?
I'd assume also that your worksheet and the 5 .wav files would work, provided all 6 files were held together in a single folder? I.e. pathname to the .wav files is not to an absolute place on a particular hard disk, but is to the same place (whatever that is) that the Excel workboook is stored?
Ok Geoff here you go
I made it a stand alone file with the sounds waves embedded in the file so you do not have to have separate sound files.
Each button plays the sound object associated with it. Also the macros are named after the wav files for ease of identification
Hope this is ok. Let me know if you have any questions, and thank you for clicking Accept if you are satisfied with the solution
Our Saturday here and I've got to go out.
Eager to test it tonight.
Hope you had a chance to test it. Let me know if there are any issues, otherwise thank you for clicking Accept if you are satisfied with the work
Thank you. It's a great idea to have the sound files included with the Excel workbook. (Where are they? In what form--what do I need to know to transfer them to my worksheet?)
I saved the file on my Mac (an almost new MacBook Pro). Opened it in Excel 2011. Enabled macros. Saw the nice line-up of 5 buttons. Clicked one button, obtained dialogue box:
Microsoft Visual Basic
Run-time error '1004':
Cannot start the source application for this object. There may not be enough memory available. [Actually I have 8GB RAM, no other applications open, except the Finder.]
I clicked 'Debug' and saw this line in the macro code highlighted yellow:
My Excel 2011 is up to date.
I rebooted the computer, tried again. Same result.
The computer is not connected to the internet.
iTunes 10 is loaded (tho' I haven't loaded any music yet). It was not open when I tried your file and it did not automatically open.
I played around with your file (on a different computer), in Windows Excel 2003, and heard a single sound for the first button I clicked, then saw that iTunes had opened and 'clarry1' had been added to that iTunes music collection. But by then the buttons had become rectangles that did not function as buttons. Somewhat similar in Excel 2007, tho' I didn't hear a sound.
When I first clicked (in Excel 2003) a dialogue box warned 'You are about to activate an OLE object...' If I said yes, I heard the sound, first time only. If I said no, I got VB runtime error 1004 (Verb method of OLEObject class failed)--same error as on Mac.
I mention that Windows stuff, not because I'm asking for something that works in Windows, but in case it's more evidence that helps diagnosis.
Is there something I'm missing in what I'm doing in Excel 2011 in Mac?
The Y/N question about OLE in Excel 2003, and the similarity of the errors, suggests I may need to turn on some permission to use OLE, in Excel 2011?? Is that a relevant thought? If so, how do I do that? (I've never come across that issue, in my only 12 years labouring with Excel...)
John, I greatly appreciate your efforts.
Geoff, I am sorry I don't know what's going on at your end but I tried it on different windows systems and it worked all the time. It should work on the mac as well.
Unless you found the macros and the set up that I sent you helpful I will opt out. Thanks
I have accepted; thanks for the good work. I'm extremely keen to solve this problem, and can't understand why it works for you, but not me.
I would happily pay again to get it solved, so I can play the 5 sounds and have the .wav files embedded in the workbook. (At least for Mac, but for every version of Excel would be great.)
I have experimented further, and have tried your file on three different computers, and in Excel 2003, 2007, and 2011 (Mac). On an XP machine, using either Excel 2003 or 2007, it hangs (at the second line of the macro I think, the select object)--that machine is not connected to the internet and does not have iTunes.
The closest I've come is on a machine running Windows 7, with iTunes loaded. I open your .xls file in Excel 2007, save it as .xlsm, close. Open again, then clicking the buttons opens iTunes, adds the sound to iTunes, plays the sound, and finishes with the object off to the right (Column AB) selected. (I add a line of code to select a cell back left, so I can see the buttons again, after every click.)
Click further buttons and the same thing happens, tho' sometimes a sequence of a couple of sounds is played. iTunes is opened every time, and all sounds added to it.
If you are prepared to put a bit more time in (please!), my first suggested check is that I've received what you tried to send me. I have saved your .xls file as .xlsm, and uploaded it to Wikisend:
File ID: 394404
Does that work back with you?
Any suggestions as to what I can try? (I would need it to work whether or not a computer had iTunes loaded--that surely should be irrelevant?)
Hoping to hear from you, and thanks again,
Thanks for accepting
Ok let me look into it further. I will also try to give you an alternative method if the current one refuses to work on your systems.
Will keep you posted later in the day.
Thanks John. I've been reading a bit about OLE objects, but haven't had any bright ideas. (You have some add-in that I don't? I need to activate something, or set some permission, so your embedded .wav objects can do their stuff?) Strange that I can't get it to work on so many different platforms.
The file I sent back to you worked fine with you?
Ok Geoff, here are there variations of the clarry1 sound file . Could you try playing them on the Mac and PC platforms and let me which one(s) play correctly on these platforms
Thanks. But, alas, no luck.
I tried each button, on each platform, with each version of Excel mentioned below. I did exit and reload between trial of each single button. (If iTunes was opened, I closed it before the next trial.) Macros enabled in every case.
Windows 7 machine, with iTunes installed:
Excel 2003: for each button, get dialogue box warning about activating an OLE object. I click 'Yes', then iTunes is opened. Played sound. (Occasionally twice.)
Excel 2007: for each button, iTunes is opened, then sound is played. (Same behaviour, whether .xls or .xlsm file) (No initial warning box.)
XP machine, with no iTunes:
Excel 2003 with .xls. Excel 2007 with either .xls or .xlsm. Any button.
...same result in every case: machine hangs, Excel is reported as 'not responding' and I need to use Task Manager to close Excel.
Mac, with Excel 2011 (iTunes installed, but never opened)
clarry1: get runtime error 1004: cannot start source application for this object, stops at the 'Selection.Verb' line
clarry1 (alt): get runtime error 445: object doesn't support this action, stops at 'Sheet1.Shapes' line
clarry1(mp3): 1004 as above for clarry1
...the same for .xls and .xlsm
Why does Microsoft make it so hard?!
hmm.. At least one if not all three should work on any windows computer
I suppose you do have Windows Media Player installed on your windows computer. Could you look for the windows player exe file (i.e. wmplayer.exe) and send me the folder path where this file is located on your system
My Windows 7 machine has wmplayer.exe in what I imagine is the standard place:
C:\Program Files\Windows Media Player
I changed my defaults so that all audio is played, by default, by WMP. (Before the default was iTunes.) Now each of the 3 buttons plays the clarry sound, and also opens WMP, and leaves the WMP small window open in front of Excel.
My XP machine (no iTunes) has wmplayer in the same place. It works fine. No sign of the clarry1 sound having been added to it. I changed my default so all audio is played by WMP (previously: WavePad). No change to button behaviour: Excel hangs in all cases.
PS I guess my priorities are
1. Get something that works on Mac
2. (Very nice but not essential) A solution that integrates the soundfile with the workbook, and works across platforms.
Ok one more try for the windows now that we know where your wmp is located
place a copy of the "trumpet1.wav" file in say the root directory, (i.e. C:\trumpet1.wav)
then copy the following code and place it in your sound file, and run it
Sub Playit()zSnd = "C:\Program Files\Windows Media Player\wmplayer.exe C:\trumpet1.wav"MyAppID = Shell(zSnd, 0)End Sub
Works on both Windows machines, in both Excel 2003 and 2007.
Great! Geoff if you want me to re-set up the 4 buttons to be used with this method let me know
As for playing them on the Mac unfortunately all the routines that I am aware of rely on the Windows API functions, which don't appear to work on the Mac. I have researched this quite a bit today but couldn't find any readily available solutions. Will continue to research it hopefully we can come up with something.
John, I've accepted, in recognition of your time and expertise, and what I've learned. And your comment that you'll continue to try to find a Mac solution -- my original problem!
Can I confirm: Your most recent Windows solution works nicely, but it needs to know the path to WMP. And the .wav file must be in a known hard location. (Maybe it could be a location relative to the .xlsm file, but it would still be a separate file.) Do I have that correct? So my original solution using winmm.dll still looks better for Windows?
For Mac, it seems weird that a machine that prides itself on media and interactivity doesn't have an easy way to play a sound? Even from Excel? I would have thought that was a pretty general problem?
Anyway, thanks so far, and I'm very keen for some Mac inspiration to strike.
And I'm happy to pay again for a Mac solution.
Thanks so much for the accept Geoff. I was not expecting another accept nor did I think it would be fair for you to pay again since your core issue has not been resolved. I certainly do not think you should pay yet again in the unlikely event that a solution can be found. I will however continue to look into it
You are right about Mac being the leader in the media applications. I think the issue has to do with the fact that Microsoft programming language is developed around windows platform, so in cases like this 3rd party solutions (rather than custom programing) are normally sought.
OK, thanks John.
Standing by (hoping!) for some good Mac news!
Just a quick further comment.
Even so... getting a mac solution is, to me, definitely worth another payment.
By analogy with your WMP solution, is there some way of using some corresponding audio utility that all Macs have?
Nope! I can't believe that such a supposedly trivial task is proving to be so uncommon that there aren't many online articles or discussions covering it. I even looked for shareware, add-ins and commercial programs but found nothing that covers all three aspects, i.e. Mac, Excel and Wav. What I found was basically some bits and pieces of related information that might be helpful but cannot say that any is 100% relevant. Here are some links (probably you have already come across some of them)
Apple article and code for playing wav files in Office applications, but the covered application is Entourage
Sample code using Applescript and VBA
Discussion and solution for playing text-to-speech in Excel on the Mac
Apple tutorial for integrating media files in the custom applications (this is meant for developers and experienced programmers but it might give you some ideas)
Geoff, I know that this is not the answer you're looking for but this is the best that I could do. I am planning to check with a couple of experienced friends tomorrow to see if they know any solutions. Will get back to you in case I have some meaningful to report.
Thanks again and good luck. I hope I will have the opportunity to assist you in a better way on another excel question
Thanks very much for the effort John. Still have fingers crossed you (or your colleagues) might have some flash of inspiration...!
Meanwhile I've had an intensely frustrating day on a different Excel and Mac problem. If I can define it sufficiently I'll ask it as another question.
Yes please go ahead and post your new question when you're ready and I will be more than happy to try to assist you with them
I got a reply from my colleague and I'm afraid his opinion is more or less the same as what I had already mentioned, i.e. "...not sure that would be possible with VBA, even if the excel version supports it, since you'd normally have to use windows API calls and you can't do that on a mac. you'd probably need AppleScript rather than VBA"
If I get any useful information I will sure let you know
Ah well, the Mac version will just have to be silent.
I detest having to develop different versions. When I chose Excel as the development platform 12 years ago, it was after lots of investigations of options. Now have lots of sunk costs, and must stick with it. But other options look increasingly attractive these days, with much better cross-platform reputation. For statistics, 'R' is probably the choice.
Anyway, thanks again.
Excel is still a pretty good cross platform application despite some routines which are basically related to the operating system, but it may not stay that way for long. Microsoft appears to be phasing out VBA from Excel and replacing it with Applescript. They've already done so in version 2008 but then due to customer complaints they deiced to give it me more time and re-introduced VBA in their subsequent version