Vba in Excel: Create 'Open With' Macro Or Create, Run and Delete Bat File

[pemartins]

Sorry for the double post but I just noticed a slight flaw in the Kamikaze Karaoke code if I don't have the subfolder included in the cell with the name of the song. Is it possible just to add to this code the inclusion of the subfolder path in C column?

Sub Kamikaze_Karaoke()
    Dim sFolder As String
    Dim sProgram As String
    Dim sSong As String
    Dim iFile As Integer
    Dim x
    Dim Target As Range

    Set Target = Selection.Cells(1, 1)
    If Not Intersect(Target, Columns("D:D")) Is Nothing Then
        sFolder = [F1]
        sProgram = [F2]
        Application.Calculate
        sSong = Range("D" & [F3]).Value

        'Create Bat file
      On Error Resume Next
        Kill "d:\Karaoke.Bat"      'Delete existing file

        iFile = FreeFile
        Open "d:\Karaoke.Bat" For Output As #iFile    'Open the file
      Print #iFile, """" & sProgram & """ """ & sFolder & "\" & sSong & """"    'Write the command
      Close #iFile                    'Close the file

        'Execute the command
      x = Shell("cmd.exe /c d:\Karaoke.Bat")
        Cancel = True

    End If

End Sub

Thank you once again!

 

[NARAYANK991]

Hi ,

Can you post the contents of the various cells ( Target , [F2] , [F3] , and any entry in column D ) that are used by your macro ? Or preferably upload your workbook ?

Narayan

 

[pemartins]

Surely I can Narayan, here it is and thank you once again.

 

[NARAYANK991]

Hi ,

I can move it there , with your permission ; as and when you have the time , please add the instructions in this same thread. Of course , you will find the thread in the Vault section.

Narayan

 

[NARAYANK991]

Hi ,

F3 contains this formula : =ALEATÓRIOENTRE(4,5) ; what is the English equivalent ?

I assume it is RANDBETWEEN.

Narayan

 

[pemartins]

By all means please do so and I'll start working on a mini tutorial for it right away.

Will you move the whole thread or is it better to make a new post with only the workbook and instructions, so it's 'cleaner' for the forum members to see?


EDIT: exactly, it is RANDBETWEEN.

 

[NARAYANK991]

Hi ,

See your file now.

I think in the interest of those who may want to get a complete picture , the entire thread can be moved.

Narayan

 

[pemartins]

Perfect! The subfolder path is perfectly included and all is working!


Oh of course, you're totally right, move the entire thread then. Details are very important, specially in a coding forum.

Once again I give you the hugest thank you that you can imagine, for all your knowledge, for your time, for your kindness, for your patience and for everything!

 

[pemartins]

Tutorial

So this is a tutorial in how to use this worksheet to create an executable list of all your karaoke files.

This worksheet was made from a simple idea I had and was put together with the knowledge of GCExcel, NARAYANK991 and shrivallabha.

The basics

1- First of all, if you are new to 'home karaoke' it's highly recommended to download and install a karaoke player. KaraFun Player is my recommendation. It's free and will do the job perfectly. Supports all the needed formats like cdg+mp3, wmv, kar, midi and more, and supports zipped files.
It also can play the karaoke directly in secondary screens connected to the computer without the need of any configuration.

2- Then you need the songs, of course! You can buy them in cd or dvd format, but since these are destined to become obsolete, the best purchases should be in digital format. The most popular digital format is cdg+mp3, which are a couple of files of the same song, one containing the on-screen lyrics and the other the audio. Both files must have exactly the same name.
When you have your songs, I recommend to name the files properly in this format: "Artist Name - Song Name.xxx". I also recommend zipping the cdg+mp3 songs into one single zip file so it's more practical and saves you a bit of disc space.

3- Then you should create a list of the files inside your karaoke folder or folders, in Excel format. The other option is to introduce each one name by name in this sheet, which is ok if you have a couple of dozen karaoke songs, but it would be a nightmare it you had thousands of karaoke songs like a karaoke freak like me does!
So a free software like the free version of Song List Generator does this for you, with the limit of 1.000 songs. If you have more than that just make folders of 1.000 songs tops and keep on getting the list and pasting it into the Excel sheet.

How to use the Excel sheet created in this thread

This worksheet opens every karaoke song file listed with your favorite karaoke player software! No need to have the file types of your karaoke songs associated in Windows with your player since this sheet has code to make "open with".

You need to enable macros in order to play the songs directly from your sheet.

1- First introduce the basic locations. In cell F1 introduce the folder where your karaoke folders are stored. In my case, they are stored at my desktop, so I introduced:

C:\Users\P\Desktop

which is the path to my desktop.

In cell F2 introduced where the exe file of your karaoke player is at. I use KaraFun Player and it's stored at this location:

C:\Program Files (x86)\KaraFun Player\KaraFunPlayer.exe

