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

[pemartins]

Greetings code Gods! :D

I need help to create some kind of 'open with' in Excel and I've been told I can't do it in a formula and must use vba to do it. I want to execute an external file with a specific program that it's not Excel, from my Excel sheet.

I'm making the list of my karaoke songs (I'm addicted to karaoke!) and I wanna make the songs execute from the list. I was able to do this for the file types that are by default played with the karaoke player (I use KaraFun Player) but I hit the wall with zipped files.
Most of my karaoke files are zipped (all the ones in cdg+mp3 format) and it's not practical to associate all zip files to poen with a karaoke player program, so I need to open the files with the player from Excel.

My sheet is just like this example (it took me a lot of time, reading and testing to be able to do this, I really don't know much about this at all... but it looks cute!):

(HIPERLIGAÇÃO is HYPERLINK in portuguese)

So instead of the formula I have in E column, I need an 'open with formula' that opens the tracks named in D column (following the path in F1 cell and subfolder path in C column) with KaraFun Player (which has his full path in F2 cell).
Is it possible to make this?


An alternative should be to create a code, also in vba, to create a .bat file, run it and then delete it.

So if I type something like this in Run or Cmd:

"C:\Program Files (x86)\KaraFun Player\KaraFunPlayer.exe" "C:\Users\P\Desktop\Karaokes\Phil Collins - Another Day In Paradise.zip"

I get my 'open with' result, and KaraFun Player opens playing Another Day In Paradise (or another day in hell for my neighbors since I suck at singing this! But no worries, they love me!:D)

I tried also creating a .bat file with that exact content, nothing more or less, and when I execute it, it also works perfectly.

So can any of this solutions be done? How can I turn my E column into a open with device?

I uploaded a zip package with the example of my sheet and all the stuff needed for testing:

https://www.dropbox.com/s/1nspjc2sd0x3bk7/test%20karaoke.zip

Thank you in advance and all the best for everyone!

 

[GCExcel]

Hi pemartins,
I didn't download your files but this piece of code should work.
You have to copy this code in the sheet module containing your songs.

When you DOUBLE-CLICK on a cell in column E (containing the songs names), the macro will create a bat file called "Karaoke.bat" and execute the command.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim sFolder As String
    Dim sProgram As String
    Dim sSong As String
    Dim iFile As Integer
    Dim x

    If Not Intersect(Target, Columns("E:E")) Is Nothing Then
        sFolder = [F1]
        sProgram = [F2]
        sSong = Target.Value

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

        iFile = FreeFile
        Open "c:\Temp\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 c:\Temp\Karaoke.Bat")
        Cancel = True

    End If

End Sub

Cheers.

 

[pemartins]

Thank you very much code God!

It seems to be exactly what I need indeed! But I need to ask for a little more help because... Well, because I'm really a big ignorant in this subject... sorry...

When you say:

You have to copy this code in the sheet module containing your songs.

What I did was to press Alt+F11, then I went to "View"-->"Code" and pasted your code (you have no idea how long it took me to be able to get this far!... lol).
This is what I got:

Then I closed the window and was back at my worksheet.

If what I did was correct, what should I do now to 'get the party started'? I tried double-clicking on the cells in E column but nothing happened, except when I clicked on the ones containing my formula but only the usual happened, which was to open the zip files with Windows Explorer (my default program to open zip files).

Thank you very much once again and I'm sorry for my basic questions...

 

[GCExcel]

Hello,

There's no basic questions... I'm so used to do this that I sometime take for granted that everybody knows how to do it...
Here's how to insert your code in your sheet:

- From Excel, press ALT+F11 to open the VBA editor
- In the left pane, you will see "Project - VBA Project"
- Navigate to find your file (ex. VBAProject (Name of your workbook)) and click on the "+" to expand the nodes
- Now, you should see "Microsoft Excel Objects", click on the "+" to expand this node
- Now, you should see Sheet1(name of your sheet), etccc
- Double click on the Sheet that contains the song lists and paste the code in the big white frame.

** The code must be placed in the Sheet and not in a Module because it uses the double-click event.
If it doesn't work it's probably because you copied the code in a standard module.

Let me know if it works now...

 

[pemartins]

Thank you very much once again, not only for your knowledge and time but also for your patience.

I followed your steps and did them, but still nothing happens. But then I noticed that you wrote back:

When you DOUBLE-CLICK on a cell in column E (containing the songs names), the macro will create a bat file called "Karaoke.bat" and execute the command.

