Error in code - play music file automatically after opening file

[mukeshbaviskar]

Dear experts,
Please correct my code to play the music file automatically after opening the file.

Thanking you in anticipation.

Mukesh

[xLJer]

I worked this out in Excel 2003, going under the assumption that
you do not want to deal with an open media player after you close
Excel. This may not work, as is, for your Excel version, but give
my attached file a try to see if it has the effect you are looking for.
Then, if there are incompatibilities between Excel versions, someone
else on the forum may have an idea how to fix the problem.
This code goes in a new module:

Sub Auto_Open()
'VBA written for Excel 2003
'plays MP3 file one time when workbook is opened, from hidden Windows Media Player control in Sheet1.
'replaces Windows Media Player control with a new one, otherwise media file may start playing where
'it was previously interrupted by closing the workbook during MP3 file playing.

Dim path_fileID As String
Dim wks As Worksheet
Dim i As Integer
    
    path_fileID = "c:\Sample1.mp3"
    
    If Dir(path_fileID) = "" Then Exit Sub     'MP3 file not available in specified path
    
    Set wks = Sheets("Sheet1")

    'delete previous Windows Media Player control, if found, from Sheet1
    For i = 1 To Sheets(wks.Name).OLEObjects.Count
        If TypeName(Sheets(wks.Name).OLEObjects(i).Object) = "WindowsMediaPlayer" Then
            On Error Resume Next
            Sheets(wks.Name).OLEObjects(i).Delete
            On Error GoTo 0
        End If
    Next
 
    'add new Windows Media Player control
    wks.OLEObjects.Add(ClassType:="WMPlayer.OCX.7", Link:=False, _
        DisplayAsIcon:=False, Left:=721.5, Top:=3.75, Width:=87.75, Height:= _
        82.5).Select

    'make the player control invisible and begin playing the file
    For i = 1 To Sheets(wks.Name).OLEObjects.Count
        If TypeName(Sheets(wks.Name).OLEObjects(i).Object) = "WindowsMediaPlayer" Then
            Sheets(wks.Name).OLEObjects(i).Visible = False
            Sheets(wks.Name).OLEObjects(i).Object.URL = path_fileID
            Exit For
        End If
    Next
    
    Set wks = Nothing
    
End Sub

Sub Stop_Player()
'to add this functionality to stop the file playing before it ends, assign hot keys to run this sub
Dim wks As Worksheet
Dim i As Integer

    Set wks = Sheets("Sheet1")
    
    For i = 1 To Sheets(wks.Name).OLEObjects.Count
        If TypeName(Sheets(wks.Name).OLEObjects(i).Object) = "WindowsMediaPlayer" Then
            On Error Resume Next
            Sheets(wks.Name).OLEObjects(i).Delete
            On Error GoTo 0
            Exit For
        End If
    Next
    
    Set wks = Nothing

End Sub

[mukeshbaviskar]

Hello xLJer,
Excellent! It's working fine as per my requirement. I want to play the background instrumental music while working in excel. If you have any instrumental background music in mp3 format do send it to me on my personal email id as follows:

mukeshbaviskar.nsk@gmail.com

Now my problem is solved.

Thank you for your guidance.

Mukesh

[mukeshbaviskar]

Hello xLJer,
The song is of 10 minutes and then it didn't play again. If I want to repeat the song continuously till the file is open then what change should I do in code?

Thanking you in anticipation.

Mukesh

[xLJer]

To have the music file play repeatedly, do these steps.

In Sheet1 module, add this code:

Private Sub WindowsMediaPlayer1_StatusChange()
'repeat playing of file with media player control
Dim saveURL As String, tmp As String
  
    With WindowsMediaPlayer1
        tmp = Left(.Status, 5)
        
        If tmp = "" Then Exit Sub
        
        If InStr("STOPPFINISREADY", UCase(tmp)) > 0 Then
            saveURL = .Object.URL
            .Object.URL = ""
            .Object.URL = saveURL
        End If
    End With
    
End Sub

In the module with the Auto_Open sub, change

'make the player control invisible and begin playing the file
    For i = 1 To Sheets(wks.Name).OLEObjects.Count
        If TypeName(Sheets(wks.Name).OLEObjects(i).Object) = "WindowsMediaPlayer" Then
            Sheets(wks.Name).OLEObjects(i).Visible = False
            Sheets(wks.Name).OLEObjects(i).Object.URL = path_fileID
            Exit For
        End If
    Next

to this

    For i = 1 To Sheets(wks.Name).OLEObjects.Count
        If TypeName(Sheets(wks.Name).OLEObjects(i).Object) = "WindowsMediaPlayer" Then
            
            Sheets(wks.Name).OLEObjects(i).Visible = False  'make the player control invisible
            
            'to allow checking player control status from Sheet1 module, rename the
            'control if it was not named "WindowsMediaPlayer1"
            If Sheets(wks.Name).OLEObjects(i).Name <> "WindowsMediaPlayer1" Then
                Sheets(wks.Name).OLEObjects(i).Name = "WindowsMediaPlayer1"
            End If
            
            Sheets(wks.Name).OLEObjects(i).Object.URL = path_fileID     'begin playing the file
            
            Exit For
        End If
    Next

When I run it, a bell (ding sound) rings after the file finishes. I was unable to find the reason for the bell.

[mukeshbaviskar]

Hello Jerry,
It sounds little confusing to me. Please do attach a excel sheet with codes to understand it me better and avoide any mistake.

Thanking you in anticipation.

Mukesh

[xLJer]

The workbook with revisions to play an MP3 file repeatedly is attached.

[mukeshbaviskar]

DearJerry,
Thank you. It's working fine.

Thank you.

Mukesh