1 file is over a million rows, the other file is over 38k rows..
with the script i made, its like this,
it takes
1 day = 300rows (excel 1) to go through all 38K rows (excel 2)
i tried to compute how many days can i run at least 50K rows(on Excel 1) to go through all 38K on (Excel2).
my computation gave me 3 months! i cannot do that!!!!
i tried researching how to make it faster, and i think i need to fileread on memory and put it on array, instead of directly reading on excel...
Heres the Catch... I NEVER TRIED USING ARRAY AND FILEREAD
Can someone Please help me rework my script so that i can finish my work faster? please
Heres my code:
Code: Select all
#Persistent
#SingleInstance, Force
#NoEnv
Menu ,Tray, NoStandard
Menu,Tray, Add, Exit, GuiClose
SetKeyDelay, 30
SetBatchLines -1
ListLines Off
SetConTrolDelay, 0
SetWinDelay, 0
SetTitleMatchMode, 2
XI = %A_ScreenWidth%
YI = %A_ScreenHeight%
xtop := ((XI / 2) - 50)
xtopelev := (((XI / 2) - 50) / 2)
xtopelev2 := xtopelev/2
Ybot := YI-65
Ybot2 := YI-70
Ybot3 := YI-122
xbot := XI - 310
ytop := YI - YI
OnOffClrX := XI - 25
OnOffClrY := YI - 15
cc := 2
ccc := 0
fileselectfile, fileCh,,%A_ScriptDir%,Choose ChCut,(*.xls; *.xlsx)
fileselectfile, fileDh,,%A_ScriptDir%,Choose DH,(*.xls; *.xlsx)
if ERRORLEVEL
{
Process, Close, EXCEL.exe
Sleep, 50
Process, Close, EXCEL.exe
Sleep, 50
exitapp
}
else
Gui +LastFound +OwnDialogs +AlwaysOnTop
InputBox, ccc,Enter Last Row!,,,180,100,880,540
xl := ComObjCreate("Excel.Application")
oWorkBook := xl.Workbooks.Open(fileCh)
lstrw := oWorkBook.ActiveSheet.UsedRange.rows.count
xl2 := ComObjCreate("Excel.Application")
oWorkBook2 := xl2.Workbooks.Open(fileDh)
lstrw2 := oWorkBook2.ActiveSheet.UsedRange.rows.count
Gosub, byuwer
Goto, trytry
Return
trytry:
Loop, %lstrw%
{
ChX := oWorkbook.ActiveSheet.Range("K"cc).Value
ChY := oWorkbook.ActiveSheet.Range("L"cc).Value
ChZ := oWorkbook.ActiveSheet.Range("M"cc).Value
ChID := oWorkbook.ActiveSheet.Range("H"cc).Value
DhX := oWorkbook2.ActiveSheet.Range("X"ccc).Value
DhY := oWorkbook2.ActiveSheet.Range("Y"ccc).Value
DhZ := oWorkbook2.ActiveSheet.Range("Z"ccc).Value
DhID := oWorkbook2.ActiveSheet.Range("A"ccc).Value
DhCHAPA := oWorkbook2.ActiveSheet.Range("F"ccc).Value
qX:= (ChX-DhX)**2
qY:= (ChY-DhY)**2
qZ:= (ChZ-DhZ)**2
sagot := SQRT(qX + qY + qZ)
if(sagot<=5)
{
if(sagot>0)
{
FileAppend, %ChX%%A_Tab%%ChY%%A_Tab%%ChZ%%A_Tab%%ChID%%A_Tab%<CH========DH>%A_Tab%%DhX%%A_Tab%%DhY%%A_Tab%%DhZ%%A_Tab%%DhID%%A_Tab%%DhCHAPA%%A_Tab%<=Distance=>%A_Tab%%sagot%%A_Tab%<=DDH Row=>%A_Tab%%ccc%`n,%A_WorkingDir%\Found.txt
}
}
cc:=cc+1
GuiControl,20:,N1,%cc%
GuiControl,20:,N2,%ccc%
}
Goto, trytry2
Return
trytry2:
if(ccc<=lstrw2)
{
ccc:=ccc+1
cc:= 2
FileAppend, `n%ccc%,%A_WorkingDir%\LastDDH.txt
GoTo, trytry
}
oWorkbook.Close(1)
xl.Quit()
oWorkbook2.Close(1)
xl2.Quit()
FileAppend, Done on `n%A_ComputerName%,%A_WorkingDir%\Computer Done.txt
Shutdown, 9
exitapp
Return
byuwer:
Gui,20: Destroy
Gui,20: -Caption +ToolWindow +OwnDialogs -AlwaysOnTop +LastFound +E0x20
stringtry=%cc%%ccc%
StringLen, haba, stringtry
haba:=(haba+60)
Gui,20: Default
Gui,20: Font, s10, Arial Rounded MT Bold
Gui,20: Add, Text,h13, CHCut:
Gui,20: Add, Text,w%haba% h13 vN1 ys,%cc%
Gui,20: Font,
Gui,20: Font, s10, Arial Rounded MT Bold
Gui,20: Add, Text,h13 ys, -- DDH:
Gui,20: Add, Text,w%haba% h13 vN2 ys,%ccc%
Gui,20: Font, s50, Arial Rounded MT Bold
Gui,20: Add, Text,Center Xn,Please Don't Turn OFF this Computer!
Gui,20: Add, Text,Center Xn,Please Don't OPEN any EXCEL file too!
Gui,20: Font, s40, Arial Rounded MT Bold
Gui,20: Add, Text,Center,Processing Point Variance...
Gui,20: Show,
Return
GuiClose:
Process, Close, EXCEL.exe
Sleep, 50
Process, Close, EXCEL.exe
Sleep, 50
exitapp
Return
Please please help!
Warning!, if you do test my code, keep in mind after finishing, the script will shutdown the computer
