Jump to content
Sign in to follow this  
chandler1983

Invoking Autoit to read a variable in XLSX

Recommended Posts

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!

Share this post


Link to post
Share on other sites
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.


√-1 2^3 ∑ π, and it was delicious!

How to get your question answered on this forum!

Share this post


Link to post
Share on other sites
chandler1983

HI,

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

Share this post


Link to post
Share on other sites
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


[font="'courier new', courier, monospace;"]Pastebin UDF | Prowl UDF[/font]

Share this post


Link to post
Share on other sites
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 by JLogan3o13

√-1 2^3 ∑ π, and it was delicious!

How to get your question answered on this forum!

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites
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.


√-1 2^3 ∑ π, and it was delicious!

How to get your question answered on this forum!

Share this post


Link to post
Share on other sites
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

√-1 2^3 ∑ π, and it was delicious!

How to get your question answered on this forum!

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
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!

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×