Excel VBS to autoIt

[xcaliber13]

I know this is a simple task, but for the life of me I just cannot successfully convert this excel vba macro to work correctly in autoIt.

Sub match()
Dim i As Long
For i = 2000 To 1 Step -1
     If Range("A" & i).Value = Range("N" & i).Value Then Rows(i).EntireRow.Delete
Next i
End Sub

I have tried excel.range,  created arrays.   All that I have tried either does nothing (no rows deleted) or deleted everything.

What is the correct process to convert vba to autoIt?

Thank you

[JohnOne]

It is customary to show the code you have tried.

[water]

Untested:

Func match()

With $oExcel.Activesheet

For i = 2000 To 1 Step -1

If .Range("A" & i).Value = .Range("N" & i).Value Then .UsedRange.Rows(i).EntireRow.Delete

Next

Endwith

Endfunc

[xcaliber13]

Sorry Johnone  I know I should have post codes tried.   Here is the latest code that is still not working.  No rows are deleted. The text file get pulled in correctly. (Learned that from water last week. Again thank you water) but the rows do not delete.

#include <Excel.au3>
#include <Array.au3>
Local $oExcel = ObjCreate("Excel.Application")
Global $oExcel = _Excel_Open()
Local $sTextFile = "File path file name"
Local $aField1[2] = [1, $xlTextFormat]
Local $aField2[2] = [2, $xlTextFormat]
Local $aField3[2] = [3, $xlTextFormat]
Local $aField4[2] = [4, $xlTextFormat]
Local $aField5[2] = [5, $xlTextFormat]
Local $aField6[2] = [6, $xlTextFormat]
Local $aField7[2] = [7, $xlTextFormat]
Local $aField8[2] = [8, $xlTextFormat]
Local $aField9[2] = [9, $xlTextFormat]
Local $aField10[2] = [10, $xlTextFormat]
Local $aField11[2] = [11, $xlTextFormat]
Local $aField12[2] = [12, $xlTextFormat]
Local $aField13[2] = [13, $xlTextFormat]
Local $aField14[2] = [14, $xlTextFormat]
Local $aField15[2] = [15, $xlTextFormat]
Local $aField16[2] = [16, $xlTextFormat]
Local $aField17[2] = [17, $xlTextFormat]
Local $aField18[2] = [18, $xlTextFormat]
Local $aField19[2] = [19, $xlTextFormat]
Local $aField20[2] = [20, $xlTextFormat]
Local $aField21[2] = [21, $xlTextFormat]
Local $aField22[2] = [22, $xlTextFormat]
Local $aField23[2] = [23, $xlTextFormat]
Local $aField24[2] = [24, $xlTextFormat]
Local $aField25[2] = [25, $xlTextFormat]
Local $aField26[2] = [26, $xlTextFormat]
Local $aField27[2] = [27, $xlTextFormat]
Local $aField28[2] = [28, $xlTextFormat]
Local $aField29[2] = [29, $xlTextFormat]
Local $aField30[2] = [30, $xlTextFormat]
Local $aField31[2] = [31, $xlTextFormat]
Local $aField32[2] = [32, $xlTextFormat]
Local $aField33[2] = [33, $xlTextFormat]
Local $aField34[2] = [34, $xlTextFormat]
Local $aField35[2] = [35, $xlTextFormat]
Local $aField36[2] = [36, $xlTextFormat]
Local $aField37[2] = [37, $xlTextFormat]
Local $aField38[2] = [38, $xlTextFormat]
Local $aField39[2] = [39, $xlTextFormat]
Local $aField40[2] = [40, $xlTextFormat]
Local $aField41[2] = [41, $xlTextFormat]
Local $aField42[2] = [42, $xlTextFormat]
Local $aField43[2] = [43, $xlTextFormat]
Local $aField44[2] = [44, $xlTextFormat]
Local $aFieldInfo[44] = [$aField1, $aField2, $aField3, $aField4, $aField5, $aField6, $aField7, $aField8, $aField9, $aField10, $aField11, $aField12, $aField13, $aField14, $aField15, $aField16, $aField17, $aField18, $aField19, $aField20, $aField21, $aField22, $aField23, $aField24, $aField25, $aField26, $aField27, $aField28, $aField29, $aField30, $aField31, $aField32, $aField33, $aField34, $aField35, $aField36, $aField37, $aField38, $aField39, $aField40,$aField41, $aField42, $aField43, $aField44]
Global $oWorkbook = _Excel_BookOpenText($oExcel, $sTextFile, Default, $xlDelimited, Default, True, ";", $aFieldInfo, ",", ".")
Sleep(5000)
Func match()
 With $oWorkbook.Activesheet
 For $i = 2000 To 1 Step -1
 If $oWorkbook.Range("A" & $i).Value = $oWorkbook.Range("N" & $i).Value Then $oWorkbook.UsedRange.Rows($i).EntireRow.Delete
 Next
 Endwith
 Endfunc

;$oWorkbook.Close
;_Excel_Close($oExcel)

[water]

Delete the following line. It's a duplicate:

Local $oExcel = ObjCreate("Excel.Application")

[xcaliber13]

Water   I deleted that duplicate line and have tried a few different variations of the autoIt code you posted.  Still not able to get the rows to delete. Just in case I will let you know that the vba code in excel does work. Here is the latest try:

