TTAJohn08 Posted December 13, 2007 Share Posted 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. Link to comment Share on other sites More sharing options...
Moderators big_daddy Posted December 14, 2007 Moderators Share Posted December 14, 2007 I modified my _WordAttach to work with the ExcelCOM Library. expandcollapse popup#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 Link to comment Share on other sites More sharing options...
aslani Posted December 15, 2007 Share Posted 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. expandcollapse popup; 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. [font="Georgia"]Chances are, I'm wrong.[/font]HotKey trouble?Stringregexp GuideAutoIT Current Version Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now