2- Then introduce the names of the songs just like in your files. You should start introducing in cell A4 if the list is empty, if not start introducing them after the last one on your list in column A.
If you are using Song List Generator to create your list, simple copy the artist and song columns in Song List Generator and past them into A4 or the next space available in column A.
You should list only one file type at a time. If your folder contains let's say cdg+mp3 files, zip files and wmv files it's better to do one file type at the time because of what comes next in 4-.

Be careful with the names if you introduce them by hand! After the artist name you must add a space and before the song name you must put a space, so it comes out exactly like the file name is written!

3- Now you should introduce in column C the subfolder where your karaokes are stored. In my case they are stored in a folder called "Karaokes" on the desktop. If there is another subfolder, let's say there is a folder called "Romantic" with your karaokes inside the "Karaokes" folder, then insert in the specific line in C Column:

Karaokes/Romantic

4- Now to the song names with extension in column D. There is a formula in column D with the file extension added manually. This is why previously I asked you to do one file type at a time since you must copy this formula, changing the file extension if needed. As an example, if your file type is cdg and mp3, just change .zip to .cdg in the formula and paste it down until needed. If it is wmv just change .zip to .wmv and so on.

4- After your song names are all properly inserted, you should order them alphabetically. Select every line from line 4 inclusive and down and go to Data--> Sort and choose sort by Column A in "Sort by".

And that's it! Your list is made and now you only have to double click on the song name in column D to start playing your karaoke song! Have fun!

Note: every time you are done using the sheet, as you close it you'll be asked if you wanna save the changes. This occurs because the aleatory number generator in Kamikaze Karaoke changes every time, so if you didn't make any change in the sheet that you wanna keep, just choose "No".

The Kamikaze Karaoke button

So this a button that plays a song randomly from your list! This is a golden generator of youtube videos!
A guy (or more) on a stage holding a mic waiting to sing a song... that he probably never heard in their entire life and he must do the best job he can! So it's funny as hell to see the singers make a fool out of themselves!

1- Let's start with a 'language issue'! In cell F3 you must have the formula RANDBETWEEN in the language of your Excel. So if your excel is in english, what is written in F3 should be =RANDBETWEEN(x,y); if your Excel is in portuguese like mine it should be =ALEATÓRIOENTRE(x,y). And then press <enter> so the content is changed.

2- In that same F3 cell, you must type the first line of your songs and the last one. So lets say that you have songs from line 4 to line 100, F3 should contain exactly this:

=RANDBETWEEN(4,100)

In the current sheet as there are only two song, they start at line 4 and end at line 5, so the current formula is this:

=RANDBETWEEN(4,5)

3- The Kamikaze Karaoke code is set to create a temporary file in drive D so it can run. If you don't have a drive D, for Kamikaze Karaoke to work you must change the location of the file to an existent folder in the code.
I had an issue with this because for some reason my computer wasn't creating the file in drive C so I set it to drive D. If you don't have a drive D, create a folder in the root of C, name it something like "Temp" and set the location of the file in the code to c:\Temp
Don't store it in C drive root because for me it wasn't working either, I have no idea why. Probably something to do with Windows 8.1. So make a folder first and use it.

The file is attached to this post. Enjoy!

 

[NARAYANK991]

Hi ,

Thank you for taking so much of trouble.

Narayan

 

[pemartins]

Hello again, I found a slight flaw in the Kamikaze Karaoke code, which can only be noticed if using songs with special characters in the names. When the code creates the bat file for songs with special characters, they just come out wrong...

I found that out in this song: the song name is "Amália Hoje - Gaivota (versão 2).wmv" and the bat file created came out like this:

The "á" and the "ã" just came out has strange characters and the file didn't play in KaraFun Player because the file name of the song came out different, only the player opened but with no song playing. Then I tried other songs with special characters and the same issue happened.

The current code for the Kamikaze Karaoke button is this:

Sub Kamikaze_Karaoke()
    Dim sFolder As String, subFolder As String, sProgram As String, sSong As String
    Dim iFile As Integer
    Dim x As Variant
    Dim Target As Range

    Set Target = Selection.Cells(1, 1)
    If Not Intersect(Target, Columns("D:D")) Is Nothing Then
      sFolder = [F1]
      sProgram = [F2]
       
      Application.Calculate
      sSong = Range("D" & [F3]).Value
      subFolder = Range("C" & [F3]).Value

'      Create Bat file
      On Error Resume Next
      Kill "d:\Karaoke.Bat"      'Delete existing file

      iFile = FreeFile
      Open "d:\Karaoke.Bat" For Output As #iFile    'Open the file
      Print #iFile, """" & sProgram & """ """ & sFolder & "\" & subFolder & "\" & sSong & """"    'Write the command
      Close #iFile                    'Close the file

'      Execute the command
      x = Shell("cmd.exe /c d:\Karaoke.Bat")
      Cancel = True
    End If
