15

I have the following code that download xml file with the subtitle of a video from YouTube

Sub Test()
Dim http        As Object
Dim oStream     As Object

Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "http://video.google.com/timedtext?lang=en&v=qANA6POtuFo", False
http.send
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write http.responseBody

oStream.SaveToFile ThisWorkbook.Path & "\Sample.xml", 2
oStream.Close
End Sub

But it doesn't work for other videos for example I tried this link v=4Z3EJrh7_5k

Any idea how to do the download with any video with a subtitle?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
YasserKhalil
  • 9,138
  • 7
  • 36
  • 95

1 Answers1

7

As far as I researched, you cannot download from 4Z3EJrh7_5k because it's subtitle is not a file someone uploaded, but it is generated automatically.

To see if a video has any subtitle file, you can use http://video.google.com/timedtext?type=list&v=qANA6POtuFo, and it will list every file with languages inserted in the video. You can note that it shows only one, even if you go to the video and click, there are two (English and English (Automatically generated))

Moacir
  • 617
  • 4
  • 19
  • 1
    Thanks a lot for reply. So there is no way to do that using VBA ... Any other way – YasserKhalil Jul 30 '18 at 16:06
  • 1
    It looks like there is no file for it to download, since it is generated automatically. It will only work if its a submitted subtitle. – Moacir Jul 30 '18 at 16:37
  • 1
    If you may, check http://titaniumhelp.fullerton.edu/m/FAQ/l/498105-how-do-i-export-the-youtube-auto-generated-captions, but it is a more manual approach – Moacir Jul 30 '18 at 16:39
  • 1
    Also, this question: https://stackoverflow.com/questions/14013431/extract-automatic-captions-from-youtube-video . It seems inconsistent whether you can or not download the automatic. But a way to check is to use the list I mentioned on my answer – Moacir Jul 30 '18 at 16:50
  • 1
    Thanks a lot for your valuable replies. Hope one day I could develop a code using VBA to do such task – YasserKhalil Jul 30 '18 at 19:19
  • 1
    Sorry I couldn't help more. These are the information I got about your problem. – Moacir Jul 30 '18 at 19:37
  • 1
    No sorry. You do well. Thank you very much – YasserKhalil Jul 30 '18 at 20:41