Jump to content
YouriKamperman

Problem with Excel_RangeWrite

Recommended Posts

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)

Share this post


Link to post
Share on other sites
Subz

Delete the count in the Array

_ArrayDelete($aFileList, 0)

 

  • Thanks 1

Share this post


Link to post
Share on other sites
YouriKamperman

Works like a charm!

Thanks a lot for the quick reply!

Share this post


Link to post
Share on other sites
stick3r
Posted (edited)

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

What I have for now:

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 by stick3r

Share this post


Link to post
Share on other sites
Subz

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

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

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

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

  • Similar Content

    • Skeletor
      By Skeletor
      Hi Guys,
      Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 
      .Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets I also tried it like this:
      .Range("=$A3:$A4000").FormatConditions _ .Add(xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets  
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
    • AnonymousX
      By AnonymousX
      Hello,
      I'm trying to write a script that moves copies excel cells into an array. I'll than manipulate the values and send array into another program. 
      I don't want range to be specific to a workbook, or sheet, or set of cells.
      I want user to be able to highlight desired cells and to copy either normally ("Ctrl+C") or by a hotkey ("Alt+C"). 
      Could someone help me with this?
      Thank you,
      I've tried to write the framework: (edited)
      #include <MsgBoxConstants.au3> #include <Array.au3> #include <Excel.au3> HotKeySet("!v", "Pastedata") While True Sleep(1000) WEnd func Makearray() local $bArray ;User has cells already copied ;Convert clipboard into an array ;I don;t know how excel stores data to clipboard so don;t know how to bring it into array _Arraydisplay($bArray) MsgBox(0,0,$bArray) return $bArray endfunc func Pastedata() Local $aArray MsgBox(0,0,"wait",1) ;make array based on assumption user has already copied a range to clipboard $aArray = Makearray() ;paste code ;don;t worry about this I got the rest endfunc  
×