Excel - new line in _ExcelReadSheetToArray

[Dizzy]

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

[foster74]

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?

[Dizzy]

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?

Thanks

Dizzy

[foster74]

Any chance you could make all the new entries after you've finished parsing the array?

[Dizzy]

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?

Thanks

Dizzy

Edited March 18, 2009 by Dizzy

[Dizzy]

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

[PsaltyDS]

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.

[Dizzy]

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.