Me.OLEObjects("Object 1").Verb Verb:=xlPrimary
No can do? This used to be dependant upon Sound Recorder. I do not know if it still is. In other words, irregardless of the registration properties for "wav", the Dound Doc. Object would play thru Sound Recorder. I have no clue how to use this as a resource in a win Dll. You might copy the object to the clipboard which creates a shell scrap and then create a memory stream. I have no idea how to create a memory stream in VBA... Sorry.
For me, OLE .verb brings up the registered server application, which in my case is winamp (XL 2002 SP2). However, even if it was sound recorder, it's not the behavior I prefer.
I tried playing around with copying it to the clipboard, coversions between dataobject and object, but unfortunately I can't get it into the resource handle that the PlaySound lib is looking for. I'm too much of a novice to figure it out.
I did read up on memorystreams, but I couldn't find any examples of where it was used in VBA (only VB).
Does anyone know of any controls that embed a sound similar to how a picture is embeded with the picture form control?
it's really a shame that microsoft didn't include a resource editor into vba... it would really come in handy for a lot of things.
You may have already found the answer to this, but here's one that I found.
First, insert your sound into a worksheet that will not be used (except, perhaps, to store other sounds) - I also copied the sound file into the same folder as the workbook as per insructions on the other forum. Make sure you write down the object number (if you don't know, by opening up vba after the next step, you can see its name there).
Next, record a new macro, then play the file, then stop the recorder.
The next step is one where I'm not sure it's optional as I just followed the instructions I found on another forum. Hide the worksheet with the sound.
Now, open vba and copy the lines of the macro code into the vba code where you want the sound to play.
Next, alter your code so that it looks like this (and keep it to one line only by joining the two lines and deleting/adding vba code accordingly):
"Sounds" is the name of the hidden sheet containing the wave sound.
"Object 2" is the name of the object pasted into the hidden sheet when you inserted the sound.
Now, run your code and it should work. I don't know enough about vba to answer the question as to why the sound doesn't work when the recorded macro is pasted into vba, but I eventually found that the above line worked.
The OLEObjects/Verb method evokes the registered windows application, so that method unfortunately won't work for my purposes. I am trying to get the sound to play in background but still be contained in the excel file. thanks for the detailed instructions though. I am certain your instructions will help others who searching for the OLE solutions.
I ended up keeping the sound file separate. I was thinking that I could contain the wav within the excel as text and output it to temp wav, but that's not all the different from just keeping the file separate to begin with. It still seems like there must be a way to put a string into memory and associate a file handle with it, but I'm not sure how to do that. I've only played with a couple memory APIs and nothing with how they relate to file handles.
thanks again everyone, I very much appreciate all the consideration.
Same here. I have only worked with streams within the net framework and not directly by way of API functions. You can create a TextStream using the MS Scripting Runtime library's TextStream class, but I'm sure you would need a binary stream with any audio file. If you simply need portability and wish to store a very small wav file within your workbook, you could convert it to binary, save it in worksheet cells, in workbook names, or in several other targets. The application could check for the file on the target system or create the file if it does not exist and then use the PlaySound API. That might be overkill but it is an idea. Do your users have internet access? Maybe you should provide a single remote location and simply download the file to the target machine when needed.