and maybe this is why it's not working, because the song names are in D column. In E column I had my formula just with a hyperlink to the file with no 'open with' feature.

I uploaded only my small excel file attached to this post if you wanna check that out. Maybe there's something I should change in order for the macro to work.

Thank you very much once again for everything!

ps- the file uploaded doesn't have the code yet.

 

[GCExcel]

Oups... my mistake...

Please replace this line :

    If Not Intersect(Target, Columns("E:E")) Is Nothing Then

by this line :

    If Not Intersect(Target, Columns("D:D")) Is Nothing Then

So the code will execute only if you double click in column D (where the song name is)

 

[pemartins]

Still no luck...

I previously tried that, changing the range from E to D but also with no luck... I took my time to examine your code and try to learn about it (strangely it made sense to me, it must be my memory coming back from my coding classes back in high school about 22 years ago, Pascal and QBasic I think...) and tried to change something in order to get it to work and also tried that.

Could it be that for some reason my file format or something (like permissions?) doesn't allow me to execute macros? I already changed the file format, I saved the file as Excel book that allows Macros .xlsm, since it was in Excel 97-2003 format, but still I double click the song names in D column and nothing happens.
In this computer I'm using now and will use for karaoke I have Excel 2013 so I can choose any format at all. Do you think it can be something like that?


EDIT: I just tried doing everything also in OpenOffice Calc to see if I could get it to work and when I go to the menu and choose to execute the macro with the code I get this error (it's in portuguese but in english is something like this):
"Error in Scripting Framework in Basic, Standard.test_open_with.Worksheet_BeforeDoubleCLick"
(I named the macro test_open_with)

I don't know if it's for being a different program or if something must be changed in order to work.

Thank you and sorry for me being such a bother.


Re-EDIT: The error it returns in OpenOffice is on this line, saying something like "argument non optional":

If Not Intersect(Target, Columns("D:D")) Is Nothing Then

 

[GCExcel]

Hello,
I don't have open office, so I can not test.

1)
Does the macro creates a .bat file in c:\temp?
If yes, can you open it and see if the command line is correct? Can you try run it from there?

2)
to debug, you can also open the macro, put the cursor on the first line of code starting by "If...." and press F9 to put a marker there. Then go in Excel and double-click in a cell to execute the macro. It will stop at the marker.
Press F8 to run the code line by line.
See where it stops.

 

[pemartins]

1) No it doesn't create the .bat file, I check that previously but forgot to mention it. And I kept on checking on it a few times.

2) So I went to the macro and pressed F9 and got this:

Closed the window, went back to Excel and double-clicked on a line in D column and got this:

Then I pressed F8 and got a yellow line right after the "If..." line:

And as I go on pressing F8 the yellow line just keeps lowering one line, like this:

Apparently it stops in every line. I pressed F8 as many times as it took to go all the way to end of the code, until it wasn't possible to do it anymore. But nothing seemed to happen, no temp folder or bat file were created.

I really must be screwing up big time... sorry...


EDIT: Funny thing, if I delete the "Not" in the line "If Not Intersect...", when I press F8 to debug it doesn't stop on each line and goes all the way to the end.

 

[NARAYANK991]

Hi ,

I tried out the code by downloading the .xls file , and copying and pasting the code.

When I ran it by clicking on D4 , it created a .bat file , with the following entry :

"C:\Program Files (x86)\KaraFun Player\KaraFunPlayer.exe" "C:\Users\P\Desktop\Alphaville - Forever Young.zip"

When I clicked on D5 , I got a .bat file with the following entry :

"C:\Program Files (x86)\KaraFun Player\KaraFunPlayer.exe" "C:\Users\P\Desktop\Phil Collins - Another Day In Paradise.zip"

Can you download this file and try it out ?

Narayan

 

[pemartins]

So it works just fine for you?

I must be doing something really, really wrong... I just tried the file you just uploaded and I still can't get it work! I double-click and nothing happens...

So when I open your file with Excel, I get the usual warning about macros and then I click to execute and activate macros and everything. But still nothing happens when I double-click!... Neither on D4 or D5 or anywhere else...

Is there something I must do or click, an option I must choose or something to activate the macro when I open the sheet? I'm so frustrated right now because I realize I must be wasting everybody's time and the problem must be something that I should do and am not doing...

Thank you very much for your help!

 

[NARAYANK991]

Hi ,

Before we go further , can you confirm whether you are able to run macros in any other Excel file ?

Narayan

 

[pemartins]

I really don't know... I don't have any other Excel files with macros to test... Can you tell me where I can download one for testing?

EDIT: My OS is Windows 8.1 and Office version is 2013, don't know if that's relevant or not.

Re-EDIT: Can it be some kind of permissions issue in Windows?

 

[NARAYANK991]

Hi ,

Can you check your settings in Excel as follows :

Click on the Office button , then on Excel Options , then on Trust Center , Trust Center Settings , Macro Settings.

See if the checkbox :

Trust Access to the VBA Project Object model

is checked.

Narayan

 

[pemartins]

It wasn't checked. I just checked it and macro definitions are set to run or activate all macros. But still no luck...

Ok just figured out what was wrong!!! It's working now!!!

For some reason the directory of the bat file wasn't being created (temp) and by so neither the bat file; when I changed the bat file location in the code to d:\ I just double-clicked a name of a song in D column and it opened KaraFun Player playing the selected song! It works perfectly! For some strange reason, the bat file must be set to be created in a already existent folder.

Thank you very much GCExcel for the code and all your help and time and patience. Thank you very much Narayan for your help and time and patience, and also for your file.

This is awesome, can't wait to get my list ready!

Thank you very much code Gods! All the best for you! You're awesome!

 

[shrivallabha]

You already have one answer which works for you. I was wondering if following tweak works for you. If it does then there's no need to create bat file and execute it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim sFolder As String
  Dim sProgram As String
  Dim sSong As String
  Dim iFile As Integer
  Dim x
  If Not Intersect(Target, Columns("D:D")) Is Nothing Then
  sFolder = [F1]
  sProgram = [F2]
  sSong = Target.Value
  Shell Environ("ComSpec") & " /c " & """" & sProgram & """ """ & sFolder & "\" & sSong & """"  'Write and Execute the command
  Cancel = True
  End If
End Sub

 

[pemartins]

Thank you Shrivallabha. It didn't work for me, or at least I think it didn't. Not sure anymore after all this trouble I had to be able to figure out how to run a macro!
When I double-clicked the song names in D column nothing happened, so I think the creation of the bat file is needed.


If I can ask for a little more help, I would like to ask for only two more things.

1) In the previous code that worked perfectly, would it be possible to include in the path for the song the subfolder path in column C? I don't have all the files in the same folder because I have everything organized by language, and the international ones are also organized by alphabetical letter since they are many. So the inclusion of the subfolder path introduced in C column just before the song name column would be very helpful. Right now I get this in the bat file:

"C:\Program Files (x86)\KaraFun Player\KaraFunPlayer.exe" "C:\Users\P\Desktop\Phil Collins - Another Day In Paradise.zip"

and if possible this would be more handy (the subfolder \Karaoke appears before the song name):

"C:\Program Files (x86)\KaraFun Player\KaraFunPlayer.exe" "C:\Users\P\Desktop\karaokes\Phil Collins - Another Day In Paradise.zip"

If it is too much trouble just forget about it because I can insert that path in the formula that types the song name and make it work. This would be better mainly because of practical and aesthetic reasons.

2)

Kamikaze Karaoke
Similar to regular karaoke, but in this version each participant takes to the microphone without any foreknowledge as to what song they will be performing and must complete said song whether they know it or not. Best results are achieved on at-home karaoke machines where discs can be inserted and played completely at random.

So based on this definition of Kamikaze Karaoke you are able to see where I'm getting at!
Would it be possible to create a button (I believe this I know how to do, but not the code of course) that just plays a totally random song with KaraFun Player in a range of column D?
Let's say I wanna choose any random song from D4 to D10 and from D20 to D30, or in alternative just in the entire range of column D. So I press the button and some random song starts playing.
Can this be made?

Thank you all very much once again!

 

[NARAYANK991]

Hi ,

Can you try and do this yourself ?

1. You know how to insert a CommandButton ( preferably a Forms Control one ) , and assign a macro to it.

2. Rename the code which you already have , and rename it from its present Worksheet_BeforeDoubleClick to what ever you wish. Assign this to the button , so that when ever you click the button , this renamed macro will run.

3. The only change you need to do is to remove the parameters of the Worksheet_BeforeDoubleClick procedure i.e. the following part needs to be removed : (ByVal Target As Range, Cancel As Boolean)

4. Suppose your song names are in the range D4 through D100 ; all you need to do is to generate a random number between 4 and 100 in the worksheet itself ; assign a cell to store this number ; suppose the cell is M1 , then in M1 , put in the formula =RANDBETWEEN(4,100)

5. Within the code , you have the statement : sSong = Target.Value

Change this to the following two statements :

Application.Calculate
sSong = Range("D" & [M1]).Value

When ever you click the button , the worksheet is recalculated , as a result of which the number in cell M1 will change , and the song name will change accordingly.

Narayan

 

[shrivallabha]

While I got the code for ComSpec working it created another problem of not closing DOS windows.

I do not have KaraFunPlayer and nor do I wish to install it as I know I am bad singer

Here's a different take based on firing Winamp player [I have just involved one exe for testing purpose] which also takes into account your request part one.

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

You can try out what Narayan has outlined for you as that'll help you understand the code you'll be using for sometime.

 

[pemartins]

Uau you guys are amazing! I don't know how to thank you properly for all this so I'll do all I can do with words and tell you thank you very much once again for everything!

@Narayan so I was able to do the creation of the button (a forms control one as you recommended), named it "Kamikaze Karaoke!!!" and was able to assign a macro to it; then I edited the code and typed this (I placed =RANDBETWEEN(4,5) in cell F3):

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

    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

Instead of RANDBETWEEN in the formula in the worksheet, I had to use the portuguese equivalent which is ALEATÓRIOENTRE (found it here).

But when I click the button, I get this error: "Run-time error '424': Object required".
Then I click "Debug" and it puts this line in yellow:

    If Not Intersect(Target, Columns("D:D")) Is Nothing Then

I must have forgot something or made some mistake, can you point out what it must be?


@Shrivallabha your code worked just perfectly! And as you mentioned it already has the inclusion of the subfolder path!
If I noticed correctly, it also doesn't create the bat file, like the previous one you placed for me to test.

ps- Everyone can sing! Not everyone can sing professionally and be a star, but everyone can sing! You just have to pick the right songs that suit you and keep it in a low range. You've never heard and never will hear Eminem singing "I will always love you" from Whitney Houston nor "O sole mio" like Luciano Pavarotti, but Eminem can sing (his bank account proves it! lol). And if you don't really think you can sing, just take a couple of friends with you to the stage and sing all together, because two or more voices tend to harmonize, like in a choir.
So now you've no excuse not to sing!


Thank you very much code Gods!

 

[NARAYANK991]

Hi ,

Just change the statement to :

If Not Intersect(Target, Range("D:D")) Is Nothing Then

Narayan

 

[shrivallabha]

My guess is that the method is searching for object "Target" and not getting it.

So please add one more row just below Dim x:

Dim Target as Range

And then set that Target as:

Set Target = Selection.Cells(1, 1)

Just before this line:

If Not Intersect(Target, Columns("D:D")) Is Nothing Then

 

[pemartins]

Ok so changing that line like Narayan sugested the code is like this:

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

And it worked perfectly! As I click the button, KaraFun Player starts playing a random song from the interval I previously selected. I tested by clicking several times in a row and each time it restarts the random function, generating a different number and a consequently a random song.

That's it, I got everything I wanted for the list and much more.

Here is what it looks like now:

Maybe you have no idea of it, but this is pure gold to any karaoke user, either a home user like me or a professional karaoke dj. If you have all your songs names arranged properly, like let's say "Artist Name - Song Name.xxx", you just have to run a program that creates a list of the files in a folder to excel format (this one in the free version does it until 1.000 songs, if you have more songs than that just run it as many times as needed) and then paste it to this worksheet to very quickly make a perfect list of all your karaokes, with all songs to be executable from the sheet! Additionally you just need to install the free and excellent KaraFun Player.

I'm attaching the file to this post. There are 2 files, the one named "test karaoke chanddo.org FINAL MODEL alt DoubleClick subfolder.xls" has Shrivallabha's code for the double-click and the subfolder path; for the other I inserted it in the songs names.


So thank you very, very much GCExcel, Narayan and Shrivallabha! You guys are amazing! All the best for you!

 

[NARAYANK991]

Hi ,

I think your final version should go into the Vault section of this forum.

Narayan

 

[pemartins]

Indeed it should, I just checked what the Vault is and this should be there so it can be useful to more people. It should be named something like "Excel's Karaoke Song List" or something.

It should be posted with a brief how to use instructions and a description of the additional useful software like I posted in the previous post.

How can it be posted there? Can I just create a new thread there or should someone else do it?