New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Macro VBA code to play an audio file automatically when the excel file is opened

Macros, VBA, Excel Automation, etc.

Macro VBA code to play an audio file automatically when the excel file is opened

Postby vbharath0708 » Fri Mar 16, 2012 2:45 pm

Hi All,

I am having an excel workbook with three sheets. In sheet 3 I have embedded an audio file which has a message. I have implemented a VBA code to make this the active worksheet every time I open Excel with this code:

Private Sub Workbook_Open()
Worksheets(1).Activate
End Sub

I have inserted this code on the ThisWorkbook section in VBA. I am now looking for a VBA code to make the audio file on this sheet to automatically play when the excel file is opened. I want the audio file to play only once and only when the excel file is opened. it has a welcome message and instructions.

I need help with the code. Please help!!
  • 0

vbharath0708
Rookie
 
Posts: 10
Joined: Mar 8, 2012
Reputation: 0
Excel Version: 2007

Re: Macro VBA code to play an audio file automatically when the excel file is opened

Postby Sisyphus » Fri Mar 16, 2012 11:55 pm

Hi,
Add a code module to your VBA Project (by default 'Module1') and place the following code in it:
Code: Select all
Option Explicit

Private Declare Function PlaySound Lib "winmm.dll" _
  Alias "PlaySoundA" (ByVal lpszName As String, _
  ByVal hModule As Long, ByVal dwFlags As Long) As Long

Public Sub WelcomeMessage()
    Const SoundPath As String = "C:\My Music\"
    Const SoundFile As String = "TheMessage.wav"          ' must be a WAV file
    Dim Sound As String

    Sound = SoundPath & SoundFile
    PlaySound(Sound, 0&, &H1 Or &H20000)
End Sub

You must assign real values to the two constants 'SoundPath' and 'SoundFile', and the SoundFile must be of WAV type.
Change your Workbook_Open event procedure to include one extra line:
Code: Select all
Private Sub Workbook_Open()
    WelcomeMessage
    Worksheets(1).Activate
End Sub

The message will be played whenever you open the workbook. Loading the workbook will continue while the message is played and the user may be able to start work before the message has finished. If you wish to delay the opening until after the message has finished replace the last line of the 'WelcomeMessage' procedure with this one:
Code: Select all
PlaySound(Sound, 0&, &H0 Or &H20000)
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Macro VBA code to play an audio file automatically when the excel file is opened

Postby vbharath0708 » Mon Mar 19, 2012 8:44 am

Hi Sisyphus

Thanks for your reply...The code which you sent links an audio clip which is not embedded as an object in your excel sheet and it calls the file through specific path mentioned in the code. I wanted the audio file to be embedded in the sheet itself. I figured out the code myself. I now have this code on the workbook:

Private Sub Workbook_Open()
Worksheets(1).Activate
Sheet2.Shapes("Object 1").Select
Selection.Verb Verb:=xlPrimary
End Sub

I have this code on the worksheet:

Declare Function sndPlaySound Lib "winmm.dll" Alias "sndPlaySoundA" _
(ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

Public Const SND_SYNC = &H0
Public Const SND_ASYNC = &H1
Public Const SND_NODEFAULT = &H2
Public Const SND_MEMORY = &H4
Public Const SND_LOOP = &H8
Public Const SND_NOSTOP = &H10

Dim SoundName As String
Dim wFlags As Double
Dim X


Sub WAVPlay(File)
Dim SoundName As String
SoundName = File
wFlags = SND_ASYNC Or SND_NODEFAULT
X = sndPlaySound(SoundName, wFlags)
End Sub

The audio file is now playing whenever I open the worksheet, but the playback is interrupted and stops when I scroll on the sheet. Please let me know what change should I make such that the playback sustains and is not interrupted or aborted when I scroll on the worksheet.

Regards,

Bharat.
  • 0

vbharath0708
Rookie
 
Posts: 10
Joined: Mar 8, 2012
Reputation: 0
Excel Version: 2007

Re: Macro VBA code to play an audio file automatically when the excel file is opened

Postby Sisyphus » Mon Mar 19, 2012 10:57 pm

Hello Bharat,
From what I read the sndPlaySound function is obsolete and replaced by the PlaySound function for which I gave you correct syntax to do what you want. It seem that all you need to do is to construct the variable Sound in my code with the sound source you have constructed in yours. You may find this link helpful:
http://vmd.myxomop.com/apires/ref/p/playsound.html
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Macro VBA code to play an audio file automatically when the excel file is opened

Postby vbharath0708 » Tue Mar 20, 2012 8:28 am

Hi,

Thanks a lot...I will look into it.

Regards,

Bharat.
  • 0

vbharath0708
Rookie
 
Posts: 10
Joined: Mar 8, 2012
Reputation: 0
Excel Version: 2007


Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google [Bot] and 26 guests