Copy Excel data range every 20 rows loop

[Vaida]

Hello,

Perhaps someone could help me. I am writing a script, where I need to copy data range every 20 rows from Excel file (column A and B), until there are no filled in rows. Here is what I have so far. I don't know how to create a loop, that would repeat copying from row 21, then 41, then 61 etc., then pasting it into other ERP, and would stop when there are no more data left.

Code: Select all

ex :=ComObjCreate("Excel.Application")
ex.visible := True
ex.Workbooks.Open("C:\Users\Desktop\slice.xlsm")
Sleep 2000

ex.Range("A1:B20").Copy
Sleep 1000
Send, ^v
Send, {Enter 1}
Send, {PgDn 1}
Send, {Down 1}
Sleep, 1000

[Mod edit: [code][/code] tags added.]

Thank you

[Mod edit: Moved topic to AHK v1 help, based on posted code.]

[mikeyww]

Welcome to this AutoHotkey forum!

Here is a start with a loop.

Code: Select all

; This script copies ranges of cells from an Excel worksheet
#Requires AutoHotkey v1.1.33
XL1       := ComObjActive("Excel.Application")
target    := "ahk_exe notepad.exe"
chunkSize := 20
Loop {
 endRow    := chunkSize * A_Index
 startRow  := endRow - chunkSize + 1
 range     := XL1.Worksheets("Sheet1").Range("A" startRow ":B" endRow)
 If !XL1.Application.WorksheetFunction.CountA(range)
  Break
 Clipboard := ""
 range.Copy
 ClipWait 0
 If ErrorLevel {
  MsgBox 48, Error, An error occurred while waiting for the clipboard.
  Return
 }
 SoundBeep 2000
 Control EditPaste, % Clipboard, Edit1, % target
}
MsgBox 64, Status, Done!
WinActivate % target

[flyingDman]

I presume that there would be some hotkey to paste one chunk of data at a time (otherwise why not paste the whole thing in one fell swoop?). Instead of making a COM call every time you want to paste a batch of 20 rows, I would get all the data in an array and then have the script parse that data and paste it one batch at a time (this also allows you to open and close multiple Excel files and get the ranges you need). The following reads the Excel data in a safe-array and pastes it 20 rows at a time by pressing F12 until there is no more data. The script also accommodates other than the first and second column (e.g. 2 and 4, 1 and 3, 1 and 3 and 5, etc.) and can (re)format data (e.g. use round to strip "0"s). Note: by doing it this way what you paste looses its formatting (as I am unfamiliar with SAP ERP, I do not know if this is important).

Code: Select all

sarr := ComObjActive("excel.application").activesheet.usedrange.value
; excel can close at this point as all data has been saved in sarr.
arr := [], cnt := 0, sz := 20
loop % sarr.maxindex(1)
	{
	lst .= sarr[a_index,1] "`t" sarr[a_index,2] "`n"		; column 1 and 2 of the usedrange
	if (!mod(a_index,sz)) OR (a_index = sarr.maxindex(1))
		arr.push(lst), lst := ""
	}

F12::
clipboard := arr[++cnt]
send, ^v
return

[mikeyww]

That makes more sense! Thanks, flyingDman.