Invoking Autoit to read a variable in XLSX

[chandler1983]

Hi Everyone,

I have a spreadsheet with these columns:

File    Date     Desc

The number of files can differ from day-to-day, however are always 10 digits long.

I am trying to have the file numbers hyperlink to an auto-it file which will open that file up in our in-house program.

The command I have (which works without a variable)  is:

Run ("C:Program Filesfile_masterfile_masterv2.exe -c:OPEN -k:{file number here} ")

Is there a way to set the (file number here) portion to use the file that is on the spreadsheet?

IE:  if there is a file named 1234567890,  when I click it in Excel it would convoke this code in an auto-it script, use that file number in replace of (file number here)

Is this at all possible to have the auto-it script receive the variable from excel, and if so, how to syntax it in?

THANKS EVERYONE, love this site!

[JLogan3o13]

hi, chandler1983. Does the {file number here} always write to the same cell, or is it like a running total? If it is always in the same spot, you can do this with a little VBA code.

[chandler1983]

HI,

The file numbers are always in cell A2 and A3 etc...

[mrflibblehat]

Something Like This

#include <Excel.au3>

Global $vSpreadSheetName = "book1.xlsx"

Local $vExcel = _ExcelBookOpen(@ScriptDir & "/" & $vSpreadSheetName, 0)

For $i = 1 to 3 ;Rows to Run Down
    Local $vReadCell = _ExcelReadCell($vExcel, $i, 1) ; Column A row $i
    ConsoleWrite($vReadCell & @CRLF)
    ;Run Your Program Here
Next

Im sure you could do it by range but I'm not sure how, I think Water is your man for that good luck

[JLogan3o13]

I'm sorry, I may have misunderstood. I thought you were looking to call the script from within the workbook. Is that not the case?

If you are looking to call the script by clicking on a cell, and input that cell's value for your script, I would do the following in VBA:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Target.Address = "$A$1" Then
        Call MyMacro
    End If
End Sub
_________________________________________________________________________________

Private Sub MyMacro()
    RetVal = Range("A1").Value
    Shell ("C:\Program Files\file_master\file_masterv2.exe -c:OPEN -k:{" & RetVal & "}")
End Sub

Edited October 2, 2013 by JLogan3o13

[chandler1983]

Correct,

So each cell in the A column can be hyperlinked to the same autoit file and use the text inside the cell to fill in for the variable to autoit,

So if this was the case:

File                    Date                  Desc

1234567890     today                 n/a

9876543210     yesterday         n/a

So when i click on 1234567890 it will run the autoit script and use 1234567890 as the variable to open that file...

hope this helps

 

[JLogan3o13]

I'm away from my desktop now. I know you can use the ActiveCell.Value option, to give you the value of whatever cell you click on, but can't come up with the syntax right off the top of my head. Quick and dirty, you could always create two If statements:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Target.Address = "$A$1" Then
        Call MyMacro
    End If
    
    If Target.Address = "$A$2" Then
        Call MyMacro2
    End If
    
End Sub

Private Sub MyMacro()
    RetVal = Range("A1").Value
    Shell ("C:\Program Files\file_master\file_masterv2.exe -c:OPEN -k:{" & RetVal & "}")
End Sub

Private Sub MyMacro2()
    RetVal = Range("A2").Value
    Shell ("C:\Program Files\file_master\file_masterv2.exe -c:OPEN -k:{" & RetVal & "}")
End Sub

If someone hasn't come along with the cleaner solution, I will look at it again once I return home.

[JLogan3o13]

Try this, much cleaner:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If (Target.Address = "$A$1" Or Target.Address = "$A$2") Then
        Shell ("C:\Program Files\file_master\file_masterv2.exe -c:OPEN -k:{" & Target.Value & "}")
    End If
End Sub

[chandler1983]

Thanks JLogan3o13,

Would this part:

If (Target.Address = "$A$1" Or Target.Address = "$A$2") Then

work if I had 58 file numbers in A 1....A58?

It seems to be invoking the program and passing along some data but not the file number (seems like a null)

[chandler1983]

Something Like This

#include <Excel.au3>

Global $vSpreadSheetName = "book1.xlsx"

Local $vExcel = _ExcelBookOpen(@ScriptDir & "/" & $vSpreadSheetName, 0)

For $i = 1 to 3 ;Rows to Run Down
    Local $vReadCell = _ExcelReadCell($vExcel, $i, 1) ; Column A row $i
    ConsoleWrite($vReadCell & @CRLF)
    ;Run Your Program Here
Next

Im sure you could do it by range but I'm not sure how, I think Water is your man for that good luck

 

Thanks mrflibblehat,

just curious, how do I call the variable in the run line?  this part in underline:

Run ("C:Program Filesfile_masterfile_masterv2.exe -c:OPEN -k: __________________")

Thansk!