Dizzy Posted March 18, 2009 Share Posted March 18, 2009 Hi all, i've a little problem with Excel. As you can see, i use _ExcelReadSheetToArray. Now i'm searching the through the array for a value. If it is there - ok. If not, i make a new entry ($Excel_List[0][0] + 1). Now my prob: to include the new lines i have to reread the file to the array ($Excel_Liste = _ExcelReadSheetToArray($Excelout, 1, 1)). This is very slowly and i think that there should be an better way. Any ideas are welcome Thanks $Excel_Liste = _ExcelReadSheetToArray($Excelout, 1, 1) For $x = 2 to $aRecords[0] $epo = StringSplit($aRecords[$x], ",") $Excelfound = 0 For $i = 9 to $Excel_List[0][0] If $Excel_List[$i][1] = $epo[1] Then $Excelfound = 1 _ExcelWriteCell($Excelout, "1", $i , 5) _ExcelWriteCell($Excelout, $epo[2], $i , 4) ExitLoop EndIf Next If $Excelfound = 0 then _ExcelWriteCell($Excelout, $epo[1], $Excel_List[0][0] + 1, 1) _ExcelWriteCell($Excelout, "", $Excel_List[0][0] + 1, 3) _ExcelWriteCell($Excelout, "1", $Excel_List[0][0] + 1, 5) _ExcelWriteCell($Excelout, $epo[2], $Excel_List[0][0] + 1, 4) $Excel_Liste = _ExcelReadSheetToArray($Excelout, 1, 1) EndIf Next Link to comment Share on other sites More sharing options...
foster74 Posted March 18, 2009 Share Posted March 18, 2009 I don't know how valid of a solution this is, but what about adding the extra row before _ExcelReadSheetToArray then if the data is found delete the row in excel and _ArrayDelete($Array, 0) on the array? Link to comment Share on other sites More sharing options...
Dizzy Posted March 18, 2009 Author Share Posted March 18, 2009 I don't know how valid of a solution this is, but what about adding the extra row before _ExcelReadSheetToArray then if the data is found delete the row in excel and _ArrayDelete($Array, 0) on the array?Thanks for replay but i don't know how much rows will follow. I scan a file and depending of existence or not i want to add the new entry.Any other ideas? ThanksDizzy Link to comment Share on other sites More sharing options...
foster74 Posted March 18, 2009 Share Posted March 18, 2009 Any chance you could make all the new entries after you've finished parsing the array? Link to comment Share on other sites More sharing options...
Dizzy Posted March 18, 2009 Author Share Posted March 18, 2009 (edited) Any chance you could make all the new entries after you've finished parsing the array?Hmm - not really. 'Cause i've to check if a value exits more than once .. so i've to check it against the cell-entries. (Or i have to read all in an array to check it in RAM ..)But i'll think around a little bit.More Ideas?ThanksDizzy Edited March 18, 2009 by Dizzy Link to comment Share on other sites More sharing options...
Dizzy Posted March 18, 2009 Author Share Posted March 18, 2009 (edited) OK - didn't use "_ExcelReadSheetToArray" .. for this action .. It seems handling with _ExcelSheetUsedRangeGet is faster. I'll have to recode my function. Thanks for support. Dizzy Edited March 18, 2009 by Dizzy Link to comment Share on other sites More sharing options...
PsaltyDS Posted March 18, 2009 Share Posted March 18, 2009 OK - didn't use "_ExcelReadSheetToArray" .. for this action .. It seems handling with _ExcelSheetUsedRangeGet is faster. I'll have to recode my function. Thanks for support. Dizzy If the only changes to the sheet after you do _ExcelReadSheetToArray() the first time are those you make yourself, then you could ReDim the array and update it at the same time you add data to the sheet: $avSheet = _ExcelReadSheetToArray($oExcel) ; Add a new row with data in col 1 $sNewData = "This is new data" ReDim $avSheet[UBound($avSheet) + 1][UBound($avSheet, 2)] $avSheet[0][0] = UBound($avSheet) - 1 $avSheet[$avSheet[0][0]][1] = $sNewData _ExcelWriteCell($oExcel, $sNewData, $avSheet[0][0], 1) This should be MUCH faster. You only need to run _ExcelReadSheetToArray() when there is a possibility that your array has gotten out of sync with the actual sheet. Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
Dizzy Posted March 24, 2009 Author Share Posted March 24, 2009 Hi PsaltyDS, thanks for reply. This sounds logical. I'll try it. Dizzy If the only changes to the sheet after you do _ExcelReadSheetToArray() the first time are those you make yourself, then you could ReDim the array and update it at the same time you add data to the sheet: $avSheet = _ExcelReadSheetToArray($oExcel) ; Add a new row with data in col 1 $sNewData = "This is new data" ReDim $avSheet[UBound($avSheet) + 1][UBound($avSheet, 2)] $avSheet[0][0] = UBound($avSheet) - 1 $avSheet[$avSheet[0][0]][1] = $sNewData _ExcelWriteCell($oExcel, $sNewData, $avSheet[0][0], 1) This should be MUCH faster. You only need to run _ExcelReadSheetToArray() when there is a possibility that your array has gotten out of sync with the actual sheet. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now