#include <Excel.au3>

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")
Global $sRandomString = "RandomDataakjvshsfdhttpher|1341297 04909385y276`4eis a link ;lnkra:// lalala/ ndomDataakj"
$sRandomString &= $sRandomString & '"' & $sRandomString & @CRLF
for $i=1 to 10
$sRandomString &= $sRandomString
Next


Global $oWorkbook_page_data
Global $oWorkbook_page_settings
$sWorkbook_page = "Data"
$path = @ScriptDir &"\"
$bookaddress= $path &"Data.xlsx"
$oExcel = _Excel_Open()
if FileExists($bookaddress) then
	$oWorkbook = _Excel_BookOpen($oExcel, $bookaddress)
	sleep(5000)
Else
$oWorkbook = _Excel_BookNew ( $oExcel , 3)
$oWorkbook.saveas($bookaddress)
EndIf
$timer_start = TimerInit()
$oWorkbook.Worksheets(1).Name = $sWorkbook_page
$oWorkbook.Worksheets(2).Name = "Settings"

CastoWorkbook_page()
$Data = $oWorkbook.Worksheets($sWorkbook_page).usedrange.Columns("A:AN").value

$oExcel.Application.Calculation = $xlCalculationManual

$xlUp=-4162
$ExcelLastRow = $oWorkbook_page_data.Cells(1000000, 1).End($xlUp).Row
if $ExcelLastRow  < 2 then $ExcelLastRow  = 2

;~ WriteRandomData()
;~ $oExcel.Application.Calculation =  $xlCalculationAutomatic
ReadRandomData()


$oExcel.Application.Calculation =  $xlCalculationAutomatic




Exit





Func MyErrFunc()
	$HexNumber = Hex($oMyError.number, 8)
	$oError = $oMyError
	$TError = "We intercepted a COM Error !" & @CRLF
	$TError = ''
	$TError &=		@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF
	$TError &=		@TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) ;& @CRLF
	$TError &=		@TAB & "err.windescription:" & @TAB & $oError.windescription ;& @CRLF
	$TError &=		@TAB & "err.description is: " & @TAB & $oError.description & @CRLF
	$TError &=		@TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF
	$TError &=		@TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF
	$TError &=		@TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF
	$TError &=		@TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF
	$TError &=		@TAB & "err.scriptline is: " & @TAB & $oError.scriptline ;& @CRLF
	$TError &=		@TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode); & @CRLF & @CRLF
	$TError &= @CRLF
	$TError &= @CRLF
	$TError &= @CRLF
ConsoleWrite($TError )
ExcelObjectDebug()
ConsoleWrite(@CRLF)
ConsoleWrite( "Time Run: " &TimerDiff($timer_start))
ConsoleWrite(@CRLF)
ConsoleWrite(@CRLF)
Exit

EndFunc   ;==>MyErrFunc


Func ExcelObjectDebug()

If (not ($oExcel.ready = True)) Then
	ConsoleWrite("T1 whileexcelbusy, oExcel.ready = "& $oExcel.ready & @CRLF)
EndIf



If (not (IsObj($oWorkbook.Worksheets()  ))) Then
	ConsoleWrite("T2 $oWorkbook.Worksheets() Type:" & VarGetType($oWorkbook.Worksheets()) & @CRLF)
EndIf

If (not (IsObj($oWorkbook.Worksheets($sWorkbook_page)  ))) Then
	;this happens
	ConsoleWrite("T3 $oWorkbook.Worksheets($sWorkbook_page) Type:" & VarGetType($oWorkbook.Worksheets($sWorkbook_page)) & @CRLF)
EndIf


If (not (IsObj($oWorkbook_page_data.Cells(1,1)))) Then
	ConsoleWrite("T5B $oWorkbook_page_data.Cells(1,1)) Type:" & VarGetType($oWorkbook_page_data.Cells(1,1)) & @CRLF)
EndIf

If (not (IsString($oWorkbook_page_settings.Cells(1,1).Value)))  Then
	ConsoleWrite("T5 $oWorkbook_page_settings.Cells(1,1).value Type:" & VarGetType($oWorkbook_page_settings.Cells(1,1).Value) & @CRLF)
EndIf



If (not (IsString($oWorkbook_page_data.Cells(1,1).Value)))  Then
	ConsoleWrite("T5 $oWorkbook_page_data.Cells(1,1).value Type:" & VarGetType($oWorkbook_page_data.Cells(1,1).Value) & @CRLF)
EndIf





Return True

EndFunc















Func RandomTrueFalse($ChanceTrue)
$iS = random()
if $is > $ChanceTrue then
	return False
Else
	Return True
EndIf

EndFunc






Func GenerateTextString($len)
$iS = int(random(1,20))
return StringMid($sRandomString,$iS , $len)
EndFunc




Func InitialiseWorkBook_links()
;~ 	if $WorkbookInitialised = 1 Then Return 0

	CastoWorkbook_page()
	$Data = $oWorkbook.Worksheets($sWorkbook_page).usedrange.Columns("A:AN").value

	$WorkbookInitialised = 1

EndFunc



Func CastoWorkbook_page()
$oWorkbook_page_data =""
for $each in $oWorkbook.Worksheets()
	ConsoleWrite("Name:" & $each.name & @CRLF)
	if $each.name = $sWorkbook_page Then
		$oWorkbook_page_data = $each
		ExitLoop
	EndIf



Next
$sWorkbook_page_settings = "Settings"
$oWorkbook_page_settings=""
for $each in $oWorkbook.Worksheets()
	ConsoleWrite("Name:" & $each.name & @CRLF)
	if $each.name = $sWorkbook_page_settings Then
		$oWorkbook_page_settings = $each
		ExitLoop
	EndIf



Next



EndFunc



Func WriteRandomData()



for $iRow = $ExcelLastRow  to 150000
	if RandomTrueFalse(0.2) then ContinueLoop


$string_len3 = GenerateTextString(int(Random(20,100)))
$string_len1 = GenerateTextString(10)

$str1 = GenerateTextString(10)
$str3 = GenerateTextString(int(Random(20,100)))
for $iC = 1 to 7
$oWorkbook_page_data.cells($iRow,1*$iC).value = $str1
$oWorkbook_page_data.cells($iRow,3*$iC).value =  $str3
Next

Next
$oWorkbook.saveas($bookaddress)
EndFunc


Func ReadRandomData()

for $iTry = 1  to 5000000

	$iRow = int(Random(1,$ExcelLastRow))

$iCol = int(Random(1,11))

$x = $oWorkbook_page_data.cells($iRow,$iCol).value &@CRLF
;~ sleep(1) ;without crashes at 4680 ;with at 4517
ConsoleWrite($iTry)
ConsoleWrite(@CRLF)
;~ ConsoleWrite($oWorkbook_page_data.cells($iRow,$iCol).value &@CRLF)

Next

EndFunc