_Excel_RangeWrite One Column only

[zuladabef]

I am reading an array from one Excel file with 10 columns and creating a new Excel file with only 3 of the columns.  How do I use _Excel_RangeWrite to copy/paste a column?  Do I have to loop through the array, or can I tell _Excel_RangeWrite to only write from one column of a 2-D Array?

I am not sure how to show my code, since I am copying/pasting from one Excel file to another, but here is an idea of what I am doing.

 

#include-once
#include <Array.au3>
#include <Date.au3>
#include <Excel.au3>

Local $sFileStudentList = @ScriptDir & "\excel files\" & "StudentHousing.xlsx"
Local $aStudentData = _GetStudentDataFromExcel()

; Create application object and create a new workbook
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)


;For Some Reason, this does not like to be grouped together and produces errors in the output
For $iCC = 1 To UBound($aStudentData) - 1
    _Excel_RangeWrite($oWorkbook, Default, $aStudentData[$iCC][0], "A" & $iCC + 1)
    _Excel_RangeWrite($oWorkbook, Default, $aStudentData[$iCC][1], "B" & $iCC + 1)
    _Excel_RangeWrite($oWorkbook, Default, $aStudentData[$iCC][4], "C" & $iCC + 1)
Next

Func _GetStudentDataFromExcel()
    Local $oExcel = _Excel_Open()
    Local $oWorkbook = _Excel_BookOpen($oExcel, $sFileStudentList)
    
    Local $aStudentData = _Excel_RangeRead($oWorkbook)
    _Excel_BookClose($oExcel, False)
    _Excel_Close($oExcel, False, True)
    
    Return $aStudentData
EndFunc   ;==>_GetStudentDataFromExcel

 

[Nine]

You could use _Excel_RangeCopyPaste.  That would allow you to do the whole transfer in a single step.  It is also the best method to perform the transfer if you want to keep formats and formulas.  If this is the case, you will need to provide the appropriate $iPaste flag.

Edited March 3, 2021 by Nine

[Subz]

You could also manipulate the array using _ArrayColDelete or _ArrayExtract rough examplse (untested, but hopefully will help you get started):

_AddStudentDataFromExcel($aStudentData, -1, -1, 0, 0, "A1")
_AddStudentDataFromExcel($aStudentData, -1, -1, 1, 1, "B1")
_AddStudentDataFromExcel($aStudentData, -1, -1, 4, 4, "C1")

Func _AddStudentDataFromExcel($_aStudentData, $_iRowStart = -1, $_iRowEnd = -1, $_iColStart = -1, $_iColEnd = -1, $_vCell)
    Local $aExtract = _ArrayExtract($_aStudentData, $_iRowStart, $_iRowEnd, $_iColStart, $_iColEnd)
    _Excel_RangeWrite($oWorkbook, Default, $aExtract, $_vCell)
EndFunc

;~ Or
Local $aColDelete[7] = [2,3,5,6,7,8,9]
Local $aExtracted = _GetStudentDataFromArray($aStudentData, $aColDelete)

Func _GetStudentDataFromArray($_aStudentData, $_aColDelete)
    Local $aExtract = $_aStudentData
    _ArraySort($_aColDelete) ;~ Need to delete columns from highest to lowest 
    For $i = UBound($_aColDelete) - 1 To 0 Step -1
        _ArrayColDelete($aExtract, $_aColDelete[$i])
    Next
    Return $aExtract
EndFunc