Sign in to follow this  
Followers 0
NickT

Array to Excel

23 posts in this topic

I believe it's time to consult the experts on what I am trying to do...I am trying to take a large text file and write it to an Excel spreadsheet. After 2 days of trial and error I finally got it to work. What I would like help with is on making it faster. In my opinion it is taking WAY too long to write these values to the worksheet.

SCRIPT:

test.au3

TEXT FILE:

aptsource.txt

Share this post


Link to post
Share on other sites



Didn't have a look at your script in detail. You could try my UDF (Attention! Alpha version!) and use the _Excel_RangeWrite function to write an array to a worksheet. Or use the _Excel_BookOpenText function to directly read the file into a sheet.

That's much faster!


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Okay, I tried using the _Excel_BookOpenText function with no luck. I keep getting a failed object error on it.

Share this post


Link to post
Share on other sites

Can you post your code?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

#include <Excel.au3>

#include <File.au3>

#include <String.au3>

#include <Array.au3>

Global $ext,$slnum = "-1"

Func write($book,$val,$row,$x,$y,$z)

$zVAL = _ExcelReadCell($book,$row - 1,3)

If _ExcelReadCell($book,$row,3) = $zVAL Then

_ExcelWriteCell($book,$slnum,$row,10)

Else

$slnum += 1

_ExcelWriteCell($book,$slnum,$row,10)

EndIf

_ExcelWriteCell($book,$x,$row,7)

_ExcelWriteCell($book,$y,$row,8)

_ExcelWriteCell($book,$z,$row,9)

If Number(Round(($row - 2) / $val * 100)) > "0" Then

$per = Round(($row - 2) / $val * 100)

ProgressSet($per,$per & " Percent complete")

EndIf

EndFunc

Func parse($file,$tmpfile,$book,$lcount)

ProgressOn("AptSource Conversion","","",1,1)

Local $line

If Not StringInStr(FileRead($file),"GOTO",1) Then

MsgBox(0,"ERROR",'This is not an "aptsource" file.')

ex($book,$tmpfile)

EndIf

_ExcelWriteCell($book,"in machine datum : xm=-zb ; ym=-xb ; zm=yb with rotation (0.223deg)",1,1)

_ExcelWriteCell($book,"Xc",2,1)

_ExcelWriteCell($book,"Yc",2,2)

_ExcelWriteCell($book,"Zc",2,3)

_ExcelWriteCell($book,"i",2,4)

_ExcelWriteCell($book,"j",2,5)

_ExcelWriteCell($book,"k",2,6)

_ExcelWriteCell($book,"x+i",2,7)

_ExcelWriteCell($book,"y+j",2,8)

_ExcelWriteCell($book,"z+k",2,9)

_ExcelWriteCell($book,"scan line " & @CRLF & "number",2,10)

_ExcelHorizontalAlignSet($book,1,1,1,1,"center")

$book.Range($book.Cells(1,1),$book.Cells(1,10)).MergeCells=True

$book.Columns.NumberFormat="@"

$read = StringReplace(FileRead($file),"GOTO","",0,1);_StringStripChr(FileRead($file),"GOTO/ ",8)

$read = StringReplace($read," ","",0,1)

$read = FileWrite($tmpfile,StringReplace($read,"/","",0,1))

For $i = 1 To $lcount

$read = FileReadLine($tmpfile,$i)

$read = _StringExplode($read,",")

$x = StringReplace($read[0] + $read[3],".",",")

$y = StringReplace($read[1] + $read[4],".",",")

$z = StringReplace($read[2] + $read[5],".",",")

$read = StringReplace(_ArrayToString($read),".",",")

$read = _StringExplode($read,"|")

_ExcelWriteArray($book, $i + 2, 1, $read)

write($book,$lcount,$i + 2,$x,$y,$z)

Next

$book.columns.autofit

$folder = FileSaveDialog("Save",@ScriptDir,"Excel (*.xlsx)",18)

$book.ActiveWorkbook.SaveAs($folder);,"","",False,False,1,2,False)

MsgBox(0,"",@error)

ex($book,$tmpfile)

EndFunc

Func lcount($file)

If $file = "" Then Exit

Local $line

_FileReadToArray($file,$line)

