I wanted to see YouTube videos displaying the theme songs for a number of TV shows from the 1960s. After browsing through TV.com and other websites, I came up with a list of 79 shows that were of interest to me.
It would have been a chore to manually retype or revise Google searches for each of those shows. Instead, I planned to work up a batch file that would automate the process.
I didn’t want the process to be too automated, however. A batch file that immediately searched for all 79 shows would have several problems. First, it might crash my browser to have that many tabs open at once. Second, Google would probably hinder that kind of mass undertaking. Third, preliminary exploration suggested that there were many YouTube offerings for some of these shows. Even if my browser and Google could handle it, I didn’t want my browser cluttered up with dozens of tabs, while I took my sweet time working through the Google search results, one at a time, to find the YouTube video that I wanted for each of those shows.
So I decided I would design the batch file to interject a PAUSE command after each Google search. I would then have to hit a key, in the command window, before the batch file would advance to the search for the next show. This would give me time to do other things, as needed, and to close out the current search before proceeding. If I got partway through and then had to end my session, I could edit the batch file in Notepad to remove the searches I had already completed.
The basic batch command would look like this:
start Firefox "http://www.google.com/search?q=site:youtube.com+theme+[Name of Show]"
The [Name of Show] had to look like this:
The “%22” was Google’s term for a quotation mark: this would produce a search for “captain kangaroo,” with quotes. The + sign substituted, in Google’s search lingo, for the space bar.
In the batch file, I would have to use two % symbols, not one. So the full batch command would look like this:
start Firefox "http://www.google.com/search?q=site:youtube.com+theme+%%22captain+kangaroo%%22" pause
That would run the search and then pause. I would examine the search results in Firefox and then hit Ctrl-W (or click on the appropriate place) to close the current Google search when I was finished with it. In this example, Google was searching for
site:youtube.com theme “captain kangaroo”
with quotation marks, so as to narrow in on those YouTube entries that used exactly that phrase — Captain Kangaroo — in their titles.
Now I wanted to automate the process of producing all those batch commands. The approach I used was to set up an Excel spreadsheet with four columns. The first and third columns contained the starting and ending parts of the desired batch command. These were the same in every row. The second column contained the official name of the show, with + symbols as shown above. The fourth column concatenated the first three columns into a single batch command. For Captain Kangaroo, the cells looked like this:
Cell A1: http://www.google.com/search?q=site:youtube.com+theme+%%22 Cell B1: captain+kangaroo Cell C1: '%%22 Cell D1: ="start Firefox "&CHAR(34)&A1&B1&C1&CHAR(34)&"!pause"
Then I copied those cells all the way down the spreadsheet as appropriate, repeating cells A1, C1, and D1 on each following row, for each of the shows listed in column B. That gave me a set of 79 commands in column D, one for each show. The resulting command in cell D1 looked like this:
start Firefox "http://www.google.com/search?q=site:youtube.com+theme+%%22captain+kangaroo%%22"!pause
In the cell entries just shown, note several things:
- The leading apostrophe (‘) in cell C1 was provided automatically by Excel. If Excel hadn’t provided it automatically, I might have had to insert it manually, so as to prevent Excel from interpreting that entry as 22% or otherwise getting confused.
- The simple reference to Firefox in cell D1 would be possible if I had added Firefox.exe to my system’s PATH, or if it were in the directory where this batch command would run. I think the simple reference to Firefox worked in my case because, instead, I had put a shortcut to Firefox.exe in C:\Windows. The shortcut was named simply Firefox.
- CHAR(34) was the Excel character for quotation marks. I had to put quotation marks around the full batch command to make it work properly.
Note also the “!pause” at the end of cell D1. The exclamation mark helped me to add the pause command on a separate line. The basic idea was that I would replace the ! signs with the ^p combination in Microsoft Word: ^p was Word’s shorthand for “start a new line here.”
At this point, then, I had produced complete batch commands in each of the 79 rows in column D in the Excel spreadsheet. Now I copied all 79 commands from column D into Notepad, and then copied them from Notepad into Word. (Copying them directly into Word would produce a table, which would be harder to work with.) In Word, I did a search-and-replace (Ctrl-H), replacing ! with ^p. This search and replace would put the PAUSE command on a separate line, so that it would be understood. Finally, I copied everything from Word back into Notepad and saved it as a .BAT file, so that it could be executed from Windows Explorer.