Pick data from excel and add to string

[Vikramjeet]

I have the below script where the sequence is very important as the 1st Script generates a log file and then the 2nd Script uses the value from the log file as the loop counter.

Here is the what I want and the sequence

Script 1

1-  Send("S*D/<The value here should come from cell A1 of excel workbook data.xlsx>/17AUG/US") so the keystrokes sent would be e.g. S*D/321/17AUG/US (321 was the value in excel cell A1)

Script 2

2- After Script 1 is complete, Script 2 performs its actions which is sending formats in a loop a certain number of times

3- Once Script 2 completes its actions, I want to go back to Script 1 and this time pick data from Cell A2 of the excel workbook and perform its actions

4- This loop continues till there is no more cells with a value in column A of Excel workbook

expandcollapse popup

;Script 1
#include <MsgBoxConstants.au3>

WinActivate("Application")
WinWaitActive("Application")

Send("S*D/$Value from Excel$/17AUG/US")
Sleep(1000)
Send("{ENTER}")
Sleep(2000)
Send("MB")
Sleep(1000)
Send("{ENTER}")
Sleep(3000)

Script2()
Func Script2()
    Local $iPID = Run("Script2.exe")
EndFunc

;Script2
#include <MsgBoxConstants.au3>

$file = @ScriptDir & "\T.log"

$iNum = StringRegExp(FileRead($file), "(?sm).*^\h*(\d{1,3})", 1)[0]
ConsoleWrite($iNum & @CRLF)

WinActivate("Application")
Local $hWnd = WinWaitActive("Application", "", 5)

If $hWnd Then
    For $i = 1 To $iNum
        Send("GTIM"& $i &"/OVR")
        ;Send("GTIM"& $i &"/OVR "{ENTER}"")
        Sleep(1000)
        ;Send("/OVR")
        Send("{ENTER}")
        Sleep(1000)
    Next
Else
    MsgBox(0, "", '"Application" window not found.')
EndIf

 

[FrancescoDiMuro]

@Vikramjeet

Why don't you use Excel UDF to automate Excel stuffs?

[Vikramjeet]

1 hour ago, FrancescoDiMuro said:

@Vikramjeet

Why don't you use Excel UDF to automate Excel stuffs?

I would love to. I started with AutoIt 1 week ago. I have no knowledge or experience of writing scripts. I will work on your suggestion. Thank you for your guidance 

[caramen]

12 hours ago, Vikramjeet said:

I would love to. I started with AutoIt 1 week ago. I have no knowledge or experience of writing scripts. I will work on your suggestion. Thank you for your guidance 

You request a lot of coding from other in multiple posts.... Even if we help you* You dont try anything. Exept CTRL - C , V . Try to learn at le@st.

Edited August 18, 2018 by caramen

[Vikramjeet]

I am trying and struggling with this. Thank you for your help

[Vikramjeet]

I have managed to capture cell value from Column A, row 1, 2, 3, 4........without opening the excel file. The script makes a certain 'Application' active. Now instead of showing the cell values in a pop up message, I want the script to send it as key strokes to the 'Application'. How can I achieve this

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible =  0

WinActivate("Application")
WinWaitActive("Application")

If IsObj($oExcel) Then
   For $a = 1 to 5
   $oExcel.Application.WorkBooks.Open("C:\Users\Charlie\Desktop\Data.xlsx")
      msgbox(1 ," ", $oExcel.Application.Cells($a,1).Value )
   Next
EndIf

 

[water]

You open the Excel workbook but do not see it as it is being done invisible  

[Vikramjeet]

Well, I actually don't want to open the excel file and still read the data. I need to add something to close it or figure a way to read without opening it

[FrancescoDiMuro]

@Vikramjeet

How can you see what there is in a box without open it?

 

[Vikramjeet]

3 minutes ago, FrancescoDiMuro said:

@Vikramjeet

How can you see what there is in a box without open it?

 

This script is reading the cell values and doing exactly what I want. Now I need to close the excel file and set the loop to end when the row is blank

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible =  0

WinActivate("Application")
Local $hWnd = WinWaitActive("Application", "", 5)

$oExcel.Application.WorkBooks.Open("C:\Users\Charlie\Desktop\Data.xlsx")

If $hWnd Then
   For $a = 1 to 5
      Send("S*D/"& $oExcel.Application.Cells($a,1).Value &"'US/NYC")
      Sleep(1000)
      Send("{ENTER}")
      Sleep(3000)
   Next

EndIf

 

[FrancescoDiMuro]

With Excel UDF:

• _Excel_Open();
• _Excel_BookOpen();
• _Excel_RangeRead();
• _Excel_BookClose();
• _Excel_Close().

 

[Vikramjeet]

Ok. Let me play with that. Can you help exit the loop in my script if blank cell in found or if it finds the word 'END' in a cell 

[FrancescoDiMuro]

@Vikramjeet

After reading the value of the cell in your For...Next loop with _Excel_RangeRead():

If $strCellValue <> "" And $strCellValue <> "END" Then 
    ExitLoop
Else
    ; Do your stuffs
EndIf

 

[Vikramjeet]

expandcollapse popup

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible =  0
#include <MsgBoxConstants.au3>

WinActivate("Application")
Local $hWnd = WinWaitActive("Application", "", 5)

$oExcel.Application.WorkBooks.Open("C:\Users\Charlie\Desktop\Data.xlsx") ;Opens the excel file. I need to close it at the end of the script
local $iCell = $oExcel.Application.Cells(31,1).Value

If $hWnd Then
   For $a = 1 to $iCell
      Send("S*D/")
      Send($oExcel.Application.Cells($a,1).Value)
      Send("'DUL/SX*E")
      Sleep(1000)
      Send("{ENTER}")
      Sleep(3000)
      Send("MB")
      Send("{ENTER}")
      Sleep(3000)
      
$file = @ScriptDir & "\T.log"

$iNum = StringRegExp(FileRead($file), "(?sm).*^\h*(\d{1,3})", 1)[0]
ConsoleWrite($iNum & @CRLF)

If $hWnd Then
    For $i = 1 To $iNum
        Send("DSIM"& $i &"/AST")
        Sleep(2000)
        Send("{ENTER}")
        Sleep(2000)
    Next
Else
    MsgBox(0, "", '"APPLICATION" window not found.')
EndIf
Next
EndIf

I have the above script which opens an excel file as not visible. I need to add a line to close it at the end of the script so the user does not have to do that

[water]

Why don't you use the Excel UDF that comes with AutoIt. See functions _Excel_Open, _Excel_BookOpen, _Excel_RangeWrite, _Excel_BookClose and _Excel_Close.

[caramen]

Also if you got a process that is open you can easily close it.