Returning arrays of excel sheets

TTAJohn08 · December 13, 2007

First I have to appologize, im fairly new to coding but I learn very fast and have already written several small programs.

I am currently working on a program to take a PRE_OPENED excel workbook, find all of the sheets and thier names in the book, and return the vale as an array or otherwise. I am just starting this program and have almost no code for it, i just need help getting it off the ground. I am using the wonderful Excel_Com.UDF in this. Any insight is appreciated.

** If it helps to know i will be using this to go to each worksheet in the book and analyze data on each sheet.

big_daddy · December 14, 2007

I modified my _WordAttach to work with the ExcelCOM Library.

#include <Word.au3>
#include <ExcelCOM_UDF.au3>

$sFilePath = @ScriptDir & "\Test.xls"
$sFileName = "Test.xls"

_WordErrorHandlerRegister()
$oExcel = _ExcelAttach($sFilePath, "FilePath")
$oExcel = _ExcelAttach($sFileName, "FileName")

;===============================================================================
;
; Function Name:    _ExcelAttach()
; Description:      Attach to the first existing instance of Microsoft Excel where the
;                   search string matches based on the selected mode.
; Parameter(s):     $s_string   - String to search for
;                   $s_mode     - Optional: specifies search mode
;                                   FileName    = Name of the open Workbook
;                                   FilePath    = (Default) Full path to the open Workbook
; Requirement(s):   AutoIt3 Beta with COM support (post 3.1.1)
;                   On Success  - Returns an object variable pointing to the Excel.Application object
;                   On Failure  - Returns 0 and sets @ERROR
;                   @ERROR      - 0 ($_WordStatus_Success) = No Error
;                               - 1 ($_WordStatus_GeneralError) = General Error
;                               - 5 ($_WordStatus_InvalidValue) = Invalid Value
;                               - 7 ($_WordStatus_NoMatch) = No Match
;                   @Extended   - Contains invalid parameter number
; Author(s):        Bob Anthony (Code based off IE.au3)
;
;===============================================================================
;
Func _ExcelAttach($s_string, $s_mode = "FilePath")
    $s_mode = StringLower($s_mode)

    Local $o_Result, $o_workbook, $o_workbooks, $h_hwnd, $return, _
            $i_Extended, $s_ErrorMSG = "", $i_ErrorStatusCode = $_WordStatus_Success

    ; Setup internal error handler to Trap COM errors, turn off error notification
    Local $status = __WordInternalErrorHandlerRegister()
    If Not $status Then __WordErrorNotify("Warning", "_ExcelAttach", _
            "Cannot register internal error handler, cannot trap COM errors", _
            "Use _WordErrorHandlerRegister() to register a user error handler")
    Local $f_NotifyStatus = _WordErrorNotify() ; save current error notify status
    _WordErrorNotify(False)

    $o_Result = ObjGet("", "Excel.Application")
    If @error = $_WordStatus_ComError And $WordComErrorNumber = -2147221021 And $WordComErrorWinDescription = "Operation unavailable"  Then
        $i_ErrorStatusCode = $_WordStatus_NoMatch
    EndIf

    If $i_ErrorStatusCode = $_WordStatus_Success Then
        $o_workbooks = $o_Result.Application.Workbooks
        If Not IsObj($o_workbooks) Then
            $i_ErrorStatusCode = $_WordStatus_NoMatch
        EndIf
    EndIf

    If $i_ErrorStatusCode = $_WordStatus_Success Then
        For $o_workbook In $o_workbooks

            Switch $s_mode
                Case "filename"
                    If String($o_workbook.Name) = $s_string Then
                        $i_ErrorStatusCode = $_WordStatus_Success
                        $o_workbook.Activate
                        $return = $o_workbook
                    EndIf
                Case "filepath"
                    If String($o_workbook.FullName) = $s_string Then
                        $i_ErrorStatusCode = $_WordStatus_Success
                        $o_workbook.Activate
                        $return = $o_workbook
                    EndIf
                Case Else
                    ; Invalid Mode
                    $i_ErrorStatusCode = $_WordStatus_InvalidValue
                    $s_ErrorMSG = "Invalid Mode Specified"
                    $i_Extended = 2
            EndSwitch
        Next
        If Not IsObj($return) Then
            $i_ErrorStatusCode = $_WordStatus_NoMatch
        EndIf
    EndIf

    ; restore error notify and error handler status
    _WordErrorNotify($f_NotifyStatus) ; restore notification status
    __WordInternalErrorHandlerDeRegister()

    Switch $i_ErrorStatusCode
        Case $_WordStatus_Success
            SetError($_WordStatus_Success)
            Return $return
        Case $_WordStatus_NoMatch
            __WordErrorNotify("Warning", "_ExcelAttach", "$_WordStatus_NoMatch")
            SetError($_WordStatus_NoMatch)
            Return 0
        Case $_WordStatus_InvalidValue
            __WordErrorNotify("Error", "_ExcelAttach", "$_WordStatus_InvalidValue", $s_ErrorMSG)
            SetError($_WordStatus_InvalidValue, $i_Extended)
            Return 0
        Case Else
            __WordErrorNotify("Error", "_ExcelAttach", "$_WordStatus_GeneralError", "Invalid Error Status - Notify Word.au3 developer")
            SetError($_WordStatus_GeneralError)
            Return 0
    EndSwitch
