December 12, 2016
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Hi Julian,
I think you have to build the playlist first, then you start play.
Try:
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
December 12, 2016
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Try changing these 2 lines:
Dim NewFile As Variant
NewFile = WindowsMediaPlayer1.newMedia(Text1)
Or, you can try this version:
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
VIP
Trusted Members
June 25, 2016
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
VIP
Trusted Members
June 25, 2016
December 12, 2016
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
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
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
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
VIP
Trusted Members
June 25, 2016
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
December 12, 2016
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
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
June 25, 2016
December 12, 2016
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
December 12, 2016
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
VIP
Trusted Members
June 25, 2016
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 ).
Hope this helps.
Sunny
1 Guest(s)