xcaliber13

Excel VBS to autoIt

11 posts in this topic

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

Share this post


Link to post
Share on other sites



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


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

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)

Share this post


Link to post
Share on other sites

Delete the following line. It's a duplicate:

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


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

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.

Share this post


Link to post
Share on other sites

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.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

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

#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)

Share this post


Link to post
Share on other sites

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.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

1 person likes this

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

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