Excel VBA to AutoIt Help

[xcaliber13]

Hello everyone,

            I have been struggling for days to understand how to get this excel VBA script to work in AutoIt.  I have looked at the help files and have been reading the forums but just cannot get my head around it.   Here is the VBA script that works great.  The script splits on a string in a cell and than copies to rows.

Sub RedistributeData()
  Dim X As Long, LastRow As Long, A As Range, Table As Range, Data() As String
  Const Delimiter As String = "; "
  Const DelimitedColumn As String = "D"
  Const TableColumns As String = "A:H"
  Const StartRow As Long = 2
  Application.ScreenUpdating = False

Worksheets("Sheet1").Range("A:H").NumberFormat = "General"
  LastRow = Columns(TableColumns).Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  For X = LastRow To StartRow Step -1
    Data = Split(Cells(X, DelimitedColumn), Delimiter)
    If UBound(Data) > 0 Then
      Intersect(Rows(X + 1), Columns(TableColumns)).Resize(UBound(Data)).Insert xlShiftDown
    End If
    If Len(Cells(X, DelimitedColumn)) Then
      Cells(X, DelimitedColumn).Resize(UBound(Data) + 1) = WorksheetFunction.Transpose(Data)
    End If
  Next
  LastRow = Cells(Rows.Count, DelimitedColumn).End(xlUp).Row
  On Error Resume Next
  Set Table = Intersect(Columns(TableColumns), Rows(StartRow).Resize(LastRow - StartRow + 1))
  If Err.Number = 0 Then
    Table.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    Columns(DelimitedColumn).SpecialCells(xlFormulas).Clear
    Table.Value = Table.Value
End If
   On Error GoTo 0
  Application.ScreenUpdating = True
End Sub

This is what I have come up with so far in AutoIt.    Am I even close?   Or am I like way out in left field?

#include <file.au3>
#include <Array.au3>
#include <String.au3>
#include <StringConstants.au3>
#include <Date.au3>
#include <Excel.au3>
$Date = StringFormat("%02u%02u", @MON, @MDAY) & StringRight(@Year, 2)

FileCopy("X:\DEMOS\NR\recon\*.xls", "X:\DEMOS\NR\recon\temp.xls", 1)

Local $oExcel = ObjCreate("Excel.Application")
Const $sType = $xlExcel8
Const $xlToRight = -4161
Global $oExcel = _Excel_Open()
Global $sFile = "X:\DEMOS\NR\recon\temp.xls"
Global $Delimiter = ";"
Global $DelimitedColumn = "D"
Global $TableColumns = "A:H"
Global $oWorkBook = _Excel_BookOpen($oExcel, $sFile, Default, Default, Default, Default)
$oWorkbook.ActiveSheet.Range("1:1").EntireRow.Delete
$oWorkbook.ActiveSheet.Range("A:H").NumberFormat = "General"

 For $i = 8000 To 1 Step -1
    $Data = StringSplit(Cells($i, $DelimitedColumn), $Delimiter)
        If UBound($Data) > 0 Then
            Intersect(Rows($i + 1), Columns($TableColumns)).Resize(UBound($Data)).Insert xlShiftDown
        EndIf
        If Len(Cells($i,$DelimitedColumn))Then
            Cells($i,$DelimitedColumn).Resize(UBound($Data) + 1) = $oWorkbookFunction.Transpose($Data)
        EndIF
     Next
$Table = Intersect(Columns($TableColumns,Rows(1).Resize(8000 - 1)+1))
$Table.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
Columns($DelimitedColumn).SpecialCells(xlFormulas).Clear
$Table.Value = $Table.Value

Thank you for any help

[Jfish]

As a general rule, it would probably be a good idea for you to describe what it is supposed to do and what it is not doing.  That way folks can advise if what your are doing is the most efficient and better assist you.

You should know that when you reference the Excel methods from the API you need to reference the object first like this:  $oExcel.Intersect (arguments).  You could also do With $oExcel .Intersect(args) EndWith and enumerate the methods and args between the With and EndWith.  For this object you need to pass all parameters in parenthesis so when you reference the $xsShiftDown (this should be a variable set to the Excel constant value as follows $xlShiftDown=-4121 prior to using it as an argument since it is not part of the standard Excel constants file that ships with AutoIt) it would be ( $xlShiftDown) - after you initialized it as previously described.   Same goes for $xlCellTypeBlanks etc.

I would also add some debugging.   You should see some error messages in the console for Scite telling you that you are not working with a valid object and other valuable info.  I would suggest breaking the script into pieces and making sure each section works and has debugging to isolate these types of issues.  Also, look for examples in the forum about using Excel's Intersect method  - I know @water has posted on that topic previously.  Same for any other Excel functions you are trying to call.  MSDN is great but will only have VB examples that you need to translate which sort of defeats the purpose given your original question.  However, you can still learn from comparing what they do to what you see as working examples in the forum.  The more Au3 Excel examples you look at in the forum the better - the structure is a bit tricky but arguments are usually either a string reference as in the case of a range (i.e. "A1:C:5") or a variable (i.e. $xlShiftDown).  If the latter, check to see whether or not it is in the includes file that ships with AutoIt "ExcelConstants.au3".  If not, that means no standard Excel UDF function needs it.  Consequently, you need to initialize it to the correct value.  You can research the value of of it on Google or someplace and use it as shown above (i.e. -4121).

Hopefully that is helpful to get you started.