Func match()
 With $oWorkbook.Activesheet
 For $i = 2000 To 1 Step -1
 If $oWorkbook.Activesheet.Range("A" & $i).Value = $oWorkbook.Activesheet.Range("N" & $i).Value Then $oWorkbook.Activesheet.UsedRange.Rows($i).EntireRow.Delete
 Next
 Endwith
 Endfunc

No rows get deleted.

[BrewManNH]

Please post your whole script, also use the autoit code tags to do it, it's the blue square with the autoit symbol in it in the reply box.

Your script as posted never calls the function match() so I don't doubt nothing gets deleted.

[xcaliber13]

BrewManNH  Here is the whole script with the latest changes to try to make it work.  Thank you

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>
Global $oExcel = _Excel_Open()
Local $sTextFile = "X:\DEMOS\WMR\DEMOTEMP\SMH_Demographics_030315.txt"
Local $aField1[2] = [1, $xlTextFormat]
Local $aField2[2] = [2, $xlTextFormat]
Local $aField3[2] = [3, $xlTextFormat]
Local $aField4[2] = [4, $xlTextFormat]
Local $aField5[2] = [5, $xlTextFormat]
Local $aField6[2] = [6, $xlTextFormat]
Local $aField7[2] = [7, $xlTextFormat]
Local $aField8[2] = [8, $xlTextFormat]
Local $aField9[2] = [9, $xlTextFormat]
Local $aField10[2] = [10, $xlTextFormat]
Local $aField11[2] = [11, $xlTextFormat]
Local $aField12[2] = [12, $xlTextFormat]
Local $aField13[2] = [13, $xlTextFormat]
Local $aField14[2] = [14, $xlTextFormat]
Local $aField15[2] = [15, $xlTextFormat]
Local $aField16[2] = [16, $xlTextFormat]
Local $aField17[2] = [17, $xlTextFormat]
Local $aField18[2] = [18, $xlTextFormat]
Local $aField19[2] = [19, $xlTextFormat]
Local $aField20[2] = [20, $xlTextFormat]
Local $aField21[2] = [21, $xlTextFormat]
Local $aField22[2] = [22, $xlTextFormat]
Local $aField23[2] = [23, $xlTextFormat]
Local $aField24[2] = [24, $xlTextFormat]
Local $aField25[2] = [25, $xlTextFormat]
Local $aField26[2] = [26, $xlTextFormat]
Local $aField27[2] = [27, $xlTextFormat]
Local $aField28[2] = [28, $xlTextFormat]
Local $aField29[2] = [29, $xlTextFormat]
Local $aField30[2] = [30, $xlTextFormat]
Local $aField31[2] = [31, $xlTextFormat]
Local $aField32[2] = [32, $xlTextFormat]
Local $aField33[2] = [33, $xlTextFormat]
Local $aField34[2] = [34, $xlTextFormat]
Local $aField35[2] = [35, $xlTextFormat]
Local $aField36[2] = [36, $xlTextFormat]
Local $aField37[2] = [37, $xlTextFormat]
Local $aField38[2] = [38, $xlTextFormat]
Local $aField39[2] = [39, $xlTextFormat]
Local $aField40[2] = [40, $xlTextFormat]
Local $aField41[2] = [41, $xlTextFormat]
Local $aField42[2] = [42, $xlTextFormat]
Local $aField43[2] = [43, $xlTextFormat]
Local $aField44[2] = [44, $xlTextFormat]
Local $aFieldInfo[44] = [$aField1, $aField2, $aField3, $aField4, $aField5, $aField6, $aField7, $aField8, $aField9, $aField10, $aField11, $aField12, $aField13, $aField14, $aField15, $aField16, $aField17, $aField18, $aField19, $aField20, $aField21, $aField22, $aField23, $aField24, $aField25, $aField26, $aField27, $aField28, $aField29, $aField30, $aField31, $aField32, $aField33, $aField34, $aField35, $aField36, $aField37, $aField38, $aField39, $aField40,$aField41, $aField42, $aField43, $aField44]
Global $oWorkbook = _Excel_BookOpenText($oExcel, $sTextFile, Default, $xlDelimited, Default, True, ";", $aFieldInfo, ",", ".")
Sleep(5000)
Func match()
 With $oWorkbook.Activesheet
 For $i = 2000 To 1 Step -1
 If .Activesheet.Range("A" & $i).Value = .Activesheet.Range("N" & $i).Value Then .Activesheet.Range.EntireRow.Delete
 Next
 Endwith
 Endfunc

;$oWorkbook.Close
;_Excel_Close($oExcel)

[BrewManNH]

You're still not calling the function Match anywhere in your script. If you don't use the function, it's not going to do anything.

[xcaliber13]

So after a few more hours of trying to figure out how to call the function...... I threw in the towel and just wrote a VBS code that works.  But Thank you for all the help.

[BrewManNH]

Have you ever used a function in VBScript, or a Sub? Calling a function in AutoIt is pretty much the same thing. You use the function _ExcelBookOpen(), which would be the exact same way to call the match() function.

You give up far too easily if something as simple as calling a function is too hard to figure out, especially when practically every single example in the help file uses an Example function.