Jump to content
Sign in to follow this  
forum0member

How to translate VBA code to AutoIt script?

Recommended Posts

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 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

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

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

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  

×
×
  • Create New...