EndFunc   ;==>_ExcelAttach

aslani · December 15, 2007

First I have to appologize, im fairly new to coding but I learn very fast and have already written several small programs.

I am currently working on a program to take a PRE_OPENED excel workbook, find all of the sheets and thier names in the book, and return the vale as an array or otherwise. I am just starting this program and have almost no code for it, i just need help getting it off the ground. I am using the wonderful Excel_Com.UDF in this. Any insight is appreciated.

** If it helps to know i will be using this to go to each worksheet in the book and analyze data on each sheet.

In the Excel_Com.UDF, locate the following;

_ExcelSheetList()

_ExcelSheetActivate()

I'm also working with Excel Spreadsheet and here's a portion of what I have just to get you started.

; Open new book, make it visible
Local $oExcel = _ExcelBookOpen($i_path, 1)

WinWaitActive("Microsoft Excel -")

$mySheetList = _ExcelSheetList($oExcel)

;_ArrayDisplay($mySheetList)
$g_sheet = ""
For $sht_array = 0 To Ubound($mySheetList)-1
    $g_sheet = $g_sheet & $mySheetList[$sht_array] ; Grabbing names of each Sheet.
Next

; Getting sheet quantity
$cvr_sht_qty = Ubound(StringRegExp($g_sheet, ".(COVER)", 3)) ; Counting how many cover sheets
$bill_sht_qty = Ubound(StringRegExp($g_sheet, ".(BILL)", 3)) ; Counting how many billing sheets

; Gathering cover sheet datas
_ReadCoverSht()
; Gathering billing datas
_ReadBomSht()

