COM Object: Excel.Sheet
Purpose: Hold MS Excel Data - represents a MS Excel workbook
System Requirements: MS Excel
Documentation Link: WorkBook Object
Other Links: Excel Constants, Sort Method
Code Example:
;// Excel ConstantsxlMaximized := -4137xlAsc := xlYes := 1;// Create WorkBookXLBook := ComObjCreate("Excel.Sheet")XLSht := XLBook.ActiveSheet;// Maximize the WorkBook ControlXLBook.Windows(1).WindowState := xlMaximizedXLBook.Application.Visible := true;// Fill in Data & Sortfor cell in XLSht.Range("A1:C10") if (cell.row = 1) cell.value := "Header " [color=brown]A_Index else { Random, num, 0, 1 cell.value := num };// Sort DataMsgBox, Sorting by Column 1, then 2, then 3...XLSht.Cells.Sort( XLSht.Columns(1), xlAsc , XLSht.Columns(2), ComObjMissing(), xlAsc , XLSht.Columns(3), xlAsc , xlYes );// Save WorkBookMsgBox, 4, , The Workbook will close once the object is released - would you like to save it to the scirpt directory?IfMsgBox, Yes XLBook.SaveAs(A_ScriptDir "\Excel.Sheet Example.xls")
Note - the Excel.Sheet object will open in the Active Excel Process (accessed by ComObjActive) - or will create a new Excel Process - and it will close when the pointer is released.
COM Object: Excel.Application
Purpose: Perform calculations, analyse information and visualise data in spreadsheets.
System Requirements: Microsoft Office Excel application
Documentation Link: <!-- m -->http://msdn.microsof...ry ... 12).aspx<!-- m -->
Other Links:
Basic Code Example:
oExcel := ComObjCreate("Excel.Application") ; create Excel Application objectoExcel.Workbooks.Add ; create a new workbook (oWorkbook := oExcel.Workbooks.Add)oExcel.Range("A1").Value := 3 ; set cell A1 to 3oExcel.Range("A2").Value := 7 ; set cell A2 to 7oExcel.Range("A3").Formula := "=SUM(A1:A2)" ; set formula for cell A3 to SUM(A1:A2)oExcel.Range("A1:A3").Interior.ColorIndex := 19 ; fill range of cells from A1 to A3 with color number 19oExcel.Range("A3").Borders(8).LineStyle := 1 ; set top border line style for cell A3 (xlEdgeTop = 8, xlContinuous = 1)oExcel.Range("A3").Borders(8).Weight := 2 ; set top border weight for cell A3 (xlThin = 2)oExcel.Range("A3").Font.Bold := 1 ; set bold font for cell A3A1 := oExcel.Range("A1").Value ; get value from cell A1, and store it in A1 variableoExcel.Range("A4").Select ; select A4 celloExcel.Visible := 1 ; make Excel Application visibleMsgBox % A1 "`n" oExcel.Range("A2").Value ; check. You can use Round() function to round numbers to the nearest integerExitApp
How to access Workbook without opening it?
FilePath := "C:\Book1.xlsx" ; example pathoWorkbook := ComObjGet(FilePath) ; access Workbook objectMsgBox % oWorkbook.Sheets(1).Range("A1").Value ; get value from A1 cell in first sheet
How to access active Excel Application object?
Use oExcel := Excel_Get(), not oExcel := ComObjActive("Excel.Application"). More info here.
How to access active Workbook?
tryoWorkbook := Excel_Get().ActiveWorkbook ; try to access active Workbook objectcatchreturn ; case when Excel doesn't exist, or it exists but there is no active workbook. Just Return or Exit or ExitApp.; if there is active workbook, code continues execution...oWorkbook.ActiveSheet.Range("B2").Value := "B2" ; set value of B2 cell in active sheet to "B2"
How to access Excel object from Workbook object?
oExcel := oWorkbook.Application ; returns Excel application object that owns Workbook object
xl := ComObjActive("Excel.Application")for cell in xl.range("Q4:Q200"){ case := cell.value if case between 0.07 and 1 Cell.Interior.ColorIndex := 3 ; Red else if case between 0.065 and 0.07 Cell.Interior.ColorIndex := 46 ; Orange else if case between 0.06 and 0.065 Cell.Interior.ColorIndex := 45 ; Light Orange else if case between 0.055 and 0.06 Cell.Interior.ColorIndex := 44 ; Gold else if case between 0.05 and 0.055 Cell.Interior.ColorIndex := 40 ; Tan else if case between 0.0475 and 0.05 Cell.Interior.ColorIndex := 36 ; Light Yellow Else Cell.Interior.ColorIndex := -4142 ; Default}return
xl := ComObjActive("Excel.Application")loop 4 xl.cells(A_Index, 1).value := "A", xl.cells(A_Index, 2).value := A_Indexloop 2 xl.cells(A_Index+4, 1).value := "B", xl.cells(A_Index+4, 2).value := A_Index+4; the above is to create sample dataArray := {}Loop { if !(xl.cells(A_Index, 1).value) break Name := xl.cells(A_Index, 1).Text Product := xl.cells(A_Index, 2).Text Array[Product] := Name }return!1:: ; press alt+1 to Enter a nameInputBox, MyName, Question, Please Enter NameMyProducts := ""for Product, Name in Array MyProducts .= (Name = MyName) ? "> " Product "`n" : ""MsgBox % """" MyName """ Has `n" MyProductsreturn
聯(lián)系客服
