Jump to content

_Excel_RangeWrite One Column only


Recommended Posts

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

 

Link to post
Share on other sites

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

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

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...