Sign in to follow this  
Followers 0
Dizzy

Excel - new line in _ExcelReadSheetToArray

8 posts in this topic

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 :P

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

Share this post


Link to post
Share on other sites



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?

Share this post


Link to post
Share on other sites

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? :P

Thanks

Dizzy

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#5 ·  Posted (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?

Thanks

Dizzy

Edited by Dizzy

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

OK - didn't use "_ExcelReadSheetToArray" .. for this action ..

It seems handling with _ExcelSheetUsedRangeGet is faster. :P

I'll have to recode my function.

Thanks for support.

Dizzy

Edited by Dizzy

Share this post


Link to post
Share on other sites

OK - didn't use "_ExcelReadSheetToArray" .. for this action ..

It seems handling with _ExcelSheetUsedRangeGet is faster. :P

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.

:unsure:


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

Share this post


Link to post
Share on other sites

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.

:D

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0