Is it possible to pass arguments to local hyperlinks to .cmd files?

SmartShark · 02 Apr 2012

I am facing the following interesting situation.

I have an excel file with many columns. One column contains line numbers (for example 3150).

Code:

Source                       Line Number
if(this.getValue()==0)        3150
for(i=0;i<value;i++)           3175
while(true)                       3200
...                                  ...

I want to set up hyperlinks, such that when the user clicks on one of the cells in the Line Number column, a batch program is called with the line number as its command line argument, so that the source file is opened at the specific line number.

I was able to set up a hyperlink without any command line arguments for one cell, but then I had to set the value explicitly in the batch file as follows -

Code:

cd "%~dp0"
notepad++ sourcefile.txt -n3150

So this batch file opens the "sourcefile.txt" at line number 3150 in the Notepad++ editor. However, it's not practical to write a separate batch file for every such line number in the excel sheet. So I am wondering if it is possible to somehow pass the line number as an argument to the batch program.

Ztruker · 02 Apr 2012

You can pass a variable to a bath file. Change the line to read

notepad++ sourcefile.txt -n%1

Duzzy · 02 Apr 2012

I could find any information on getting this to work using a hyperlink but I did find a way to do it using some VB code. Took me a little while to get it to work though and it might not be perfect, plus you need to enable macros and remove your hyperlinks.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 3 Then
        Dim strArg As String
        Dim RetVal As Integer
    
        RetVal = MsgBox("Run Batch", vbYesNo)
    
        If RetVal = 6 Then
        strArg = Target
            RetVal = Shell("C:\ExcelExperiments\BatchFile.bat" & " " & strArg, 1)
        End If
    End If
End Sub

This is one example, it runs every time you change the selected cell and first checks if the newly selected cell is in Column C and if it is then it asks you if you want to run the batch file. It needs to be added to each worksheet you want it to work with.

To get to the VBA editor you need to add it to the ribbon by going to,
File > Options > Customize the ribbon and on the right select "Main tabs" and Check "Developer".

Next click the Developer tab on the ribbon and click "Visual Basic". If your using Excel 2003 a quick Google should find a way to do this.

When the editor opens you should see Sheet1,2,3 and ThisWorkbook on the left. Double click a Sheet to open it and then paste my code into it.

Changing the code.
To change the column change Target.Column = 3 to the column number you need, eg. A=1, B=2 etc...

Removing the prompt may prove a problem when entering new data or accidently clicking in the column but if you want to try it then remove the following lines,

1. RetVal = MsgBox("Run Batch", vbYesNo)
2. If RetVal = 6 Then
3. End If ---- Either one

Instead of automatically running you can run it either via a keyboard shortcut or a button.

I really don't know how much you know about this stuff but if you want to set it up as Macro to use a keyboard shortcut or a button and need help or if there's any other problems with this then let me know.