Sign in to follow this  
Followers 0
forum0member

How to translate VBA code to AutoIt script?

6 posts in this topic

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.

Share this post


Link to post
Share on other sites



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


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

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.

Share this post


Link to post
Share on other sites

do you have any suggestion?

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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

Can anybody help me?

#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 by forum0member

Share this post


Link to post
Share on other sites

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

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