How to translate VBA code to AutoIt script?

[forum0member]

Hello,
 
I have a VBA Code, which I need to translate to AutoIt Script.

 

Sub moveto()
Dim lngZ(1 To 2) As Long
With Workbooks("result.csv").Worksheets(1)
lngZ(2) = .Cells(.Rows.Count, 2).End(xlUp).Row
lngZ(1) = .Cells(.Rows.Count, 1).End(xlUp).Row - lngZ(2)
If lngZ(1) Then
Workbooks("data.csv").Worksheets(1).Cells(2, 1).Resize(lngZ(1), 12).Copy .Cells(lngZ(2) + 1, 2)
Workbooks("data.csv").Worksheets(1).Cells(2, 1).Resize(lngZ(1), 12).Delete xlUp
End If
End With
End Sub

With this Code I move data from data.csv to result.csv

How do I translate the VBA Code to AutoIt, so I don't need VBA?

I tried with the following code, but it is so miserable that it never could work..

#include <Excel.au3>
 #include <MsgBoxConstants.au3>
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $odata = _Excel_BookOpen($oExcel, @ScriptDir & "\data.csv")
Local $oresult = _Excel_BookOpen($oExcel, @ScriptDir & "\result.csv")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Workbook '" & $sWorkbook & "' has been opened successfully." & @CRLF & @CRLF & "Creation Date: " & $oWorkbook.BuiltinDocumentProperties("Creation Date").Value)


Local $iTargetColumn = $oresult.ActiveSheet.Usedrange.Columns.Count + 1
Local $oTargetRange = $oresult.ActiveSheet.Range("Usedrange" & $iTargetColumn)
 _Excel_RangeCopyPaste($odata.Activesheet, "Usedrange", $oTargetRange)
_Excel_RangeDelete($odata.Activesheet, "Usedrange", $xlShiftUp)

This are the both files: thecsvfilesdataandresult.zip

Thank you for your help.

[water]

So you want to copy a Worksheet from one Workbook to another?

[forum0member]

No, I don't want to copy the whole worksheet. I just want to copy the used special cells.

Look for the last used cells, where the rows B:M in result.csv are free and move cells from data.csv.

[forum0member]

do you have any suggestion?

[forum0member]

I tried to find the right columns and rows, but I don't understand how to do it right.

Can anybody help me?

expandcollapse popup

#include<Excel.au3>
$oExcel = _Excel_Open()
Local $oX = _Excel_BookOpen($oExcel, @ScriptDir & "\results.csv")
Local $sLastCell = FindLastCell($oX)
MsgBox(0, "", "Last cell address is: " & $sLastCell.address)

Func FindLastCell(ByRef $oExcel)

    Local $intColumn, $intLoop, $lngLoop, $lngRow, $objLast_Cell = ""

    $objWorksheet = $oExcel.ActiveSheet
    $intColumn = $objWorksheet.UsedRange.Column - 1 + $objWorksheet.UsedRange.Columns.Count
    $lngRow = $objWorksheet.UsedRange.Row - 1 + $objWorksheet.UsedRange.Rows.Count

    ConsoleWrite("1: " & $lngRow & @CRLF)
    For $lngLoop = $lngRow To 1 Step -1
        If $oExcel.Application.WorksheetFunction.CountA($objWorksheet.Rows($lngLoop)) > 0 Then ExitLoop
    Next

    $lngRow = $lngLoop

    ConsoleWrite("2: " & $intColumn & @CRLF)
    For $intLoop = $intColumn To 1 Step -1
        If $oExcel.Application.WorksheetFunction.CountA($objWorksheet.Rows($lngLoop)) > 0 Then ExitLoop
    Next

    $intColumn = $intLoop

    If $lngRow > 0 And _
        $lngRow <= $objWorksheet.Rows.Count And _
        $intColumn > 0 And _
        $intColumn <= $objWorksheet.Columns.Count Then
        $objLast_Cell = $objWorksheet.Cells($lngRow, $intColumn)
    EndIf

    Return $objLast_Cell
EndFunc
Local $oX = _Excel_BookOpen($oExcel, @ScriptDir & "\data.csv")
_Excel_RangeCopyPaste($oX.Activesheet, $intColumn, $lngRow)
Local $oX = _Excel_BookOpen($oExcel, @ScriptDir & "\results.csv")
_Excel_RangeDelete($oX.Activesheet, $intColumn, $lngRow)

Edited May 30, 2014 by forum0member

[forum0member]

Has anyone a suggestion for me, how to resolve my problem?