Jump to content
Sign in to follow this  
TTAJohn08

Returning arrays of excel sheets

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

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
Sign in to follow this  

×
×
  • Create New...