Problem with Excel_RangeWrite

[YouriKamperman]

I am working on a script that will turn all file names in a directory into an array, and then writes this Array to an Excel file.

This in itself is working, but the RangeWrite function always puts the Array count in the first cell.

How can i make sure this does not happen? I can of course just filter it out in Excel, but i am trying to keep all logic of filtering text in my script.

This is what my script looks like:

Local $Yesterday = _DateAdd('d', -1, _NowCalcDate())
Local $cDate = StringReplace($Yesterday, "/", "-")

Local $aFileList = _FileListToArray(@WorkingDir & "/" & $cDate, "*")

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aFileList)

[Subz]

Delete the count in the Array

_ArrayDelete($aFileList, 0)

 

[YouriKamperman]

Works like a charm!

Thanks a lot for the quick reply!

[stick3r]

Not to start a new topic, I will ask here as my question is related with RangeWrite.

What I have for now:

expandcollapse popup

Func _OpenDialog()

    $sFileOpenDialog = FileOpenDialog($sMessage, @WindowsDir & "\", "All (*.*)", $FD_FILEMUSTEXIST + $FD_MULTISELECT)

EndFunc   ;==>_OpenDialog

Func _ExcelOpen()

    $oExel = _Excel_Open()
    $oWorkbook = _Excel_BookOpen($oExel, $sFileOpenDialog)

EndFunc   ;==>_ExcelOpen


;================Get ID's from Excel=============================
Func _getID()

    Local $arrResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 2)
    For $i = 1 To UBound($arrResult) - 1
=======Irrelevant code running=======
        _makingActions()
    Next

EndFunc   ;==>_getID


;===================Changing Orders=================
Func _makingActions()
    For $i = 7 To 17
        Local $strK = _Extra_StringGet($oExtra, $i, 10, 1)

        If $strK = "K" Then
=======Irrelevant code running=======
            EndIf
        Else
            Return
        EndIf
    Next

EndFunc   ;==>_makingActions

I get ID's from Excel A column, save them to array and then do some things with them.

What I need to do is after completing with every ID, write something in L column.

If ID was from A2, I need to write something  to L2, then finish with ID from A3 and write something to L3.

How do I do that?

Edited May 9, 2018 by stick3r

[Subz]

Do you mean you want to just write the modified array to L Column?

_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $arrResult, "L1")

 

[stick3r]

Not exactly. I have ID's, i.e. 5372615376, in $arrResult, then _makingActions() withing other program with these ID's.

When finished _makingActions(), I want to write Completed or Incompleted in L column. First ID is in A2 and there are a lot of them. So when finished _makingActions() with first ID, I want to write "Completed" or "Incompleted" in L2, depending on the result.

[Subz]

So you mean you copy A2-A100 for example from the spreadsheet, you then want to loop through the array and do stuff and send a response back?  If so you can do it two ways you can use the index of the loop to write back to the spreadsheet, one at a time (using an offset e.g. if A2 = 1 in your array, then you would then write back to $index + 1) or expand your array to two columns and write the response back to the second column, once completed you can write the entire second column back to the Excel spreadsheet.  It really depends on your preference.  If you can provide an example spreadsheet and what results you expect then might be able to put something together.