End Sub

In the double-click code all is fine and this issue doesn't exist because I'm using shrivallabha's code that doesn't create a bat file. This is shrivallabha's double-click code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim sFolder As String, sProgram As String, sSong As String
  Dim wShell As Object: Set wShell = CreateObject("WScript.Shell")
  If Not Intersect(Target, Columns("D:D")) Is Nothing Then
  sFolder = [F1]
  sProgram = [F2]
  sSong = Target.Offset(0, -1).Value & "\" & Target.Value
  sCmd = """" & sProgram & """" & " " & """" & sFolder & "\" & sSong & """"
  wShell.exec sCmd
  Cancel = True
  End If
End Sub

In portuguese and spanish we use a lot of this special characters... Is there a way that this issue can be solved? Or maybe make Kamikaze Karaoke button code based on shrivallabha's code so no bat file is created?

I attached to this post the worksheet with four song names with special characters to test, if needed.

Thank you very much once again. And I'm sorry for being asking for help again.

 

[shrivallabha]

Those character things can really be pain and special characters can be more so. These probably don't sit in the ANSI characters but that is just a guess and I have near zero experience of handling such things via code.
https://www.microsoft.com/typography/unicode/cs.htm

If the non-bat code works for you then here's how it should look (without testing ) You may have to correct if I have missed but this should be good starting point for changing the code.

Sub Kamikaze_Karaoke()
  Dim sFolder As String, subFolder As String, sProgram As String, sSong As String, sCmd As String
  Dim iFile As Integer
  Dim x As Variant
  Dim Target As Range
  Dim wShell As Object: Set wShell = CreateObject("WScript.Shell")

  Set Target = Selection.Cells(1, 1)
  If Not Intersect(Target, Columns("D:D")) Is Nothing Then
  sFolder = [F1]
  sProgram = [F2]
  
  Application.Calculate
  sSong = Range("D" & [F3]).Value
  subFolder = Range("C" & [F3]).Value
  sCmd = """" & sProgram & """" & " " & """" & sFolder & "\" & subFolder & "\" & sSong & """"
  wShell.Exec sCmd
  Cancel = True
  End If
End Sub

Hth,

 

[pemartins]

shrivallabha it worked perfectly! Every time I click the Kamikaze Karaoke button the random song just starts playing, whether it has special characters in the name or not!

I only notice a funny thing, for some reason you have to execute a song with the double-click method on the list before you can use the Kamikaze Karaoke button, in order for it to work. It's not a big issue, in fact it's not an issue at all, you just have to do it and then you can use the Kamikaze Karaoke button as many times in a row as you want! Even if this issue can't be fixed, it is still perfect!

I attached to this post the new worksheet using both of your codes that do not create the bat file and make that 'open with' job.

Thank you very much once again for everything!

 

[pemartins]

Never mind that small issue, I just found out that if I click on cell D1 and after on the Kamikaze Karaoke button it always works, so I just put the Kamikaze Karaoke button on the top of a part of cell D1 and a note on the button to click on D1 first if needed. So it's no issue at all.


So to give the final touch to the worksheet a search engine would be very useful. I made a button and formatted some cells to do this, the only thing missing now is the code for the button!
It looks like this:

And I have in mind a search engine in these parameters:
- search any result, complete or partial, non-case sensitive;
- the text to search is typed in B2;
- it only searches column D;
- after the text to search is typed, it only works by pressing the find button;
- tell the number of matches found in C2;
- when Find button is clicked, it goes automatically to the first result found;
- if the Find button is clicked again then it goes to the next found match, and so on;
- when it reaches the end of the document, it just restarts to search from the beginning;
- if there are no matches, it just makes a sound and does nothing; and types zero (0) in C2.

Is there a way that this can this be done? I attached the worksheet to this post, if needed.

Thank you very much once again!

 

[pemartins]

Hi,

Just wanted to post the very last version of the file, search macro and instructions already included!

Hope it'll be useful for someone else!

Thank you all once again for everything!

 

[Stevie B]

I just stumbled on this post. I hope this helps someone

SUB YourMainFileRoutine
do this
don't do that
do some more
then

'PREPARE FILENAME BY ADDING QUOTES TO FRONT AND REAR OF FILENAME
FN$ = Chr(34) & "C:\Users\P\Desktop\Karaokes\Phil Collins - Another Day In Paradise.zip" & Chr(34)

'CALL FUNCTION TO
'OPEN KARAFUN W FILE
OpenKfun FN$
End Sub


Public Function OpenKFun(strFile As String)
Dim x As Variant
Dim PrgPath As String
DoEvents
PrgPath = "C:\Program Files (x86)\KaraFun Player\KaraFunPlayer.exe "

x = Shell(PrgPath & strFile, 1)
DoEvents

End Function

the variable PrgPath MUST have the space at the end of the string
Works Great for me without the batch files and DOS boxes