Play embedded wav file when workbook opens

[BlondieC]

SUBJECT: Play embedded wav file when workbook opens but without showing the media player

Running Excel 2013.

I've done quite a bit of searching on this one today and from most of the info I read it doesn't look like this can be done. However, most of the info was years old so I'm hoping over the years something changed and I will be able to have this work successfully.

I don't include sound in my Excel reports but this is a special case. We have a product launch of sorts coming up and for the first report I send out on these products I wanted to have a wav play when the end user opens the workbook.

I have the wav embedded and some code and it works with one exception... the media player pops up asking with an "Open Package Contents" window asking "Do you want to open this file?, Open or Cancel".

I would really prefer if for the end user it just played without being prompted by the Media Player.

My code is placed in "ThisWorkbook".


Private Sub Workbook_Open()Application.DisplayAlerts = False
ActiveSheet.Shapes("Object 1").Select
Selection.Verb Verb:=xlPrimary
Application.DisplayAlerts = True
End Sub

[Leith Ross]

Hello BlondieC,

Working with embedded files is restrictive on purpose. These file types were used early on to thwart security to run malware.

If you have access to the original wav file, I have an alternative "embedding" method that is safe and does not require launching the player.

If you are interested, let me know.

[BlondieC]

Hi Leith, I do have the wav file on its own and would love to hear about your method. Thank you!

[Leith Ross]

Hello BlondieC,

The macros below will let you choose the sound file (wav) to save to the new worksheet "Byte Data", restore the file to the User's Temp directory, and play it when the workbook opens.

There are 2 modules: a Standard VBA module that contains the embedding macros and the Workbook module Open event code module. After you have copied and saved the macros, run the macro "ChooseSoundFile". This will save the file automatically to the workbook on the worksheet "Byte Data". You can hide this sheet if you like.

Module1 Code


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

Sub PlaySoundFile(ByVal SoundFile As String)

Dim Ret As Long
Const SND_SYNC As Long = &H0 'play synchronously (default)

Ret = PlaySound(SoundFile, 0&, SND_SYNC)

End Sub

' Written: July 17, 2012
' Updated: April 27, 2016
' Author: Leith Ross
' Summary: Copies a file to a worksheet in binary format as bytes.
' The hex data is n rows by 32 columns wide.
' Any file format can be stored this way and later restored.
' The restoresd file is saved to the User's Temp directory.
'
' NOTE: Maximum file size is 32kb (32768) bytes.

Sub ChooseSoundFile()

Dim Filename As Variant

Filename = Application.GetOpenFilename("Sound Files *.wav, *.wav, All Files *.*,*.*")
If Filename = False Then Exit Sub

SaveToSheet Filename

End Sub

Private Sub SaveToSheet(ByVal Filename As String)

Dim c As Long
Dim DataByte As Byte
Dim Data() As Variant
Dim i As Long
Dim n As Integer
Dim r As Long
Dim Wks As Worksheet
Dim x As String


If Dir(Filename) = "" Then
MsgBox "The File '" & Filename & "' Not Found."
Exit Sub
End If

On Error Resume Next
Set Wks = Worksheets("Byte Data")
If Err = 9 Then
Worksheets.Add After:=Worksheets(Worksheets.Count)
Set Wks = ActiveSheet
Wks.Name = "Byte Data"
End If
On Error GoTo 0

Wks.Cells.ClearContents
Wks.Cells(1, "AH").Value = Dir(Filename)

n = FreeFile

Application.ScreenUpdating = False
Application.ErrorCheckingOptions.NumberAsText = False

With Wks.Columns("A:AF")
.NumberFormat = "@"
.Cells.HorizontalAlignment = xlCenter

Open Filename For Binary Access Read As #n
If LOF(n) > (Rows.Count * 32) Then
MsgBox "File size is greater than " & (Rows.Count * 32) / 1024 & " KB", vbCritical
Close #n
Exit Sub
End If

ReDim Data((LOF(n) - 1) \ 32, 31)

For i = 0 To LOF(n) - 1
Get #n, , DataByte
c = i Mod 32
r = i \ 32
Data(r, c) = DataByte
Next i
Close #n

Wks.Range("A1:AF1").Resize(r + 1, 32).Value = Data
.Columns("A:AF").AutoFit
End With

Application.ScreenUpdating = True

End Sub

Function RestoreFile() As String

Dim c As Long
Dim Bytes As Variant
Dim Data() As Byte
Dim File As String
Dim Item As Variant
Dim j As Long
Dim LSB As Variant
Dim MSB As Variant
Dim n As Integer
Dim r As Long
Dim Rng As Range
Dim Wks As Worksheet

On Error Resume Next
Set Wks = Worksheets("Byte Data")
If Err <> 0 Then
MsgBox "The Worksheet 'Byte Data' is Missing.", vbCritical
Exit Function
End If
On Error GoTo 0

Set Rng = Wks.Range("A1").CurrentRegion

File = Wks.Cells(1, "AH").Value

If File <> "" Then
n = FreeFile
File = Environ("TEMP") & "\" & File

Open File For Binary Access Write As #n
ReDim Data((Rng.Cells.Count) - 1)

Bytes = Rng.Value

For r = 1 To UBound(Bytes, 1)
For c = 1 To UBound(Bytes, 2)
Data(j) = Bytes(r, c)
j = j + 1
Next c
Next r

