Launch external file based on cell value
 

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

Launch external file based on cell value
 

http://www.suodenjoki.dk/us/producti...ellexecute.htm

Launch external file based on cell value
 

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" wrote in message
...
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

Launch external file based on cell value
 

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" wrote in message
...
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" wrote in message
...
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

Launch external file based on cell value
 

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)



"Tim Miller" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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