Launch external file based on cell value

[Tim Miller]

I'm pulling in external data that updates often....

I want to be able to launch this file via command line code: "C:\Program
Files\My Directory\MyFileName.exe"
With a switch added: /switch (always the same)
Then a number value added, but that number value is found in a column and
changes from row to row.

So the final command I'm trying to run would be: "C:\Program Files\My
Directory\MyFileName.exe" /switch ####

What I'd LIKE is to assign this command to some key combination such that it
grabs the value for the #### based on the row that currently has focus.
As an alternative, this could be a button or link that resides in each row,
where the value of #### is referenced from a cell in that row, and then that
button or link fills down as the data is updated.

I feel like this would be too hard at all... but I'm also seeing that I have
no clue. Hoping someone can point me in the right direction.

Thank for any direction!

Tim

 

[NOPIK]

http://www.suodenjoki.dk/us/productions/articles/vbashellexecute.htm

 

[Chip Pearson]

Tim,

You can use the Shell function to run a program. The following code will run
"C:\Test\Test.exe" passing it a switch parameter whose value comes from
column "C" of the row of the ActiveCell.

Const COLUMN_WITH_SWITCH_DATA = "C"
Shell "C:\Test\Test.exe" & _
" /switch " & ActiveCell.EntireRow.Cells(1,
COLUMN_WITH_SWITCH_DATA).Text, _
vbNormalFocus


Note that Shell starts the program and then continues. It does NOT wait for
the Shell'd program to finish. If you need to wait for the program to
terminate before continuing on in VBA, see
http://www.cpearson.com/excel/shellandwait.htm .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)

 

[Tim Miller]

Thanks Chip! This was the ticket for sure! Very helpful.
To complete this I just need to figure out how to obtain the column letter
based on the value of cell A.

For A1:G1
Find which cell has the value = "Project_ID".
And then assign the letter value of that column to a variable.

I'm assuming there is some sort of GetColumn, but not finding it yet.

This way I can apply the program to entire workbooks where the exact column
changes from worksheet to worksheet.

 

[Chip Pearson]

Tim,

You can adapt the code below to meet your needs. If "Project_ID" is found in
the row of the ActiveCell, the variable C will contain the column number, R
is set to the cell containing "Project_ID". Use the function ColLetter to
get the column letter.


Sub AAA()
Dim C As Variant
Dim R As Range

On Error Resume Next
Err.Clear
C = Application.Match("Project_ID", ActiveCell.EntireRow, 0)
If IsError(C) Then
Debug.Print "NOT FOUND"
Else
Set R = ActiveCell.EntireRow.Cells(1, CLng(C))
Debug.Print "Address: " & R.Address, "Column Letter: " &
ColLetter(R)
End If
End Sub

Function ColLetter(R As Range) As String
' > 26 for columns AA to ZZ, > 702 for columns AAA to XFD (XL2007)
ColLetter = Mid(R.Address, 2, 1 + Abs(R.Column > 26) + Abs(R.Column >
702))
End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)