Jump to content

Excel (VB) Macro conversion to AutoIt


Recommended Posts

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

Link to comment
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

Link to comment
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

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...