Func _ReadCoverSht()
    ;_ArrayDelete($mySheetList, 0)
    For $xls_eco_sht = 1 To $eco_sht_qty
        _ExcelSheetActivate($oExcel, $mySheetList[$xls_eco_sht])
        Sleep(1000)
        If $xls_eco_sht = 1 Then
            ; Data Elements of Items
            $p_Elements = _ArrayCreate("B27", "B29", "B31", "B33", "B35", "B37", "B39", "B41")
            $or_Elements = _ArrayCreate("D27", "D29", "D31", "D33", "D35", "D37", "D39", "D41")
            $nr_Elements = _ArrayCreate("E27", "E29", "E31", "E33", "E35", "E37", "E39", "E41")
            $d_Elements = _ArrayCreate("G27", "G29", "G31", "G33", "G35", "G37", "G39", "G41")
            
            ; Get Part Number datas
            $g_eco_pn1_num = _ExcelReadCell($oExcel, $p_Elements[0]) ; Get Item 1 - Part Number
            $g_eco_pn1_orv = _ExcelReadCell($oExcel, $or_Elements[0]) ; Get Item 1 - Old Rev
            $g_eco_pn1_nrv = _ExcelReadCell($oExcel, $nr_Elements[0]) ; Get Item 1 - New Rev
            $g_eco_pn1_des = _ExcelReadCell($oExcel, $d_Elements[0]) ; Get Item 1 - Description
            
            ; Data Arrays
            $pnumber = _ArrayCreate($g_eco_pn1_num)
            $oldrev = _ArrayCreate($g_eco_pn1_orv)
            $newrev = _ArrayCreate($g_eco_pn1_nrv)
            $descrip = _ArrayCreate($g_eco_pn1_des)
            
            ; Check for Items 2 - 8
            For $i = 1 To UBound($p_Elements)-1 Step 1
                If _ExcelReadCell($oExcel, $p_Elements[$i]) <> "" Then
                    _ArrayAdd($pnumber, _ExcelReadCell($oExcel, $p_Elements[$i]))
                    _ArrayAdd($oldrev, _ExcelReadCell($oExcel, $or_Elements[$i]))
                    _ArrayAdd($newrev, _ExcelReadCell($oExcel, $nr_Elements[$i]))
                    _ArrayAdd($descrip, _ExcelReadCell($oExcel, $d_Elements[$i]))
                EndIf
            Next
        Else
            ; Data Elements of Items
            $p_Elements2 = _ArrayCreate("B13", "B15", "B17", "B19", "B21", "B23", "B25", "B27", "B29", "B31", "B33", "B35", "B37", "B39", "B41")
            $or_Elements2 = _ArrayCreate("D13", "D15", "D17", "D19", "D21", "D23", "D25", "D27", "D29", "D31", "D33", "D35", "D37", "D39", "D41")
            $nr_Elements2 = _ArrayCreate("E13", "E15", "E17", "E19", "E21", "E23", "E25", "E27", "E29", "E31", "E33", "E35", "E37", "E39", "E41")
            $d_Elements2 = _ArrayCreate("G13", "G15", "G17", "G19", "G21", "G23", "G25", "G27", "G29", "G31", "G33", "G35", "G37", "G39", "G41")
            
            ; Get Part Number datas
            $g_eco_pn1_num2 = _ExcelReadCell($oExcel, $p_Elements2[0]) ; Get Item 1 - Part Number
            $g_eco_pn1_orv2 = _ExcelReadCell($oExcel, $or_Elements2[0]) ; Get Item 1 - Old Rev
            $g_eco_pn1_nrv2 = _ExcelReadCell($oExcel, $nr_Elements2[0]) ; Get Item 1 - New Rev
            $g_eco_pn1_des2 = _ExcelReadCell($oExcel, $d_Elements2[0]) ; Get Item 1 - Description
            
            ; Data Arrays
            _ArrayAdd($pnumber, $g_eco_pn1_num2)
            _ArrayAdd($oldrev, $g_eco_pn1_orv2)
            _ArrayAdd($newrev, $g_eco_pn1_nrv2)
            _ArrayAdd($descrip, $g_eco_pn1_des2)
            
            ; Check for Items from top to bottom
            For $i = 1 To UBound($p_Elements2)-1 Step 1
                If _ExcelReadCell($oExcel, $p_Elements2[$i]) <> "" Then
                    _ArrayAdd($pnumber, _ExcelReadCell($oExcel, $p_Elements2[$i]))
                    _ArrayAdd($oldrev, _ExcelReadCell($oExcel, $or_Elements2[$i]))
                    _ArrayAdd($newrev, _ExcelReadCell($oExcel, $nr_Elements2[$i]))
                    _ArrayAdd($descrip, _ExcelReadCell($oExcel, $d_Elements2[$i]))
                EndIf
            Next            
        EndIf
    Next
    MsgBox(0, "Cover Sheets", "Completed Gathering Datas from Cover sheets")
    
    ; Removing dashes and white spaces
    $pnumber = StringSplit(StringReplace(_ArrayToString($pnumber, ","), "-", ""), ",") ; Removing dashes from Part Numbers
    _ArrayDelete($pnumber, 0)
    
    ; List all Part Numbers
    If UBound($pnumber) < 2 Then
        GUICtrlSetData($l_pn, $pnumber[0], $pnumber[0])
        GUICtrlSetData($m_pn, $pnumber[0], $pnumber[0])
        GUICtrlSetData($r_pn, $pnumber[0], $pnumber[0])
        GUICtrlSetData($c_pn, $pnumber[0], $pnumber[0])
        GUICtrlSetData($u_pn, $pnumber[0], $pnumber[0])
    Else
        GUICtrlSetData($l_pn, _ArrayToString($pnumber, "|"), $pnumber[0])
        GUICtrlSetData($m_pn, _ArrayToString($pnumber, "|"), $pnumber[0])
        GUICtrlSetData($r_pn, _ArrayToString($pnumber, "|"), $pnumber[0])
        GUICtrlSetData($c_pn, _ArrayToString($pnumber, "|"), $pnumber[0])
        GUICtrlSetData($u_pn, _ArrayToString($pnumber, "|"), $pnumber[0])
    EndIf
    
    $pn_qty = Ubound($pnumber)
    
    Global $pn_matrix[$pn_qty][5]
    
    For $ix = 0 To $pn_qty-1
        $pn_matrix[$ix][0] = $ix+1
        $pn_matrix[$ix][1] = $pnumber[$ix]
        $pn_matrix[$ix][2] = $oldrev[$ix]
        $pn_matrix[$ix][3] = $newrev[$ix]
        $pn_matrix[$ix][4] = $descrip[$ix]
    Next
    
    ;_ArrayDisplay($pn_matrix, "Part Number Matrix") ; debug - view array matrix
EndFunc ; ====EndFunc _ReadCoverSht()

Func _ReadBomSht() ; Not yet complete
    Dim $assy_Elements[1]
    For $xls_bom_sht = $eco_sht_qty+1 To $eco_sht_qty+$bom_sht_qty
        _ExcelSheetActivate($oExcel, $mySheetList[$xls_bom_sht])
        Sleep(1000)
        For $ia = 0 To 41 Step 1
            $bx = "B" & 8+$ia
            If _ExcelReadCell($oExcel, $bx) <> "" Then
                ReDim $assy_Elements[UBound($assy_Elements) + 1]
                $assy_Elements[UBound($assy_Elements) - 1] = _ExcelReadCell($oExcel, $bx)
            EndIf
            Sleep(10)
        Next
    Next
    
    ;ReDim $assy_Elements[UBound($assy_Elements)-1][1]
    MsgBox(0, "Billing Sheets", "Completed Gathering Datas from billing sheets")
    
    ;_ArrayDisplay($assy_Elements, "Part Number Matrix")    
EndFunc ; ======EndFunc _ReadBomSht()

Good luck.

Sign In

Returning arrays of excel sheets

Recommended Posts

TTAJohn08

Link to comment

Share on other sites

big_daddy

Link to comment

Share on other sites

aslani

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta