Playing a Window Media Player's playlist in Excel | VBA & Macros

Playing a Window Media Player's playlist in Excel

Page: 1 2

julian

Member

Members

Forum Posts: 46

Member Since:
December 12, 2016

Offline

December 12, 2016 - 6:24 pm

Hi Mynda,

I have an Excel file embedded Window Media Player Control using a pivot table slicer to populate the selected files on column A as the snapshot attached. I also wrote a VBA in a hope to play the file in the list one after another.

Sub test()
Range("A2").Select
Do While Not IsEmpty(ActiveCell)
Text1 = ActiveCell.Value
WindowsMediaPlayer2.URL = Text1
WindowsMediaPlayer2.Controls.Play

' how to continue the next until the previous one has been over?

Selection.Offset(1, 0).Select
Loop
End Sub

Unfortunately, after running through the loop WindowMedia Player 1 alway just play the last file only. Could you please spare some time to take a look at this case and help me correct it? I would appreciate it.
Best regards,

Julian Chen

Attachments

abc.png (23 KB)

See Answer

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

December 12, 2016 - 8:25 pm

Hi Julian,

I think you have to build the playlist first, then you start play.

Try:

Sub test()
Dim NewFile as Variant
Range("A2").Select
Do While Not IsEmpty(ActiveCell)
WindowsMediaPlayer1.playlistCollection.getByName("MyNewPlaylist")
WindowsMediaPlayer1.currentPlaylist.Clear
Text1 = ActiveCell.Value
Set newFile = WindowsMediaPlayer1.newMedia(Text1)
WindowsMediaPlayer1.currentPlaylist.insertItem 0, newMedia
Selection.Offset(1, 0).Select
Loop
'the playlist is created, play it
WindowsMediaPlayer1.Controls.Play

End Sub

julian

Member

Members

Forum Posts: 46

Member Since:
December 12, 2016

Offline

December 13, 2016 - 11:36 am

Hi Catalin,

Thanks for your prompt reply. I copied your code into my Sub Worksheet_PivotTableUpdate function as it was triggered by Pivot Table update then changed the initial cell to "A33" as listed below. However, after running the code the program would be stuck on line 8 with a run-time error 424 - 'Object required". Could you please check it out what's missing?

Regards,

Julian

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim NewFile As Variant
Range("A33").Select
Do While Not IsEmpty(ActiveCell)
WindowsMediaPlayer1.playlistCollection.getByName ("MyNewPlaylist")
WindowsMediaPlayer1.currentPlaylist.Clear
Text1 = ActiveCell.Value
Set NewFile = WindowsMediaPlayer1.newMedia(Text1)
WindowsMediaPlayer1.currentPlaylist.insertItem 0, newMedia
Selection.Offset(1, 0).Select
Loop
'the playlist is created, play it
'WindowsMediaPlayer1.Controls.Play

End Sub

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

December 13, 2016 - 2:56 pm

Dim NewFile as Object, instead of variant, this should be the problem.

julian

Member

Members

Forum Posts: 46

Member Since:
December 12, 2016

Offline

December 13, 2016 - 3:46 pm

Hi Catalin,

The same problem "Object required" still exists. Does it need to create a playlistarray instead?

Regards,

Julian

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

December 13, 2016 - 4:41 pm

Can you upload a sample file?

julian

Member

Members

Forum Posts: 46

Member Since:
December 12, 2016

Offline

December 14, 2016 - 11:16 am

Hi Catalin,

Here you are the attached file. There are 3 Window Media Players embedded in, Please check the tab with name "Media". The rest two works well. Sorry again for bothering you so much.

Best regards,

Julian

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

December 14, 2016 - 2:41 pm

Try changing these 2 lines:

Dim NewFile As Variant

NewFile = WindowsMediaPlayer1.newMedia(Text1)

Or, you can try this version:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim NewFile As Variant

Dim Playlist As IWMPPlaylist
Set Playlist = WindowsMediaPlayer1.newPlaylist("MyNewPlayList", "")
Range("A33").Select

'the following 2 lines were inside the loop, which was wrong... we don't have to clear the list at each loop

WindowsMediaPlayer1.playlistCollection.getByName ("MyNewPlaylist")
WindowsMediaPlayer1.currentPlaylist.Clear

Do While Not IsEmpty(ActiveCell)
Text1 = ActiveCell.Value
NewFile = WindowsMediaPlayer1.newMedia(Text1)
WindowsMediaPlayer1.currentPlaylist.insertItem 0, newMedia
Selection.Offset(1, 0).Select
Loop
'the playlist is created, play it
'WindowsMediaPlayer1.Controls.Play

End Sub

Or, you can try adding items to the new playlist created, then set the playlist as current, instead of clearing the current playlist and adding new items:

Do while...

Playlist.appendItem WindowsMediaPlayer1.newMedia(Text1)

Loop
WindowsMediaPlayer1.currentPlaylist = Playlist

WindowsMediaPlayer1.Controls.Play

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 705

Member Since:
June 25, 2016

Offline

December 14, 2016 - 4:13 pm

Hi Julian

Try this amended code (original code from Catalin above). Tested and working for me.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim NewFile As Variant
Dim Playlist As IWMPPlaylist
Set Playlist = WindowsMediaPlayer1.newPlaylist("MyNewPlayList", "")
Range("A33").Select
WindowsMediaPlayer1.playlistCollection.getByName ("MyNewPlaylist")
WindowsMediaPlayer1.currentPlaylist.Clear
Do While Not IsEmpty(ActiveCell)
Text1 = ActiveCell.Value
'NewFile = WindowsMediaPlayer1.newMedia(Text1)
Set NewFile = WindowsMediaPlayer1.mediaCollection.Add(Text1)
WindowsMediaPlayer1.currentPlaylist.insertItem (WindowsMediaPlayer1.currentPlaylist.Count), NewFile
Selection.Offset(1, 0).Select
Loop

'the playlist is created, play it
WindowsMediaPlayer1.Controls.Play

End Sub

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 705

Member Since:
June 25, 2016

Offline

December 14, 2016 - 4:33 pm

Hi Julian

Attached is my own sample playlist.

Hope this helps.

Cheers

Sunny

julian

Member

Members

Forum Posts: 46

Member Since:
December 12, 2016

Offline

December 15, 2016 - 1:53 pm

Hi Catalin / SunnyKow's :

Let me report my test results as follows:

I' m sorry to say all the solutions provided below were failed and I'm not sure did it has something with my 64-bit operating sytem. In order to get support memory amounts over 4 GB for Power query and DAX running I've installed my Excel 2013 and PowerBI desk in 64-bit versions. If this is the case, how the VBA script can be modified?

Scripts from Catalin - got the same error message "Object does'nt suppport this property or method" and stuck on the line in bold font type

(1).

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim NewFile As Variant
NewFile = WindowsMediaPlayer1.newMedia(Text1)
Range("A33").Select
Do While Not IsEmpty(ActiveCell)
WindowsMediaPlayer1.playlistCollection.getByName ("NewFile")
WindowsMediaPlayer1.currentPlaylist.Clear
Text1 = ActiveCell.Value
Set NewFile = WindowsMediaPlayer1.newMedia(Text1)
WindowsMediaPlayer1.currentPlaylist.insertItem 0, newMedia
Selection.Offset(1, 0).Select
Loop
'the playlist is created, play it
'WindowsMediaPlayer1.Controls.Play
End Sub

(2).

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim NewFile As Variant

Dim Playlist As IWMPPlaylist
Set Playlist = WindowsMediaPlayer1.newPlaylist("MyNewPlayList", "")
Range("A33").Select

'the following 2 lines were inside the loop, which was wrong... we don't have to clear the list at each loop

WindowsMediaPlayer1.playlistCollection.getByName ("MyNewPlaylist")
WindowsMediaPlayer1.currentPlaylist.Clear

Script from SunnyKow's fixed code - got the error message: "Method 'add' of object ' IWMPMedia Collectio2' failed" and stuck on the line in bold font type

'the playlist is created, play it
WindowsMediaPlayer1.Controls.Play

End Sub

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 705

Member Since:
June 25, 2016

Offline

December 16, 2016 - 1:20 am

Hi Julian

The worksheet I attached earlier (with Catalin's code) works fine with me. No errors at all.

I wonder if any other members encountered the same error as yours.

Anyway, I have attached another version that I have. The codes are different. Hopefully it will work for you this time.

Sunny

Answers Post

julian

Member

Members

Forum Posts: 46

Member Since:
December 12, 2016

Offline

December 16, 2016 - 1:00 pm

Hi SunnyKow,

Just tried run your file on my computer and it worked well. Therefore, I would modify my script based on your version later on. By the way, Just wonder if you have ever tried to play the transcripts files together with the videos synchronously also using VBA script? I think it's worth a trial. Any way, Thanks for your early Christmas gift. You are my this year's Santa Claus.

Best regards,

Julian

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 705

Member Since:
June 25, 2016

Offline

December 16, 2016 - 1:16 pm

Hi Julian

Happy to know it is working now.

"Just wonder if you have ever tried to play the transcripts files together with the videos synchronously also using VBA script?"

What transcript files are you referring to?

julian

Member

Members

Forum Posts: 46

Member Since:
December 12, 2016

Offline

December 16, 2016 - 2:19 pm

It's about SubRip file format with (.srt) extension or simply a plain text file showing what was said (ii.e. subtitle or caption) in a video / movie....

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 705

Member Since:
June 25, 2016

Offline

December 16, 2016 - 5:12 pm

Hi Julian

I can get the SubRip file format to run with the VLC media player (not from Excel) but not on WMP.

I think I need to download some codec. I will set this as my 2017 new year resolution to learn more about SubRip file format.

Sunny

julian

Member

Members

Forum Posts: 46

Member Since:
December 12, 2016

Offline

December 17, 2016 - 12:17 am

Hi Sunny,

Just came out two more findings for your reference: Both simplified Chinese characters and m4a file extensions could not be recognized when you Set sMedia = WindowsMediaPlayer2.mediaCollection.Add(SelVideo). And it's very nice to hear your new year resolution. I believe you must have had a very fruitful year 2016. I also wish you Merry Christmas and a happy new year soon in next two weeks. Thank you very much again for the wonderful gift you gave me.

Best regards,

Julian

julian

Member

Members

Forum Posts: 46

Member Since:
December 12, 2016

Offline

January 31, 2017 - 7:14 am

Hi Sunny,

Regarding the script as listed below you provided for me last time, could you please do me one more favor by adding a loop to restart the playlist when it comes to the end? Tahnks advance.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
SelVideo = "Start"
    Set sPlayList = WindowsMediaPlayer2.playlistCollection.newPlaylist("MyPlayList")
    WindowsMediaPlayer2.currentPlaylist = sPlayList

    '1 row above the playlist
    ctr = 32
    Do While SelVideo <> ""
        ctr = ctr + 1

        'Add the song/video name from the current row
        SelVideo = Worksheets("Media").Cells(ctr, 1).Value

        'Exit if end of playlist
        If SelVideo = "" Then Exit Do

        Set sMedia = WindowsMediaPlayer2.mediaCollection.Add(SelVideo)
        WindowsMediaPlayer2.currentPlaylist.insertItem (WindowsMediaPlayer2.currentPlaylist.Count), sMedia
    Loop

    'Play the playlist
    WindowsMediaPlayer2.fullScreen = False
    'WindowsMediaPlayer2.Width = 550 '400 '260
    'WindowsMediaPlayer2.Height = 380 '355 '220
    WindowsMediaPlayer2.Controls.Play
End Sub

Julian

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 705

Member Since:
June 25, 2016

Offline

February 1, 2017 - 1:22 am

Hi Julian

Add this to your code:

'Play the playlist
WindowsMediaPlayer2.settings.setMode "Loop", True
WindowsMediaPlayer2.fullScreen = False

BTW I have learned that WMP does not support .srt (subtitle) but WMP Classic does. Since I don't have WMP Classic, I am now using VLC instead and it looks OK although I don't use subtitle myself. (My 2017 New Year resolution completed Cool ).

Hope this helps.

Sunny

julian

Member

Members

Forum Posts: 46

Member Since:
December 12, 2016

Offline

February 1, 2017 - 7:47 am

Hi Sunny,

Thanks again for your great support. Furthermore, to my surprise you were that serious to make your commitment and completed it so quickly. You are really awesome. I do admire your resolution.

Best regards,

Julian

Page: 1 2