parse($file,_TempFile(),_ExcelBookNew(1),$line[0])

EndFunc

Func ex($book,$tmpfile)

ProgressOff()

_ExcelBookClose($book,0)

FileDelete($tmpfile)

EndFunc

lcount(FileOpenDialog("Open","","All(*.*)",3))

Edited by NickT

Share this post


Link to post
Share on other sites

Sorry, my bad.

I didn't mean the original code which you already posted in #1 but the code where _Excel_BookOpenText didn't work.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Apologies for taking so long, been busy. Here is my code with _Excel_BookOpenText not working.

#include <Excel.au3>
#include <File.au3>
#include <String.au3>
#include <Array.au3>

Global $ext,$slnum = "-1"

Func write($book,$val,$row,$x,$y,$z,$time)
Local $per,$min[1]
_ExcelWriteCell($book,$x,$row,7)
_ExcelWriteCell($book,$y,$row,8)
_ExcelWriteCell($book,$z,$row,9)
$zVAL = _ExcelReadCell($book,$row - 1,3)
If _ExcelReadCell($book,$row,3) = $zVAL Then
_ExcelWriteCell($book,$slnum,$row,10)
Else
$slnum += 1
_ExcelWriteCell($book,$slnum,$row,10)
EndIf
If Number(Round(($row - 2) / $val * 100)) > "0" Then
$per = Round(($row - 2) / $val * 100)
; ProgressSet($per,$per & " Percent complete" & @CRLF & @CRLF & Round(TimerDiff($time) / 1000))
EndIf
$dif = Round(TimerDiff($time) / 1000)
If $dif >= "60" Then
$min[0] = $dif / 60
If StringInStr($min[0],".") Then $min[0] = _StringBetween($min,"",".")
Else
$min[0] = 0
EndIf
$sec = $dif / 60
$sec = _StringBetween($sec,".","")
$sec = _StringInsert(_ArrayToString($sec),".",0)
$sec = Round($sec * 60)
ProgressSet($per,$per & " Percent complete" & @CRLF & @CRLF & "Time elapsed: " & $min[0] & " minutes and " & $sec & " seconds")
EndFunc

Func parse($file,$tmpfile,$book,$lcount)
$time = TimerInit()
ProgressOn("AptSource Conversion","","",1,1,18)
Local $line
If Not StringInStr(FileRead($file),"GOTO",1) Then
MsgBox(0,"ERROR",'This is not an "aptsource" file.')
ex($book,$tmpfile)
EndIf
_ExcelWriteCell($book,"in machine datum : xm=-zb ; ym=-xb ; zm=yb with rotation (0.223deg)",1,1)
_ExcelWriteCell($book,"Xc",2,1)
_ExcelWriteCell($book,"Yc",2,2)
_ExcelWriteCell($book,"Zc",2,3)
_ExcelWriteCell($book,"i",2,4)
_ExcelWriteCell($book,"j",2,5)
_ExcelWriteCell($book,"k",2,6)
_ExcelWriteCell($book,"x+i",2,7)
_ExcelWriteCell($book,"y+j",2,8)
_ExcelWriteCell($book,"z+k",2,9)
_ExcelWriteCell($book,"scan line " & @CRLF & "number",2,10)
_ExcelHorizontalAlignSet($book,1,1,1,1,"center")
$book.Range($book.Cells(1,1),$book.Cells(1,10)).MergeCells=True
$book.Columns.NumberFormat="@"
$read = StringReplace(FileRead($file),"GOTO","",0,1)
$read = StringReplace($read," ","",0,1)
$read = FileWrite($tmpfile,StringReplace($read,"/","",0,1))
_Excel_BookOpenText($book,$tmpfile,3,1,Default,Default,",",2,Default,Default,Default)
; For $i = 1 To $lcount
; $read = FileReadLine($tmpfile,$i)
; If @error Then
; MsgBox(0,"",$read & @CRLF & $i & @CRLF & $lcount)
; ExitLoop
; EndIf
; $read = _StringExplode($read,",")
; $x = StringReplace($read[0] + $read[3],".",",")
; $y = StringReplace($read[1] + $read[4],".",",")
; $z = StringReplace($read[2] + $read[5],".",",")
; $read = StringReplace(_ArrayToString($read),".",",")
; $read = _StringExplode($read,"|")
; _ExcelWriteArray($book, $i + 2, 1, $read)
; write($book,$lcount,$i + 2,$x,$y,$z,$time)
; Next
$book.columns.autofit
$folder = FileSaveDialog("Save",@ScriptDir,"Excel (*.xlsx)",18)
$book.ActiveWorkbook.SaveAs($folder)
ex($book,$tmpfile)
EndFunc

