Solved

Launch .wav file from Excel using VBA

Posted on 1998-08-30
8
565 Views
Last Modified: 2012-05-04
I am writing a VBA program for Excel so that it makes a constant sound (for an alarm) when a cell reaches a certain value.  I can do all of the VBA code except for knowing how to launch the .wav file (e.g., chimes.wav).  I've gotten a far as being able to open "MPlayer" from my C drive using the SHELL command; however, I don't know how to get my MPlayer to play a .wav file.  Any ideas?
0
Comment
Question by:wilsonj
  • 4
  • 2
  • 2
8 Comments
 
LVL 2

Expert Comment

by:mkmccreary
Comment Utility
I've looked at a couple of different things in the last weekend for doing this from Word.  The solutions I found should work the same.  It all depends on what you have installed.  If you have the Office Developer Edition or VB 5 then you have available the Microsoft Multimedia Control (MCI32.OCX).  There are a couple of ways to get at this.  I am going to post the code from a previous answer.

This deals with a control on a form.

Here you go.  If in Access 97 open the form you wish to play the files from in design mode.  You then need to use the Insert menu and select ActiveX Controls.  Select the 'Microsoft MCI Control'.  You then need to add the below code from where you want the playing to occur:


    ' Initialize the control
    MMControl1.Visible = False
    MMControl1.Enabled = False
    MMControl1.Notify = False
    MMControl1.Wait = True
    MMControl1.Shareable = False
     
    ' Play a wave file
    MMControl1.DeviceType = "WaveAudio"
    MMControl1.Filename = "D:\WINNT\Media\chimes.WAV"
    MMControl1.Command = "Open"
    MMControl1.Wait = True
    MMControl1.Command = "Play"
    MMControl1.Command = "Close"
     
    ' Play an AVI file
    MMControl1.DeviceType = "AVIVideo"
    MMControl1.Filename = "D:\WINNT\clock.avi"
    MMControl1.Command = "Open"
    MMControl1.Wait = True
    MMControl1.Command = "Play"
    MMControl1.Command = "Close"
 
You may want to hide the control in the form load event, or you may want to allow the user to control the play, it is up to you.  


Another way to this is to instantiate the control through code.  I also tried this and it works.

Try this code and see if it works.  It is Dim-ing a control of type MMControl which I use to play the files.  I placed this code under a command button to test it.  You will also have to change the filenames to valid files.  Try this:

Dim MMControl1 As New MMControl

    ' Initialize the control
    MMControl1.Notify = False
    MMControl1.Wait = True
    MMControl1.Shareable = False
     
    ' Play a wave file
    MMControl1.DeviceType = "WaveAudio"
    MMControl1.FileName = "c:\WINNT\Media\chimes.WAV"
    MMControl1.Command = "Open"
    MMControl1.Wait = True
    MMControl1.Command = "Play"
    MMControl1.Command = "Close"
     
    ' Play an AVI file
    MMControl1.DeviceType = "AVIVideo"
    MMControl1.FileName = "c:\WINNT\clock.avi"
    MMControl1.Command = "Open"
    MMControl1.Wait = True
    MMControl1.Command = "Play"
    MMControl1.Command = "Close"

Set MMControl1 = Nothing



The last way is to do it through API calls.  There is an article at Microsoft.  The other person I gave this answer to said it was great.  


I figured out that the multimedia control does not ship with Office97, it ships with certain versions of VB as well as the Office Developers Addition.  I have found an article on how to do it with API calls.  The article is on Microsofts homepage at http://www.microsoft.com/accessdev/articles/movies.htm.  It covers both .AVI and .WAV files.  I didn't have time to read the whole article.


Any of these may help to solve your problem depending on your situation.  If you have anything more specific I still may be able to help.

Later,
Martin



0
 
LVL 5

Expert Comment

by:kulikuli
Comment Utility
Martin's suggestion is ok. However I prefer to use the API calls.
place the following line in your general declarations:
Declare Function sndPlaySoundA Lib "winmm.dll" (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

call the function with some wave file by using a line like this:
Call sndPlaySoundA("c:\music\wave\sound.wav", 0)

Easy solution, isn't it?

0
 
LVL 2

Expert Comment

by:mkmccreary
Comment Utility
I agree, much cleaner.  kulikuli deserves the points.

Later
0
 
LVL 5

Expert Comment

by:kulikuli
Comment Utility
Thanks. Please grade me then.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:wilsonj
Comment Utility
You'll have to forgive my ignorance, but I write macros for Excel about once a year.  As a result, I need to know exactly what code (word for word) to write for concepts that are new to me.  I tried writing the declare statement verbatim.  Then I wrote the call statement verbatim.   I've never used the Declare statement before so my lack of understanding may be leading to my error which says "Only Comments May Appear After End Sub, End Function, or End Property."

Anyway, in playing with Excel, I've found another solution.  I've inserted my sound as an object into an Excel sheet.  Then, I simply recorded the macro that plays my sound.  I think that this will work.  Now I just need to figure out how to keep repeating the sound until an action is taken on the spreadsheet; I just started playing with it, but it doesn't look like you can play with the spreadsheet while the macro is in a loop waiting for a cell value or something to be manually changed in the spreadsheet.

Unless you have any other suggestions, we can assume that my question is closed.  I appreciate your help.  Also, if it is easy for you to explain (the idiot-proof version) how to get your solution to work, I'd still love to know.
0
 
LVL 5

Accepted Solution

by:
kulikuli earned 100 total points
Comment Utility
ok, here we go:
create  a new standard module (module tab from access database).
The module will appear in the general declarations.
You will probably see something like:"option compare database" at the top.

now put the first line(the declare statement) right under the last blue statement.
In the above sample the line has been wrapped into two lines. Make sure you that in your module this wrapped line will be one line.

I don't know where you want to have the sound activated but you can put it anywhere (multiple times).
Just insert the second line (the call statement) where you need it. e.g. behind a button.
That's all. If you still have questions about this let me know. Please don't reject my answer. You can leave comments without grading me untill you got it working.
0
 

Author Comment

by:wilsonj
Comment Utility
Thanks for the help, but my client has now decided that he doesn't need the alarm.  By the way, I did look at your latest response.  You seem to be referring to Access while I am working with Excel.  I don't know if that makes any difference, but I still don't see how your latest response will work for me.

Regardless, it's now a mute point, and I realize that it's more likely my lack of experience with VBA that's keeping me from being able to successfully integrate your response.

I do like this site though and will definitely come back if I have questions in the future.  Thanks again!
0
 
LVL 5

Expert Comment

by:kulikuli
Comment Utility
It does not make any difference to Excel. Sorry for referring to Access. This is because most of the time I spend in Experts-Exchange is on the MSAccess topic.
For the Excel terminoligy: change the term Module from my answer into Macro and it will work. Thank you for grading me.

Best regards,

kulikuli
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now