Comparing 2 excel Files takes Forever to finish Topic is solved

[ktbjx]

I found another problem that occurs with every method you try to find the appropriate records: FILEAPPEND
it opens and closes the file for every record
I have my version changed below to avoid this problem:

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
tab := {}
ccc := 1
loop, % lstrw2
{
	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
	tab.insert({DhX : DhX, DhY : Dhy, DhZ : DhZ, DhID : DhID, DhCHAPA : DhCHAPA})
	ccc++
}
ccc := 1
Gosub, byuwer

filedelete, % a_scriptdir "\found.txt"
file_found := fileopen(a_scriptdir "\found.txt", "w")
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
		
	Loop, %lstrw2%
	{
		ccc := a_index
		DhX := tab[a_index, "DhX"]
		DhY := tab[a_index, "DhY"]
		DhZ := tab[a_index, "DhZ"]
		DhID := tab[a_index, "DhID"]
		DhCHAPA:= tab[a_index, "DhCHAPA"]

		qX:= (ChX-DhX)**2
		qY:= (ChY-DhY)**2
		qZ:= (ChZ-DhZ)**2
	
		sagot := SQRT(qX + qY + qZ)
			
		if(sagot<=5)
		{
			if(sagot>0)
			{
				found_rec = %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%
				file_found.writeline(found_rec)
;				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%
}
oWorkbook.Close(1) 
xl.Quit()
oWorkbook2.Close(1) 
xl2.Quit()
file_found.close()
FileAppend, Done on `n%A_ComputerName%,%A_WorkingDir%\Computer Done.txt
;Shutdown, 9
exitapp

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

Hubert

I see what you did, and that one does have a point. will try this tomorrow, its Sunday now, rest day.. thank you sir! I will Update you

[IMEime]

by my simple testing of AHK's array
it is slower than C# almost 12 times
and, As far as I know, C# is not so fast one

https://www.autohotkey.com/boards/viewt ... 08#p258767