Func lcount($file)
If $file = "" Then Exit
Local $line
_FileReadToArray($file,$line)
parse($file,_TempFile(),_ExcelBookNew(0),$line[0])
EndFunc

Func ex($book,$tmpfile)
ProgressOff()
_ExcelBookClose($book,0)
FileDelete($tmpfile)
EndFunc

lcount(FileOpenDialog("Open","","All(*.*)",3))
Apologies for taking so long, been busy. Here is my code with _Excel_BookOpenText not working.

Share this post


Link to post
Share on other sites

You can't combine the Excel UDF that comes with AutoIt and my rewrite of the Excel UDF.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

I've tried it both ways, using your rewrite and $book.OpenText method.

-- Note that I have not only tried $book.OpenText but also $book.ActiveWorkbook.OpenText and $book.Workbooks.OpenText.

Share this post


Link to post
Share on other sites

If you run the _Excel_BookOpenText.au3 example of my rewritten Excel UDF. Does it work?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Sweet! That works like I wanted it too! Is there any way to attach the "OpenText" book that is created to the previously created workbook instead of having two seperate books? I've looked around _ExcelBookAttach but it doesn't seem to be what I'm looking for. Of course I'm probably wrong.

Share this post


Link to post
Share on other sites

Not at the moment. The Excel method OpenText is only available for workbooks.

What should be possible is to copy the worksheet to another book.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

I suppose what I could do is close the unused book and set my variable to the new book. Thank you for all of your help. It is greatly appreciated!

Share this post


Link to post
Share on other sites

I will test how to copy a worksheet from one workbook to another tomorrow.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

How to copy a worksheet from one workbook to another:

#include <Excel Rewrite.au3>
$oExcel = _Excel_Open()
$oBook1 = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel1.xls")
$oSheet1 = $oBook1.Sheets(1)
$oBook2 = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel2.xls")
MsgBox(0, "", "Copying sheet1 of workbook1 before sheet1 of workbook2")
$oSheet1.Copy($oBook2.Sheets(1))
Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

I finally got the script to work. Thanks for your help. I have another question that pertains to the same script but this is more of a "want to know how" sort of deal. I'm wondering what makes this work:

dim $Array1[2]=[0,2]

dim $Array2[2]=[7,2]

dim $Array3[2]=[41,2]

dim $Array4[2]=[47,2]

dim $Array5[2]=[61,1]

dim $Array6[2]=[98,1]

dim $Array7[2]=[99,1]

dim $ArrayComplet[7]=[$Array1,$Array2,$Array3,$Array4,$Array5,$Array6,$Array7]

When I run _ArrayDisplay($ArrayComplet) I get 7 rows, 1 column, and no values. I use this in my script but I just want to know how this is working. This came from

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Icon=..\..\..\..\Program Files (x86)\AutoIt3\Icons\au3.ico
#AutoIt3Wrapper_Outfile=AptSource_Converter.exe
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include
#include
#include

