forum0member Posted May 27, 2014 Share Posted May 27, 2014 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. Link to comment Share on other sites More sharing options...
water Posted May 27, 2014 Share Posted May 27, 2014 So you want to copy a Worksheet from one Workbook to another? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
forum0member Posted May 27, 2014 Author Share Posted May 27, 2014 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. Link to comment Share on other sites More sharing options...
forum0member Posted May 30, 2014 Author Share Posted May 30, 2014 do you have any suggestion? Link to comment Share on other sites More sharing options...
forum0member Posted May 30, 2014 Author Share Posted May 30, 2014 (edited) 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 Link to comment Share on other sites More sharing options...
forum0member Posted June 2, 2014 Author Share Posted June 2, 2014 Has anyone a suggestion for me, how to resolve my problem? Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now