Put #n, , Data
Close #n
End If

RestoreFile = File

End Function


Workbook Module Open Event Code


Private Sub Workbook_Open()

PlaySoundFile RestoreFile

End Sub

[Kenneth Hobs]

I have seen it done like Leith showed you.

The way that I would do it is how it was done in my reference link. Be sure to use Paul's method to save the OLEasAwav file. I usally save those to a temp folder as well. http://www.vbaexpress.com/forum/showthread.php?40772-Solved-Playing-Embedded-Wav-File

If you need a working example, please attach a a simple file or I can make one for you. Click the Go Advanced button in lower right of a reply and then click the paperclip icon on the toolbar to Browse and Upload.

I like to put API routines into a Module which makes use of the routines such as your Open event more clean looking. It can then easily be used in other projects because it is then, well, modular...

[BlondieC]

Hi Leith,

Thank you for helping. I tried the code and unfortunately the wav file starts to play before the workbook is opened. The green Excel box appears as it normally does to load a workbook and the wav file starts and the green box sort of freezes until the wav file is finished and then the workbook opens. Is there a way to set a delay on the wav file so the workbook can open first?

[BlondieC]

Hi Kenneth,

Thanks to you also for taking the time to help me out. I haven't been able to get this method working yet but it's because I most likely haven't pasted the code in the right place yet.

This line fails and brings up the debugger. I have 6 worksheets and my Sheet2(RevenueByDay) has a pivot table and pivot chart on it so would it be something to do with that the Sheet2 reference in the code?

SaveWAVOLEAs Sheet2.OLEObjects("YesMaster"), "x:\t\yesmaster.wav"

[Kenneth Hobs]

Probably. Use your sheet's code name or sheet name. The sheet name method would be:

SaveWAVOLEAs Worksheets("YourSheetNameHere").OLEObjects("YesMaster"), "x:\t\yesmaster.wav"
"YesMaster" may need to be changed to your OLEObject's name or number. What you name the file does not matter but I would use the OLEObject's base name. Of course OLEObject names are easily changed as we do normal named ranges. Select the object (may need Select on in the Developer ribbon) and type the name in the name box near top of row labels column.

I have attached my example.

[BlondieC]

Hi Kenneth, I'm sending the file home and will work on it from there - fingers crossed!

[BlondieC]

Hi Kenneth, I was able to get this working - thank you! I noticed a few things... If I double click on the Excel file icon to open the workbook or right click on the same file icon to open the workbook or load it into Sharepoint and double click on the file, the wav file starts to play before the workbook opens and doesn't allow the workbook to open until the wav file is finished. If I go into Excel and then open the file from within the application the workbook opens and then the wav file plays which is perfect. I will be loading the file onto Sharepoint and sending a distribution list with a link to the file. Is there any way of putting a delay on the wav not starting until the workbook is open? Thanks!


Probably. Use your sheet's code name or sheet name. The sheet name method would be:

SaveWAVOLEAs Worksheets("YourSheetNameHere").OLEObjects("YesMaster"), "x:\t\yesmaster.wav"
"YesMaster" may need to be changed to your OLEObject's name or number. What you name the file does not matter but I would use the OLEObject's base name. Of course OLEObject names are easily changed as we do normal named ranges. Select the object (may need Select on in the Developer ribbon) and type the name in the name box near top of row labels column.

I have attached my example.

[Kenneth Hobs]

You might want to change to async in mMain.

Sub PlaySoundFile()
Dim Ret As Long, fn As String
fn = Environ("temp") & "\YesMaster.wav"
SaveWAVOLEAs Sheet2.OLEObjects("YesMaster"), fn
'Ret = PlaySound(fn, 0&, SND_SYNC Or SND_FILENAME)
Ret = PlaySound(fn, 0&, SND_ASYNC Or SND_FILENAME)
End Sub

and/or

I am not sure that this will help since it is not an issue for me and I don't have SharePoint.

Comment out the Workbook_Open's PlaySoundFile code line in the ThisWorkbook object.

In one of the Module's (I put it at end of mMain) or a new one, add this:

Private Sub Auto_Open()
PlaySoundFile
End Sub

[BlondieC]

Hi Kenneth, your suggestions nailed it!!! The workbook opens and then the wav file begins to play and I tested opening it a number of different way. Thank you very much!


You might want to change to async in mMain.

Sub PlaySoundFile()
Dim Ret As Long, fn As String
fn = Environ("temp") & "\YesMaster.wav"
SaveWAVOLEAs Sheet2.OLEObjects("YesMaster"), fn
'Ret = PlaySound(fn, 0&, SND_SYNC Or SND_FILENAME)
Ret = PlaySound(fn, 0&, SND_ASYNC Or SND_FILENAME)
End Sub

and/or

I am not sure that this will help since it is not an issue for me and I don't have SharePoint.

Comment out the Workbook_Open's PlaySoundFile code line in the ThisWorkbook object.

In one of the Module's (I put it at end of mMain) or a new one, add this:

Private Sub Auto_Open()
PlaySoundFile
End Sub

[ahmed365]

Hi Ladies and gentlemen's

Could you please help me adding/restoring wav file to be played after workbook is opened ?

The same problem you faced , while changing the file directory

Can you please attach an example as i not expert like you

Best Regards