Func parse($file,$tmpfile,$book,$lcount)
Dim $arr0[2] = [0,2]
Dim $arr1[2] = [1,2]
Dim $arr2[2] = [2,2]
Dim $arr3[2] = [3,2]
Dim $arr4[2] = [4,2]
Dim $arr5[2] = [5,2]
Dim $arr6[2] = [6,2]
Dim $arr7[2] = [7,2]
Dim $arr8[2] = [8,2]
Dim $arr9[2] = [9,2]
Dim $array[10] = [$arr0,$arr1,$arr2,$arr3,$arr4,$arr5,$arr6,$arr7,$arr8,$arr9]
$tmpfile2 = _TempFile()
If Not StringInStr(FileRead($file),"GOTO",1) Then
MsgBox(0,"ERROR",'This is not an "aptsource" file.')
ex($book,$tmpfile,$tmpfile2,"")
EndIf
ProgressOn("Converting","","",1,1,18)
$read = StringReplace(FileRead($file),"GOTO","",0,1)
$read = StringReplace($read," ","",0,1)
$read = FileWrite($tmpfile,StringReplace($read,"/","",0,1))
$book.Application.DisplayAlerts=0
$book.Workbooks.OpenText($tmpfile,Default,1,Default,1,False,False,False,True,False,False,"",$array, Default,Default,Default,True)
_ExcelWriteCell($book,0,1,10)
$book.Range($book.Cells(1,7),$book.Cells($lcount,9)).Formula="=A1:A" & $lcount & "+D1:D" & $lcount
$book.Range($book.Cells(2,10),$book.Cells($lcount,10)).Formula="=IF(C2=C1,J1,J1+1)"
MsgBox(0,"","")
$book.ActiveWorkbook.SaveAs($tmpfile,20,Default,Default,False,False,1,2)
_ExcelBookClose($book,0)
ProgressSet(25,"25 percent complete" & @CRLF & @CRLF & $file)
$read = StringReplace(FileRead($tmpfile),".",",")
FileWrite($tmpfile2,$read)
ProgressSet(50,"50 percent complete" & @CRLF & @CRLF & $file)
$book = _ExcelBookNew(0)
$book.Application.DisplayAlerts=0
$book.Application.ErrorCheckingOptions.NumberAsText=False
$book.Workbooks.OpenText($tmpfile2,Default,1,Default,1,False,True,False,False,False,False,"",$array, Default,Default,Default,True)
$book.ActiveSheet.Rows(1).Insert()
$book.ActiveSheet.Rows(1).Insert()
ProgressSet(75,"75 percent complete" & @CRLF & @CRLF & $file)
_ExcelWriteCell($book,"in machine datum : xm=-zb ; ym=-xb ; zm=yb with rotation (0.223deg)",1,1)
_ExcelWriteCell($book,"Xc",2,1)
_ExcelWriteCell($book,"Yc",2,2)
_ExcelWriteCell($book,"Zc",2,3)
_ExcelWriteCell($book,"i",2,4)
_ExcelWriteCell($book,"j",2,5)
_ExcelWriteCell($book,"k",2,6)
_ExcelWriteCell($book,"x+i",2,7)
_ExcelWriteCell($book,"y+j",2,8)
_ExcelWriteCell($book,"z+k",2,9)
_ExcelWriteCell($book,"scan line " & @CRLF & "number",2,10)
_ExcelHorizontalAlignSet($book,1,1,1,1,"center")
_ExcelHorizontalAlignSet($book,3,1,$lcount + 2,10,"right")
$book.Range($book.Cells(1,1),$book.Cells(1,10)).MergeCells=True
$book.Columns.AutoFit
ProgressSet(100,"100 percent complete" & @CRLF & @CRLF & $file)
$folder = FileSaveDialog("Save",@ScriptDir,"Excel (*.xlsx)",18)
ProgressOff()
If Not $folder Then ex($book,$tmpfile,$tmpfile2,"")
$book.ActiveWorkbook.SaveAs($folder,51,Default,Default,False,False,1,2)
ex($book,$tmpfile,$tmpfile2,$folder)
EndFunc

Func lcount($file)
If $file = "" Then Exit
Local $line
_FileReadToArray($file,$line)
For $i = UBound($line) - 1 To 0 Step -1
If $line[$i] = "" Then _ArrayDelete($line,$i)
Next
$line = UBound($line) - 1
parse($file,_TempFile(),_ExcelBookNew(1),$line)
EndFunc

Func ex($book,$tmpfile,$tmpfile2,$folder)
_ExcelBookClose($book,0)
Do
FileDelete($tmpfile)
FileDelete($tmpfile2)
Until Not FileExists($tmpfile) And Not FileExists($tmpfile2)
If Not $folder = "" Then MsgBox(0,"Conversion",'Apt Source conversion complete!!' & @CRLF & @CRLF & 'New file is located at: ' & $folder & ".xlsx")
Exit
EndFunc

lcount(FileOpenDialog("Open","","All(*.*)",3))
Edited by NickT

