Big data set excel

[MrCheese]

Hi Guys,

Appreciate the help!

Without getting into details... basically,

So, I have a large data set (about 10k rows, and 10 columns).

I need autoit to select A2, then paste it into an external program.

then, i need it to select B2 and paste it into another field, then C2, into another, D2 into another etc.

Then move onto row 3 and repeat.

Is there an easier way to cycle the row numbers in the loop aside from what I have done before on smaller data sets (ie. <30) where $run is the reference to the addition. Coding up 20k of if $run = blar then blar blar blar seems a little tedious.

I used _Excel_RangeCopyPaste before as shown below.... i am also not sure if some of my code is not necessary. Any help on that would be appreciated also.

 

Thanks for your help!

expandcollapse popup

#include <Excel.au3>
;assist variables etc to specific excel books etc.

Func cellselect()

if $run = 1 then
$data1 = A2
$data2 = B2
$data3 = C2
endif

if $run = 2 then
$data1 = A3
$data2 = B3
$data3 = C3
endif

EndFunc

$run=0
while 1
$run = $run+1
winactivate("spreadsheet title that is open already")
cellselect()
local $oRange = $oWorkbook.ActiveSheet.Range($data1)
_Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oRange, Default)
Winactivate ("other program")
mouseclick(Left,$pos1x,$pos1y)
send("^v")
sleep(1000)

local $oRange = $oWorkbook.ActiveSheet.Range($data2)
_Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oRange, Default)
Winactivate ("other program")
mouseclick(Left,$pos2x,$pos2y)
send("^v")
;do stuff here with data ,3,4,5 etc, flicking between excel and program, pasting specific data fields and then exit

WEnd

 

Edited July 20, 2016 by MrCheese
adding explanation and more code

[MrCheese]

I think I nailed the sequential row loop.

thanks to the UDF example!!

Any ideas about how to do a sequential column grab?

Would you need to manually map A = 1, B = 2 etc?

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Open Workbook 1
Local $oWorkbook1 = _Excel_BookOpen($oExcel, @ScriptDir & "/Book1.xlsx", True)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error opening workbook '" & @ScriptDir & "Book1.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf
$run = 0
$rrdif = 1
$rowpaste = 3
$ttlrun=10

; *****************************************************************************
; Copy a range with 3 rows and 2 columns on the active worksheet.
; Pass the source range as object.
; *****************************************************************************
While $run <$ttlrun
    $run = $run + 1
    $row = $run + $rrdif
    $row2 = $run + $rowpaste
    Local $oRange = $oWorkbook1.ActiveSheet.Range("A" & $row)
    _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oRange, "K" & $row2)

    Local $oRange = $oWorkbook1.ActiveSheet.Range("B" & $row)
    _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oRange, "L" & $row2)

    Local $oRange = $oWorkbook1.ActiveSheet.Range("C" & $row)
    _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oRange, "M" & $row2)
WEnd

 

[water]

It takes some tim to process 10000 rows the way you do it. Is speed an issue for your script? Means: The faster the better?