Sign in to follow this  
Followers 0
Elias

Excel (VB) Macro conversion to AutoIt

4 posts in this topic

How to convert Excel macros to AutoIt? (V 3.3.0.0)

One thing I know, is necessary to have the xlxxxx constants

I Attached the file "Excel xl constants_complemet.au3" to this post

;************************************************************

;Enumerated Constants for CoClass "Excel.Application"

;Extracted from "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE"

;************************************************************

#include-once ; 1470 new constants 74 commented exist in Excel.au3

;Constants

Const $xlAll = -4104

;Const $xlAutomatic = -4105

Const $xlBoth = 1

;Const $xlCenter = -4108

Const $xlChecker = 9

Const $xlCircle = 8

Const $xlCorner = 2

Const $xlCrissCross = 16

:

:

With this, the problem now is How to convert the variables of the macros

created by Excel in the form Variable1:=Value1 Variable2:=Value3 Variable3:=Value4 to AutoIt format

I know the result will be something like this .method(value1, value2, etc..)

In the Excel macro:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

In AutoIt:

Const $xlPasteValues = -4163

$oExcel.Selection.PasteSpecial($xlPasteValues,Default,Default,Default)

How is made the magic? Where do I find the information PasteSpecial($Past, $Operation = Default,.....) ???????

Here is some exempls of Macros thas I was unable to convert to AutoIt:

Sub Macro1()

Columns("I:I").Select

Selection.Insert Shift:=xlToRight

Range("I2").Select

ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"

Range("I2").Select

Selection.AutoFill Destination:=Range("I2:I130")

Range("I2:I130").Select

End Sub

Sub Macro2()

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _

, AllowFiltering:=True

End Sub

Sub Macro3()

ChDir "C:\TestDir"

Workbooks.OpenText Filename:="C:\TestDir\test1.xxx", _

Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _

Array(0, 2), Array(25, 4), Array(31, 4), Array(37, 1), Array(40, 9), Array(55, 1), Array(62 _

, 1), Array(85, 1), Array(104, 1), Array(127, 1), Array(155, 1), Array(157, 1), Array(231, 1 _

)), TrailingMinusNumbers:=True

End Sub

Excel_xl_constants_complemet.au3

Share this post


Link to post
Share on other sites



Open the included Excel.au3 UDF file in SciTE and look at the many example in there.

:D


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Found the magic!

In the Excel macro editor

Right Click and select the Object finder (Pesquisador de Objetos)

PS: My Excel is in Portugues

Posted Image

Find "insert", type "insert" and press [enter]

Select Excel Range Insert

There is the result:

Function Insert([shift], [CopyOrigin])

In VB:

Range("I:I").Insert Shift:=xlToRight

In AutoIt:

$oExcel.Activesheet.Range("I:I").Insert($xlToRight)

or

$oExcel.Activesheet.Range("I:I").Insert($xlToRight,Default)

Posted Image

Hope this help's

Share this post


Link to post
Share on other sites

Open the included Excel.au3 UDF file in SciTE and look at the many example in there.

:D

Thanks, the reason to ask how to convert VB to AutoIt is that recording a Macro in Excel is very efficient to make the code

and I did't find the resolution of what I needed in the examples.

And I like to learn how it works, not just copy.

:o

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