Share this post


Link to post
Share on other sites

Array in Array is not recommended. See the end of this page.

Function _ArrayDisplay doesn't know how to handle arrays in arrays.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Okay, that's understandable. Is there a way to do this without an array in an array?

Share this post


Link to post
Share on other sites

Use a two-dimensional array. Something like this:

#include <Array.au3>
Global $Array[7][2]=[[0,2],[7,2],[41,2],[47,2],[61,1],[98,1],[99,1]]
_ArrayDisplay($Array)

BTW: Don't use "DIM", use "Global"


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

I tried that in my script but it doesn't work. I've also tried a 3-dimensional array as well.

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  
Followers 0

  • 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  
    • MrCheese
      By MrCheese
      Hey Guys,
       
      So, the functions work, where primarygui() accurately determines the evaluation of the status of the checkboxes - the msgbox picks this up.
       
      However, later on, when we re-enter a 'for $i = 0 to ubound($checkbox)' loop, then in the 'batchinitial' function it doesn't picked up that the status&$i = 1, so it jumps out, then within the While 1 loop, it exits the loop in the first row, again because the status& $i= 0
      The "assign" line within the primarygui funtion, is this only a local assignment? if so, how can I make it cross function?
       
      Thank in adv for your help
       
       
      Func excelsheetlist() $i = 0 Global $aWorkSheets = _Excel_SheetList($oWorkbook1) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetList Example 1", "Error listing Worksheets." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;_ArrayDisplay($aWorkSheets, "Excel UDF: _Excel_SheetList Example 1") ;_ArrayDisplay($aWorkSheets, "Array") Global $iRows = UBound($aWorkSheets, $UBOUND_ROWS) ; Total number of rows. In this example it will be 10. Global $iCols = UBound($aWorkSheets, $UBOUND_COLUMNS) ; Total number of columns. In this example it will be 20. Global $iDimension = UBound($aWorkSheets, $UBOUND_DIMENSIONS) ; The dimension of the array e.g. 1/2/3 dimensional. MsgBox($MB_SYSTEMMODAL, "", "The array is a " & $iDimension & " dimensional array with " & _ $iRows & " row(s) & " & $iCols & " column(s).") Dim $checkbox[$iRows] EndFunc ;==>excelsheetlist Func primarygui() ; Create a GUI with various controls. Local $hGUI = GUICreate("Script Controller", 300, ($iRows * 24)) ; Create a checkbox control. ;Local $idCheckbox = GUICtrlCreateCheckbox("Standard Checkbox", 10, 10, 185, 25) Local $Button2 = GUICtrlCreateButton("Close", 210, 200, 85, 25) Local $Button3 = GUICtrlCreateButton("Run", 210, 170, 85, 25) Local $Button1 = GUICtrlCreateButton("Discharge", 210, 140, 85, 25) For $i = 0 To UBound($checkbox) - 1 $checkbox[$i] = GUICtrlCreateCheckbox($aWorkSheets[$i][0], 8, 0 + ($i * 24)) ;, 81, 17) Next ; Display the GUI. GUISetState(@SW_SHOW, $hGUI) ; Loop until the user exits. While 1 $nMsg = GUIGetMsg() Select Case $nMsg = $GUI_EVENT_CLOSE Exit Case $nMsg = $Button2 ;Close Exit Case $nMsg = $Button1 MsgBox(0, "Discharge Button not configured", "Now Exiting") Exit Case $nMsg = $Button3 ;Run $fSelection = False For $i = 0 To UBound($checkbox) - 1 If BitAND(GUICtrlRead($checkbox[$i]), $GUI_CHECKED) Then $fSelection = True ExitLoop EndIf Next If $fSelection Then For $i = 0 To UBound($checkbox) - 1 Assign("status" & $i, GUICtrlRead($checkbox[$i])) Next $batchcount = 0 For $i = 0 To UBound($checkbox) - 1 If Eval("status" & $i) = 1 Then $batchcount = $batchcount + 1 ;Call ("o" & $i & "copy") ; if you want to call the functions directly, remove ; before the call and comment or delete the following DirCopy statement MsgBox(0, "Checking", "Checking that: " & $checkbox[$i] & " no, with title: " & $aWorkSheets[$i][0] & " was selected, Batch count: " & $batchcount) ; if you need only the DirCopy EndIf Next ExitLoop Else MsgBox(48, 'No Items Selected', 'You have not selected any Patients to Load, Please select from the list') EndIf EndSelect WEnd ; Delete the previous GUI and all controls. GUIDelete($hGUI) EndFunc ;==>primarygui Func _IsChecked($idControlID) Return BitAND(GUICtrlRead($idControlID), $GUI_CHECKED) = $GUI_CHECKED EndFunc ;==>_IsChecked Func batchinitial() If Eval("status" & $i) = 1 Then debugbox() $oWorkbook1.Sheets(1).Activate $bigloop = $bigloop + 1 $sheet = $aWorkSheets[$i][0] $oWorkbook1.Sheets($sheet).Activate debugbox() EndIf EndFunc ;==>batchinitial OpenExcel() excelsheetlist() primarygui() For $i = 0 To UBound($checkbox) - 1 batchinitial() While 1 If Eval("status" & $i) = 0 Then ExitLoop ;all the rest of my script loops etc WEnd Next  
    • FMS
      By FMS
      Hello,
      I'm building whit mine limited coding know-how a AI in Autoit....
      Just because..... why not
      It's not doing what i tought it would do.
      I hope somebody could help me whit this script?
      so far :
      -building an learning grid --> AI needs to guess the label in the grin whit only the X and Y value.
      -Building an array filled whit random values as weights.
      -quess the label
      -learn - if quess not the same as the label go change the weights

      At this point (the changing of the weights) I've some strange result and hope somebody could point me in the right direction
      I think that the problem is in the formula for changing the weights.

      PS. I'm also open for good coding practice   I'm learning coding as i go
      #AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7 #include <MsgBoxConstants.au3> #include <Array.au3> build_grid_list() build_neurons() _ArrayDisplay($gridlist,"$gridlist 0","",32) _ArrayDisplay($neurons,"$neurons 0","",32) For $i = 1 To UBound($gridlist,1) - 1 If $gridlist[$i][3] = 1 Then go_learn($neurons[1][0],$gridlist[$i][0]) EndIf Next _ArrayDisplay($gridlist,"$gridlist 1","",32) _ArrayDisplay($neurons,"q$neurons 1","",32) Func go_learn($neuron_ID,$gridlist_ID) Local $test = guess($gridlist_ID , $neuron_ID ) For $i = 1 To 5000 If $test <> $gridlist[$gridlist_ID][4] Then change_wieght($neuron_ID , $gridlist_ID ) $test = guess($gridlist_ID , $neuron_ID ) Else ExitLoop EndIf Next EndFunc Func guess($gridlist_ID , $neuron_ID = 1 ,$raw = 0);, $loop = 0 ) Local $temp = 0 ;~ For $i = 1 To ubound($neurons,1) -1 ; input1 * W1 + input2 * W2 + Bias(1) * W3 $temp = $gridlist[$gridlist_ID][1] * $neurons[$neuron_ID][1] + $gridlist[$gridlist_ID][2] * $neurons[$neuron_ID][2] + 1 * $neurons[$neuron_ID][3] ;activate (sign) If $temp >= 0 Then $neurons[$neuron_ID][6] = 1 Else $neurons[$neuron_ID][6] = -1 EndIf $gridlist[$gridlist_ID][5] = $neurons[$neuron_ID][6] $gridlist[$gridlist_ID][6] = $gridlist[$gridlist_ID][6] + 1 $neurons[$neuron_ID][9] = $neurons[$neuron_ID][9] + 1 If $raw = 0 Then Return $neurons[$neuron_ID][6] Else Return $temp EndIf EndFunc Func build_neurons($needed_neurons = 10 ) Global $neurons[$needed_neurons + 1][10] $neurons[0][0] = "id" $neurons[0][1] = "wieght1" $neurons[0][2] = "wieght2" $neurons[0][3] = "wieght3" $neurons[0][4] = "input1" $neurons[0][5] = "input2" $neurons[0][6] = "output" $neurons[0][7] = "tweak_counter" $neurons[0][8] = "not_tweak_counter" $neurons[0][9] = "quess_counter" Local $temp = 1 For $i = 1 To $needed_neurons; -1 $neurons[$i][0] = $i ;id Do ;zero_check output wieght1 $temp = Random(-1 , 1) Until $temp <> 0 $neurons[$i][1] = $temp ;wieght1 Do ;zero_check output wieght2 $temp = Random(-1 , 1) Until $temp <> 0 $neurons[$i][2] = $temp ;wieght2 Do ;zero_check output wieght3 $temp = Random(-1 , 1) Until $temp <> 0 $neurons[$i][3] = $temp ;wieght3 $neurons[$i][4] = 0 ;input1 $neurons[$i][5] = 0 ;input2 Do ;zero_check output $temp = Random(-1 , 1 , 1) Until $temp <> 0 $neurons[$i][6] = $temp ;output +1 / -1 ;~ $neurons[$i][6] = 0 ;output $neurons[$i][7] = 0 ;tweak_counter $neurons[$i][8] = 0 ;not_tweak_counter $neurons[$i][9] = 0 ;quess_counter Next EndFunc Func build_grid_list($grid_x = 10 ,$grid_y = 10 ) Global $gridlist[($grid_x * $grid_y) + 1 ][7] Local $counter = 1 $gridlist[0][0] = "ID" $gridlist[0][1] = "X" $gridlist[0][2] = "Y" $gridlist[0][3] = "Active" $gridlist[0][4] = "Label" $gridlist[0][5] = "quessed" $gridlist[0][6] = "quessed_counter" For $x = 0 to $grid_x - 1 For $y = 0 to $grid_y - 1 $gridlist[$counter][0] = $counter $gridlist[$counter][1] = $x $gridlist[$counter][2] = $y If Random(-1 , 1) >= 0 Then $gridlist[$counter][3] = 0 Else $gridlist[$counter][3] = 1 EndIf If $x > $y Then $gridlist[$counter][4] = 1 Else $gridlist[$counter][4] = -1 EndIf $gridlist[$counter][5] = -99 $gridlist[$counter][6] = 0 $counter = $counter + 1 Next Next EndFunc Func change_wieght($neuron_id , $grid_id );, $W1 , $W2 ) ;W1 = W1 + ^W1 (some change in W1) ;^W = err(known) * input ($neurons[$id][3] = "output") * learningrate ;$neurons[$id][1] = $neurons[$id][1] + "wieght1" Local $iReturn = False ; Desired | Quess | Error ; -1 -1 0 ; -1 +1 -2 ; +1 -1 +2 ; +1 +1 0 Local $error = $gridlist[$grid_id][4] - $neurons[$neuron_id][6] If $error <> 0 Then Local $learningrate = 0.1 Local $str_len1 = StringLen($neurons[$neuron_id][1]) Local $str_len2 = StringLen($neurons[$neuron_id][2]) Local $str_len3 = StringLen($neurons[$neuron_id][3]) Local $dif_weights1 = $error * $neurons[$neuron_id][1] * $learningrate Local $dif_weights2 = $error * $neurons[$neuron_id][2] * $learningrate Local $dif_weights3 = $error * $neurons[$neuron_id][3] * $learningrate Local $verschil1 = StringLeft($dif_weights1,$str_len1) Local $verschil2 = StringLeft($dif_weights2,$str_len2) Local $verschil3 = StringLeft($dif_weights3,$str_len3) Local $new_wieght1 = $neurons[$neuron_id][1] + $verschil1 Local $new_wieght2 = $neurons[$neuron_id][2] + $verschil2 Local $new_wieght3 = $neurons[$neuron_id][3] + $verschil3 $neurons[$neuron_id][1] = StringLeft($new_wieght1,$str_len1) $neurons[$neuron_id][2] = StringLeft($new_wieght2,$str_len2) $neurons[$neuron_id][3] = StringLeft($new_wieght3,$str_len3) $neurons[$neuron_id][7] = $neurons[$neuron_id][7] + 1 ;"counter" $iReturn = False Else $neurons[$neuron_id][8] = $neurons[$neuron_id][8] + 1 ;"not counter" $iReturn = True EndIf Return $iReturn EndFunc thanks in advanced.
       
    • 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