BrandonSanchez

Incremental copy paste

24 posts in this topic

Hello Autoit community

I started using Autoit about 2 weeks ago and have found myself lost looking for information. Majority of the Help options are very easy to understand but I think that my general lack of knowledge about programming has slowed my progress. I tried very hard to not be that guy asking annoying questions on the forum but I’m afraid I’m out of options…

The steps below are my attempt to explain my scenario

Step 1: open excel file

Step 2: Read range of Excel column “A1” – “A???”) The range of rows will vary from sheet to sheet.

Step 3: take the values (one cell at a time) and place one value into my web browser ran program.

Step 4: if that value has been added before (indicated by the PixelSearch) than delete (represented by the mouse click) and start the loop over moving onto the next cell.

Step 5: if that value is new then submit the value (represented by the 2 mouse clicks at the end of the (if function)

The code listed below is as far as I got. I hope this explanation will suffice and I look forward to any help I can get.

Capture.JPG.e97e00fae45cf0dddc5e50b663d0

(From WinActive(“Paste Too”) down is working as intended.)

 

Share this post


Link to post
Share on other sites



You have a lot going on there ... I will give some high level feedback without re-writing your code.  First problem, the range of used cells in Excel varies.  To solve that problem you can use this:

$oRange = $oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeLastCell)

The next issue is the for loop.  I think you would want to read the cells into an array first using _Excel_RangeRead.   Once in the array, you can look for unique values using _ArrayUnique.  Then, after you have a unique array of values from your spreadsheet you can send them to your website in the for loop (your current approach goes for 10,000 times cell by cell every time - this would be much more efficient).  That would, based on your explanation, also eliminate the pixelsearch approach to finding duplicates after you send them (because there would be none before you send).

Hope some of that helps.

 


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

There are better ways to automate a website, with IE.au3 is delivered with AutoIt. I suggest use _IEDocInsertHTML, this is the better/safer solution.

Edit: it's only temporary.

Edited by AutoBert

Share this post


Link to post
Share on other sites

Thank you for the feedback. I will look into your suggestions and update with new information. 

As for the PixelSearch elimination. I receive a new worksheet every week that i will run through this code so since ArrayUnique cannot sort the data that was placed into my website from previous worksheets it will be need to prevent future repetitive inputs. Sorry for not clearing that up in my original explanation. 

Thank you again jfish.

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

You can also read Table from the webpage:

Quote

_IETableWriteToArray

Reads the contents of a Table into an array

Edited post#3:

1 hour ago, AutoBert said:

it's only temporary.

Look for _IEFormElementSetValue() and _IEFormSubmit.

Edited by AutoBert

Share this post


Link to post
Share on other sites

Hi @BrandonSanchez
Like @Jfish said you can do it in that way and about his high level recommendation I cant make that work and I end up with:

$aResult = _Excel_RangeRead($oWorkbook, Default,$oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeLastCell), 1)

But that only will give you last used cell on the entire Worksheet and not per row.

If the number of row will vary you can read the entire worksheet to an array with this code and then play with that array:

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $oExcel = _Excel_Open()
If @error Then
    MsgBox(0, "Error", "Error creating Excel object")
    _Excel_Close($oExcel)
    Exit
EndIf
 Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Excel1.xlsx", Default, Default)
 If @error Then
     MsgBox(0, "Error", "Error opening the workbook")
     _Excel_Close($oExcel)
     Exit
 EndIf
 Local $aResult = _Excel_RangeRead($oWorkbook, Default, Default, 1)
 If @error Then
     MsgBox(0, "Error", "Unable to read workbook")
     _Excel_BookClose($oWorkbook)
     Exit
 EndIf
 _ArrayDisplay($aResult, "Data", "")
 _Excel_BookClose($oWorkbook)

Note that this is not 1D array so you will need to make some test with _ArrayUnique() because the way to do it is not the same.

Regards
Alien.

Share this post


Link to post
Share on other sites

@alien4u I did not know the worksheet had more than one used column since the OP related to Col A onl but you should be able to swap out a specific range for

.UsedRange

And I think it would work for any given column instead of all of them.  


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites
5 hours ago, Jfish said:

@alien4u I did not know the worksheet had more than one used column since the OP related to Col A onl but you should be able to swap out a specific range for

.UsedRange

And I think it would work for any given column instead of all of them.  

Hi @Jfish I tried that before and I cant find a way to use that per row or per columns, you can take a look to this:


If you know a way to do it or you could find I way will be very nice.
Thanks you.

Regards
Alien.

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

This post explains how to do it for a single column:

This is a working example:

#include<Excel.au3>
Local $oAppl = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oAppl,@ScriptDir&"\testdata.xlsx") ; your workbook here

Global Const $xlUp = -4162  ;global constant for Excel up, finding last row (thanks Water ... pulled from your post)
With $oWorkbook.ActiveSheet
     $lastRow = .Cells(.Rows.Count, "B").End($xlUp).row ; switch out your cols here
EndWith
MsgBox(0,"",$lastRow)

@water I did not notice $xlUp in the excel constants.  I know you could probably fill that file to a crazy size if you added everything but wondering if would consider adding it?

Edited by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites
1 hour ago, Jfish said:

This post explains how to do it for a single column:

This is a working example:

#include<Excel.au3>
Local $oAppl = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oAppl,@ScriptDir&"\testdata.xlsx") ; your workbook here

Global Const $xlUp = -4162  ;global constant for Excel up, finding last row (thanks Water ... pulled from your post)
With $oWorkbook.ActiveSheet
     $lastRow = .Cells(.Rows.Count, "B").End($xlUp).row ; switch out your cols here
EndWith
MsgBox(0,"",$lastRow)

@water I did not notice $xlUp in the excel constants.  I know you could probably fill that file to a crazy size if you added everything but wondering if would consider adding it?

Hi @Jfish and thanks you again but I'm still unable to make that work per row and not per columns.
With this:

With $oWorkbook.ActiveSheet
     $lastRow = .Cells(.Rows.Count, "B").End($xlUp).row ; switch out your cols here
EndWith

I'm able to get how much rows per columns or how much columns from A to H with this:

With $oWorkbook.ActiveSheet
     $lastRow = .Cells(.Columns.Count, "H").End($xlUp).column ; switch out your cols here
EndWith

I also try this to get last value per column but I still need last used cell per row, notice is not the same last used cell(last cell with a value) so I dont' need last cell per column or last cell per row, I need last used cell per row.

Global Const $xlUp = -4162  ;global constant for Excel up, finding last row
$LNC = $oWorkbook.ActiveSheet.Range("F65535").End($xlUp)
$sResult = _Excel_RangeRead($oWorkbook, Default, $LNC, 1)

Regards
Alien.
 

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

The code I supplied was last used row in a col.  This is last used col in a row

;Find the last used column in a Row: row 1 in this example
Global Const $xlToLeft = -4159
With $oWorkbook.ActiveSheet
    $lastCol = .Cells(2, .Columns.Count).End($xlToLeft).Column; where '2' is the row ... 
EndWith
MsgBox("","",$LastCol)

I am not 100% sure I follow what you are trying to do but does that get you there?

Edited by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites
1 hour ago, Jfish said:

The code I supplied was last used row in a col.  This is last used col in a row

;Find the last used column in a Row: row 1 in this example
Global Const $xlToLeft = -4159
With $oWorkbook.ActiveSheet
    $lastCol = .Cells(2, .Columns.Count).End($xlToLeft).Column; where '2' is the row ... 
EndWith
MsgBox("","",$LastCol)

I am not 100% sure I follow what you are trying to do but does that get you there?

Thank you again, I was able to understand and get that.
What I want is last used cell in a row...
I mean:
- In Row 1 last used cell is: F1(get data there)
- In Row 2 last used cell is: G1(get data there)
And get all this values from last used cell for in a row(row by row) of an entire Worksheet.

Regards
Alien.
 

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

Okay, now I think I understand ... how about this (selects cell in question):

;Find the very last used cell in a Row:
$rowNum=3; this is the row number you are targeting to find the last used cell
With $oWorkbook.ActiveSheet
    $LastUsedCellInRow = .Range("IV"&$rowNum).End($xlToLeft).Select
EndWith

This one returns the specific address:

;Find the very last used cell in a Row:
$rowNum=3; this is the row number you are targeting to find the last used cell
With $oWorkbook.ActiveSheet
    $LastUsedCellInRow = .Range("IV"&$rowNum).End($xlToLeft).Address
EndWith
MsgBox("","",$LastUsedCellInRow)

 

Edited by Jfish
1 person likes this

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

Hi again @Jfish
Thanks to your help I figure it out and end with this code:

#include<Excel.au3>
Local $oAppl = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oAppl,@ScriptDir&"\Excel1.xlsx") ; your workbook here
Global Const $xlUp = -4162
Global Const $xlToLeft = -4159
;Number of Rows
With $oWorkbook.ActiveSheet
     $countRow = .Cells(.Rows.Count, "A").End($xlUp).row
EndWith
For $i = 1 To $countRow
    ; Number of Columns in 1 Row
    With $oWorkbook.ActiveSheet
        $countCol = .Cells($i, .Columns.Count).End($xlToLeft).Column
    EndWith
    $ColLetter = _Excel_ColumnToLetter($countCol)
    $aResult = _Excel_RangeRead($oWorkbook, Default,$ColLetter&$i , 1)
MsgBox(0,"",$aResult)
Next

And I re use your last code like this:

#include <Excel.au3>
Local $oAppl = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oAppl,@ScriptDir&"\Excel1.xlsx") ; your workbook here
Global Const $xlUp = -4162
Global Const $xlToLeft = -4159
;Number of Rows
With $oWorkbook.ActiveSheet
     $countRow = .Cells(.Rows.Count, "A").End($xlUp).row
EndWith
For $i = 1 To $countRow
    ; Number of Columns in 1 Row
    With $oWorkbook.ActiveSheet
    $LastUsedCellInRow = .Range("IV"&$i).End($xlToLeft).Address
EndWith
MsgBox("","",$LastUsedCellInRow)
Next

But the return value is "$$column$$row" literally like this so that give me an extra $ sign, example:
"$C$3"(real value return) but it should be "C3"

Anyways I like this code more than the first one.

Thanks you so much.

Regards
Alien.

Share this post


Link to post
Share on other sites

This will remove the extra dollar signs ...

$rowNum=3; this is the row number you are targeting to find the last used cell
With $oWorkbook.ActiveSheet
    $LastUsedCellInRow = .Range("IV"&$rowNum).End($xlToLeft).Address
    $LastUsedCellInRow = StringReplace($LastUsedCellInRow,"$","")
EndWith
MsgBox("","",$LastUsedCellInRow)

 


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

@Jfish I have tried reading the help and after reading your continued conversation with Alien4u this is what I came up with.

Capture3.JPG.65557567e7bb5e65198d140cae6

The good news is I get no errors but the bad news is that it doesn’t work.

I am getting it to open the worksheet and copy but its only copies 0 from somewhere and paste 0 into notebook but the cell values have names. Any suggestions because the reading material isn’t that easy for me to understand. Also, I’m 100% sure I have the For loop wrong

Share this post


Link to post
Share on other sites

@Jfish & @alien4u 

(Please ignore post #16 Jfish i added more below)

 

I looked at your examples, read the help menu, and looked at other people's posts on similar issues to try and get a grasp of what is going on and this is what i was able to come up with. I get no errors when running this but i also get no results. 

Capture4.JPG.30c2b0d1efc10080d6b6ea85d28

 

I really appreciate both of your help.

 

Share this post


Link to post
Share on other sites

#18 ·  Posted (edited)

Could you please use the code tags <> when posting?  It makes it easier on our side.  That said $lastRow is only giving the a numeric value of the last row.  Therefore your range argument in _Excel_RangeRead is a single row number (when it should be a range). Try this instead:

$aResult = _Excel_RangeRead($oWorkbook,Default,"A1:A"&$lastRow)
_ArrayDisplay($aResult)

EDIT: I would also add some debugging.  When you are not sure where your errors are (this was just one - I did not study the whole thing) then best to test results as you step through the code with consolewrite or msgbox to see if you are getting the values you expect along the way.

Edited by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

I believe I'm making major progress @Jfish but I have 1 last issue that i'm stumped on.

Global Const $xlUp = -4162
With $oWorkbook.ActiveSheet
     $lastRow = .Cells(.Rows.Count, "A").End($xlUp).row
  EndWith
Local $aResult = _Excel_RangeRead($oWorkbook,Default,"A1:A"&$lastRow)
For $i = 0 to $lastRow
   _ArrayToClip($aResult,"", Default,0, $lastRow)

This code gets my array accurately (See below pic) but the last issue is when the loop restarts it keeps pasting the start of the _ArrayToClip which is "Test1"of the array, does the _ArrayToClip need to be its own loop inside of the $i loop? 

 571afbe77556a_ArrayDisplay.JPG.2b194967d  

Share this post


Link to post
Share on other sites

That is because you are starting your loop at 0 and "Test1" is in the 0 index of the array.  Try starting your for loop at 1 like this: for $i=1 to $lastrow.  Then the first value you will pass would be 2 in the example above.


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

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

  • Similar Content

    • amphoric
      By amphoric
      Hi,
      I am struggling converting my 1D array to a 2D array and then showing that in my List View. Below is my current code, where I would like to take the array from the input box, and have that displayed as a list within the list view this is a list of computer names, and then update the 2nd column of each line once I have ran a ping and a registry change on each computer name, with either 'Completed' and 'Failed'
      I have checked out the AutoIt Arrays page here: https://www.autoitscript.com/wiki/Arrays , but unfortunately I cannot get my head around it. Any help would be greatly appreciated.
      I hope this makes sense.
      Thanks
       
      #include <Date.au3> #include <ButtonConstants.au3> #include <ComboConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <GUIListBox.au3> #include <GuiListView.au3> #Region ### $Form1 = GUICreate("Title", 515, 533, -1, -1) GUISetBkColor(0xFFFFFF) $Pic2 = GUICtrlCreatePic("Images\pic.jpg", 16, 16, 212, 28) $Label1 = GUICtrlCreateLabel("Label", 240, 16, 271, 33, $SS_CENTER) GUICtrlSetFont(-1, 17, 800, 0, "") $Combo1 = GUICtrlCreateCombo("", 256, 406, 241, 25, BitOR($GUI_SS_DEFAULT_COMBO,$CBS_SIMPLE)) $Label2 = GUICtrlCreateLabel("Please select the location", 45, 401, 210, 20, $SS_CENTER) GUICtrlSetFont(-1, 10, 800, 0, "") $ChangeButton = GUICtrlCreateButton("Change", 286, 487, 75, 25) $RePromptButton = GUICtrlCreateButton("Re-prompt User", 366, 487, 139, 25) $Checkbox1 = GUICtrlCreateCheckbox("I have ensured that my selection is correct.", 10, 487, 273, 25) GUICtrlSetFont(-1, 10, 400, 0, "") $ManLocation = GUICtrlCreateLabel("Please enter the location:", 59, 448, 189, 20, $SS_RIGHT) GUICtrlSetFont(-1, 10, 800, 0, "") GUICtrlSetState(-1, $GUI_HIDE) $ManLocInput = GUICtrlCreateInput("", 256, 445, 241, 24) GUICtrlSetState(-1, $GUI_HIDE) $Label5 = GUICtrlCreateLabel("for this device:", 138, 418, 109, 20, $SS_CENTER) GUICtrlSetFont(-1, 10, 800, 0, "") $Label3 = GUICtrlCreateLabel("Asset Number:", 98, 72, 107, 20, $SS_CENTER) GUICtrlSetFont(-1, 10, 800, 0, "") $Input1 = GUICtrlCreateInput("", 215, 69, 241, 24) $AddMulti = GUICtrlCreateLabel("Add Multiple Assets", 356, 55, 98, 13, $SS_RIGHT) GUICtrlSetFont(-1, 8, 400, 6, "") GUICtrlSetColor(-1, 0x808080) GUICtrlSetCursor (-1, 0) $List1 = GUICtrlCreateListView("", 16, 104, 481, 278) $Add = GUICtrlCreateButton("Add", 461, 68, 35, 25) GUISetState(@SW_SHOW) #EndRegion ### ;### StartForm ### $FormaMultiple = GUICreate("Title", 277, 418, -1, -1) GUISetBkColor(0xFFFFFF) $Label1 = GUICtrlCreateLabel("Please add 1 asset number per line.", 8, 11, 253, 20, $SS_CENTER) GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif") $OK = GUICtrlCreateButton("OK", 56, 375, 75, 25) $Edit1 = GUICtrlCreateEdit("", 20, 40, 233, 329) $Cancel = GUICtrlCreateButton("Cancel", 136, 375, 75, 25) GUISetState(@SW_HIDE) ;### EndForm ### ;### Add List View Columns START ### ;### Add List View Columns END ### ;### Read Locations START ### ;Read the LOCAL ini file SECTIONS Global $aSections = IniReadSectionNames("Locations.ini") ;If the above succeeded, convert the array to a string with each item separated by a | (pipe) and set the default selected item to $aSections[1] If(Not @Error) Then GUICtrlSetData($Combo1, _ArraytoString($aSections, "|", 1), $aSections[1]) ;### Read Locations END ### ;### Require Admin START ### ;#RequireAdmin ;### Require Admin END ### While 1 $nMsg = GUIGetMsg() ;### Data Validation START ### ;Set Checkbox = ENABLE/DISABLE if not GUICtrlRead($Combo1) = "" AND GUICtrlRead($Combo1) = "Not In List" then if not GUICtrlRead($ManLocInput) = "" Then If not ControlCommand($Form1, "", $Checkbox1, "IsEnabled") Then GUICtrlSetState($Checkbox1, $GUI_ENABLE) Else If ControlCommand($Form1, "", $Checkbox1, "IsEnabled") Then GUICtrlSetState($Checkbox1, $GUI_DISABLE) EndIf Else if GUICtrlRead($Combo1) = "" Then If ControlCommand($Form1, "", $Checkbox1, "IsEnabled") Then GUICtrlSetState($Checkbox1, $GUI_DISABLE) Else If not ControlCommand($Form1, "", $Checkbox1, "IsEnabled") Then GUICtrlSetState($Checkbox1, $GUI_ENABLE) EndIf EndIf ;Set Button = ENABLE/DISABLE If GUICtrlRead($Checkbox1) = 1 Then If Not ControlCommand($Form1, "", $ChangeButton, "IsEnabled") Then GUICtrlSetState($ChangeButton, $GUI_ENABLE) Else If ControlCommand($Form1, "", $ChangeButton, "IsEnabled") Then GUICtrlSetState($ChangeButton, $GUI_DISABLE) EndIf ;### Data Validation END ### Switch $nMsg Case $GUI_EVENT_CLOSE Exit case $Add Case $AddMulti GUISetState(@SW_SHOW, $FormaMultiple) Case $Cancel GUISetState(@SW_HIDE, $FormaMultiple) Case $OK $sText = GUICtrlRead($Edit1) $aText = StringSplit($sText, @CRLF, 1) $bText = _ArrayToString($aText, ";", 1, -1) $cText = StringStripWS($bText, $STR_STRIPALL ) $dText = StringReplace($cText,";;",";") GUICtrlSetData($Input1, $dText) GUICtrlSetData($Edit1, "") GUISetState(@SW_HIDE, $FormaMultiple) For $i = 1 To $aText[0] Next Case $Combo1 ; If the combo is actioned Switch GUICtrlRead($Combo1) Case "Not In List" ; Show the manual input controls GUICtrlSetState($ManLocation, $GUI_SHOW) GUICtrlSetState($ManLocInput, $GUI_SHOW) if ControlCommand($Form1, "", $Checkbox1, "IsChecked") then ControlCommand($Form1, "", $Checkbox1, "Uncheck") GUICtrlSetData($ManLocInput, "") Case Else ; Hide them GUICtrlSetState($ManLocation, $GUI_HIDE) GUICtrlSetState($ManLocInput, $GUI_HIDE) if ControlCommand($Form1, "", $Checkbox1, "IsChecked") then ControlCommand($Form1, "", $Checkbox1, "Uncheck") GUICtrlSetData($ManLocInput, "") EndSwitch Case $ChangeButton ChangeReg() Case $RePromptButton RemoveReg() EndSwitch WEnd Func ChangeReg() $MultiAssets = StringSplit(GUICtrlRead($Input1), ";", 1) For $i = 1 To $MultiAssets[0] ; Ping with a timeout of 250ms. Local $iPing = Ping($MultiAssets[$i], 250) If $iPing Then ; If a value greater than 0 was returned then do the following: if not GUICtrlRead($ManLocInput) = "" Then RegWrite("\\" & $MultiAssets[$i] & "\HKLM\SOFTWARE\Location", "Location", "REG_SZ", "# - " & GUICtrlRead($ManLocInput)) RegWrite("\\" & $MultiAssets[$i] & "\HKLM64\SOFTWARE\Location", "Location", "REG_SZ", "# - " & GUICtrlRead($ManLocInput)) RegWrite("\\" & $MultiAssets[$i] & "\HKLM\SOFTWARE\Location", "LocationLog", "REG_SZ", @MDAY & "/" & @MON & "/" & @YEAR & " " & @HOUR & ":" & @MIN & ":" & @SEC) RegWrite("\\" & $MultiAssets[$i] & "\HKLM64\SOFTWARE\Location", "LocationLog", "REG_SZ", @MDAY & "/" & @MON & "/" & @YEAR & " " & @HOUR & ":" & @MIN & ":" & @SEC) MsgBox($MB_SYSTEMMODAL, "Completed", "Completed") Else If not GUICtrlRead($Input1) = "" Then RegWrite("\\" & $MultiAssets[$i] & "\HKLM\SOFTWARE\Location", "Location", "REG_SZ", GUICtrlRead($Combo1)) RegWrite("\\" & $MultiAssets[$i] & "\HKLM64\SOFTWARE\Location", "Location", "REG_SZ", GUICtrlRead($Combo1)) RegWrite("\\" & $MultiAssets[$i] & "\HKLM\SOFTWARE\Location", "LocationLog", "REG_SZ", @MDAY & "/" & @MON & "/" & @YEAR & " " & @HOUR & ":" & @MIN & ":" & @SEC) RegWrite("\\" & $MultiAssets[$i] & "\HKLM64\SOFTWARE\Location", "LocationLog", "REG_SZ", @MDAY & "/" & @MON & "/" & @YEAR & " " & @HOUR & ":" & @MIN & ":" & @SEC) MsgBox($MB_SYSTEMMODAL, "Completed", $MultiAssets[$i] & @CRLF & " Changed to: " & GUICtrlRead($Combo1)) Else MsgBox($MB_SYSTEMMODAL, "Error", "Please ensure all boxes are completed") EndIf EndIf Else MsgBox($MB_SYSTEMMODAL, "Error", "This asset may be OFFLINE, unable to complete") EndIf Next EndFunc ;==>PingReg Func RemoveReg() $MultiAssets = StringSplit(GUICtrlRead($Input1), ";", 1) For $i = 1 To $MultiAssets[0] ; Ping with a timeout of 250ms. Local $iPing = Ping($MultiAssets[$i], 250) If $iPing Then ; If a value greater than 0 was returned then do the following: if not GUICtrlRead($ManLocInput) = "" Then RegWrite("\\" & $MultiAssets[$i] & "\HKLM\SOFTWARE\Location", "Location", "REG_SZ", "") RegWrite("\\" & $MultiAssets[$i] & "\HKLM64\SOFTWARE\Location", "Location", "REG_SZ", "") RegWrite("\\" & $MultiAssets[$i] & "\HKLM\SOFTWARE\Location", "LocationLog", "REG_SZ", "") RegWrite("\\" & $MultiAssets[$i] & "\HKLM64\SOFTWARE\Location", "LocationLog", "REG_SZ", "") MsgBox($MB_SYSTEMMODAL, "Completed", "Completed") Else If not GUICtrlRead($Input1) = "" Then RegWrite("\\" & $MultiAssets[$i] & "\HKLM\SOFTWARE\Location", "Location", "REG_SZ", "") RegWrite("\\" & $MultiAssets[$i] & "\HKLM64\SOFTWARE\Location", "Location", "REG_SZ", "") RegWrite("\\" & $MultiAssets[$i] & "\HKLM\SOFTWARE\Location", "LocationLog", "REG_SZ", "") RegWrite("\\" & $MultiAssets[$i] & "\HKLM64\SOFTWARE\Location", "LocationLog", "REG_SZ", "") MsgBox($MB_SYSTEMMODAL, "Completed", $MultiAssets[$i] & @CRLF & "CLEARED") Else MsgBox($MB_SYSTEMMODAL, "Error", "Please ensure all boxes are completed") EndIf EndIf Else MsgBox($MB_SYSTEMMODAL, "Error", "This asset may be OFFLINE, unable to complete") EndIf Next EndFunc ;==>PingReg1  
    • anusha
      By anusha
      Hi I have jus started using auto-it . Please correct me if I'm wrong.
      I need to read data from an input in text box and search in excel file and return value in next column of matched cell on GUI.
      I have written below code but i cannot use variable which has data stored. it works only when search string is hard coded.
      Please help out.
       
      Example()
      Func Example()
      Local $GuiMain = GUICreate("EXCEL TEST", 399, 180) ;creates main GUI
      ;~ Local $idOK = GUISetOnEvent($GUI_EVENT_CLOSE, "Close")
      Local $iWidthCell = 70
      Local $idLabel = GUICtrlCreateLabel("PART NUMBER", 10, 30, $iWidthCell,50)
      Local $RUN_1 = GUICtrlCreateButton("OK", 70, 70, 85, 25)
      Local $Input_1 = GUICtrlCreateInput("PART NUMBER", 100, 20, 120, 20)
      Local $sMenutext = GUICtrlRead($Input_1, 1)
      GUISetState(@SW_SHOW, $GuiMain)

          While 1
          $MSG = GUIGetMsg()
          Select
              Case $MSG = $GUI_EVENT_CLOSE
                  Exit
              Case $MSG = $RUN_1
                  Local $oAppl = _Excel_Open()

      Local $sFilePath1 = "D:\Anu_WorkFolder\Components.xlsx"
      Local $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath1, Default, Default, True)
      Local $aResult = _Excel_RangeFind($oWorkbook, $sMenutext , Default, Default, $xlWhole)
    • Nareshm
      By Nareshm
      How to Activate Opened Excel Windows Using Class not Tittle, Because Some time opened defferent excel that have different name.
      I Tried with
      Winactivate ("[CLASS:XLMAIN]") but not working
    • willichan
      By willichan
      Here is another one from my archives that filled a specific need.
       
      Here is the back story if you are interested.
       
      Keep in mind that I wrote this script over 3 years ago, so it may not compile or run directly without some minor tweaks.  It also requires the use of GraphViz to build the graph.
      #cs ---------------------------------------------------------------------------- Project Name: ExcelLinksMapper Description: Analyse an Excel file's links and map them out. Creation Date: 9/26/2014 AutoIt Version: Author: willichan Requires: Graphviz (http://graphviz.org/) #ce ---------------------------------------------------------------------------- Opt("MustDeclareVars", 1) ;0=no, 1=require pre-declare Opt("TrayAutoPause", 0) ;0=no pause, 1=Pause Opt("TrayMenuMode", 0) ;0=append, 1=no default menu, 2=no automatic check, 4=menuitemID not return Opt("TrayIconHide", 0) ;0=show, 1=hide tray icon Global Const $MyName=StringLeft(@ScriptName, StringInStr(@ScriptName,".", 0, -1)-1) ;get just the name portion of the script/exe name Global Const $MyMutex=$MyName & "-82243BEBC30533A3" ;name the mutex for this app Global $SQLloaded = False Global $sDbName = @ScriptDir & "\db2gv.db" ConsoleWrite($sDbName & @crlf) If _MutexExists($MyMutex) Then Exit #include <SQLite.au3> #include <SQLite.dll.au3> #include <file.au3> #include <array.au3> #include <excel.au3> _ConfigInitialize() _Main() Func _ConfigInitialize() OnAutoItExitRegister("_ConfigDestroy") ;initializers here Global $sSQliteDll = _SQLite_Startup() If @error Then MsgBox(0, "SQLite Error", "could not load the DLL") Global $sSQLiteDB = _SQLite_Open($sDbName) If $sSQLiteDB = 0 Then MsgBox(0, "SQLite Error", "could not open the database") $SQLloaded =True __CreateTables() EndFunc ;==>_ConfigInitialize Func _ConfigDestroy() ;destructors here If $SQLloaded Then _SQLite_Close() _SQLite_Shutdown() EndIf EndFunc ;==>_ConfigDestroy Func _MutexExists($sOccurenceName) Local $ERROR_ALREADY_EXISTS = 183, $handle, $lastError $sOccurenceName = StringReplace($sOccurenceName, "\", "") $handle = DllCall("kernel32.dll", "int", "CreateMutex", "int", 0, "long", 1, "str", $sOccurenceName) $lastError = DllCall("kernel32.dll", "int", "GetLastError") Return $lastError[0] = $ERROR_ALREADY_EXISTS EndFunc ;==>_MutexExists Func __CreateTables() _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS nodes;") _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS nodes( name TEXT PRIMARY KEY, fileexists INTEGER);") _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS links;") _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS links( id INTEGER PRIMARY KEY, name1 TEXT, name2 TEXT, weight INTEGER);") EndFunc Func _Main() Local $sInfile, $vResult, $iErrLoop $sInfile = FileOpenDialog("Source File", @WorkingDir, "Excel files (*.xl*)", 1 + 2) If Not FileExists($sInfile) Then MsgBox(0, "Excel Links Mapper Error", "Unable to locate source file") Exit EndIf $vResult = $SQLITE_IOERR $iErrLoop = 5 While $vResult = $SQLITE_IOERR $vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sInfile) & ", 1);") If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then ConsoleWrite($iErrLoop & " tries" & @CRLF & $sInfile & @CRLF) $vResult = $SQLITE_OK EndIf WEnd _GetExcelLinks($sInfile) Global $hOutfile = FileOpen(@ScriptDir & "\" & $MyName & ".gv", 2) If $hOutfile = -1 Then MsgBox(0, $MyName & " ERROR", "Unable to upen file for output") Exit EndIf _WriteHeader() _WriteNodes() _WriteLinks() _WriteFooter() FileClose($hOutfile) _GenerateGraph() ShellExecute(@ScriptDir & '\ExcelLinksMapper.png') EndFunc ;==>_Main Func _GetExcelLinks($strFileName) Local $hQuery, $aCount, $iErrLoop, $vResult ConsoleWrite($strFileName & @CRLF) Local $iLoop, $iExists Local $aLinks Local Const $xlExcelLinks = 1 Local $oExcel = _Excel_Open() Local $ret = _Excel_BookOpen_NoUpdate($oExcel, $strFileName, True, True) Local $err = @error If $err Then If Not IsObj($oExcel) Then ConsoleWrite($ret & " - " & $err & @CRLF) Exit EndIf EndIf $aLinks = $oExcel.ActiveWorkbook.LinkSources($xlExcelLinks) _Excel_BookClose($oExcel, False) _Excel_Close($oExcel, False, True) If IsArray($aLinks) Then If UBound($aLinks) > 0 Then For $iLoop = 0 To UBound($aLinks) - 1 If $aLinks[$iLoop] <> $strFileName Then $iExists = FileExists($aLinks[$iLoop]) ConsoleWrite("DEBUG - Calling WriteNode()") __WriteNode($aLinks[$iLoop], $iExists) ConsoleWrite("DEBUG - Calling WriteLink()") __WriteLink($strFileName, $aLinks[$iLoop]) If $iExists And ($aLinks[$iLoop] <> $strFileName) Then _GetExcelLinks($aLinks[$iLoop]) EndIf Next EndIf EndIf EndFunc ;==>_GetExcelLinks Func __WriteNode($sName, $iExists) Local $iErrLoop = 5 ;Number of attempts to make Local $vResult Do ConsoleWrite("DEBUG - WriteNode()" & @CRLF & " _SQLite_Exec(INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");) - create node entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");") If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then $vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success Until $vResult = $SQLITE_OK EndFunc ;==>__WriteNode Func __WriteLink($sName1, $sName2) Local $iErrLoop = 5 ;Number of attempts to make Local $vResult, $hQuery, $vCount ConsoleWrite("DEBUG - WriteNode()" & @CRLF & " _SQLite_Query(SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";) - lookup link entry" & @CRLF) _SQLite_Query($sSQLiteDB, "SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";", $hQuery) ConsoleWrite("DEBUG - _SQLite_FetchData()" & @CRLF) _SQLite_FetchData($hQuery, $vCount) If UBound($vCount) > 1 Then _ArrayDisplay($vCount) If $SQLITE_OK And UBound($vCount) > 1 Then $vCount = $vCount[1] + 1 Else $vCount = 1 EndIf Do If $vCount = 1 Then ConsoleWrite("DEBUG - _SQLite_Exec() - create link entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "INSERT INTO links ('name1', 'name2', 'weight') VALUES (" & _SQLite_FastEscape($sName1) & ", " & _SQLite_FastEscape($sName2) & ", " & $vCount & ");") Else ConsoleWrite("DEBUG - _SQLite_Exec() - update link entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "UPDATE links SET 'weight'=" & $vCount & " WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2=" & _SQLite_FastEscape($sName2) & ";") EndIf If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then $vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success Until $vResult = $SQLITE_OK EndFunc ;==>__WriteLink ; #FUNCTION# ==================================================================================================================== ; Author ........: SEO <locodarwin at yahoo dot com> ; Modified.......: litlmike, water, GMK, willichan ; =============================================================================================================================== Func _Excel_BookOpen_NoUpdate($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default) If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, @error, 0) If Not FileExists($sFilePath) Then Return SetError(2, 0, 0) If $bReadOnly = Default Then $bReadOnly = False If $bVisible = Default Then $bVisible = True ;; changing the second parameter on the following line to a 0 tells Excel not to update any links. Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, 0, $bReadOnly, Default, $sPassword, $sWritePassword) If @error Then Return SetError(3, @error, 0) $oExcel.Windows($oWorkbook.Name).Visible = $bVisible ; If a read-write workbook was opened read-only then return an error If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(4, 0, $oWorkbook) Return $oWorkbook EndFunc ;==>_Excel_BookOpen_NoUpdate Func _GenerateGraph() RunWait(@ScriptDir & '\GraphViz238\bin\dot.exe -Tpng "' & @ScriptDir & '\' & $MyName & '.gv" -o "' & @ScriptDir & '\ExcelLinksMapper.png"') EndFunc ;==>_GenerateGraph Func _WriteHeader() __OutLine(0, "digraph main {") EndFunc ;==>_WriteHeader Func _WriteNodes() Local $aResult, $iRows, $iColumns, $iRval Local $iLoop, $sStyle __OutLine(1, "// Nodes") $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name, fileexists FROM nodes;", $aResult, $iRows, $iColumns) If @error Then ConsoleWrite("_WriteNodes()" & @CRLF & "_SQLite_GetTable") If $iRval = $SQLITE_OK Then For $iLoop = 1 To $iRows If $aResult[($iLoop * 2) + 2] > 0 Then $sStyle = "normal" Else $sStyle = "missing" EndIf __OutNode($aResult[1 + (2 * $iLoop)], $sStyle) ;; Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg() & @CRLF & "in _WriteNotes() calling _SQLite_GetTable()") Exit EndIf EndFunc ;==>_WriteNodes Func __OutNode($sName, $sStyle = Default) If $sStyle = Default Then $sStyle = "Normal" Switch StringLower($sStyle) Case "missing" __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=red,fontcolor=red,shape=octagon];') Case Else ;"normal", Default __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=black,fontcolor=black,shape=box];') EndSwitch EndFunc ;==>__OutNode Func _WriteLinks() Local $aResult, $iRows, $iColumns, $iRval Local $iLoop, $sStyle, $aCount __OutLine(1, "// Links") $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name1, name2 FROM links ORDER BY name1 ASC, name2 ASC;", $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then For $iLoop = 1 To $iRows __OutLink($aResult[1 + (2 * $iLoop)], $aResult[2 + (2 * $iLoop)]) Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) Exit EndIf EndFunc ;==>_WriteLinks Func __OutLink($sName1, $sName2, $iWeight = 1) __OutLine(1, __MakeName($sName1) & ' -> ' & __MakeName($sName2) & ';') EndFunc ;==>__OutLink Func _WriteFooter() __OutLine(0, "}") EndFunc ;==>_WriteFooter Func __OutLine($iTabs, $sText) Local $iLoop If $iTabs > 0 Then For $iLoop = 1 To $iTabs FileWrite($hOutfile, " ") ;ConsoleWrite(" ") Next EndIf FileWriteLine($hOutfile, $sText) EndFunc ;==>__OutLine Func __MakeName($sText) Local $sNewName = StringReplace($sText, "\", " ") $sNewName = StringReplace($sNewName, "/", " ") $sNewName = StringReplace($sNewName, "'", " ") $sNewName = StringReplace($sNewName, '"', " ") $sNewName = StringReplace($sNewName, ':', " ") $sNewName = StringReplace($sNewName, '.', " ") $sNewName = StringReplace($sNewName, '-', " ") $sNewName = StringReplace($sNewName, '$', " ") $sNewName = StringStripWS($sNewName, 8) Return StringLower($sNewName) EndFunc ;==>__MakeName  
    • Dimmae
      By Dimmae
      Hello,
      at first: i'm new here, so please forgive me my mistakes, and show them to me, just that i can learn to do better in the future.
      Now to my Problem: i have an excel sheet, where i just need some columns for further actions, but i have no idea how to add single columns to a new array.
      I found the following code(the one i just added as a file) from 'water' in this forum, but i wont get how i could add multiple columns into a new array.
      The biggest problem in my situation is that i dont know the count of the rows i need for the array, i just got a fix number of rows, which is 4.
       
      Hope you can help me, and sry again for this 'unlucky illustration'.
       
      btw: how can i add code shown as code here, instead of posting it as a attached file?.
       
       
       
      autoit-select-column.au3
      defects.xlsx