Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #SingleInstance, force
- /*
- xl := ComObjCreate("Excel.Application") ; creating Excel workbook handle
- xl.Visible := true ; making window visible
- ; Cheching if there is an active window
- ;----------------------------------------------------
- try xl := ComObjActive("Excel.Application")
- catch {
- MsgBox, no existing excel object. need to create one.
- xl := ComObjCreate("Excel.Application")
- xl.Visible := true
- }
- MsgBox, % IsObject(xl) ;this function let us check if there is a connection to an object (1 - yes, 0 - no)
- */
- /*;Choosing a file from a dialog box
- ;----------------------------------------------------
- FileSelectFile, MySelectedFile ;filepath is stored in a variable
- Sleep, 300
- MsgBox, We will open this file: %MySelectedFile%
- try xl := ComObjActive("Excel.Application")
- catch {
- MsgBox, no existing excel object. need to create one.
- xl := ComObjCreate("Excel.Application")
- xl.Visible := true
- }
- xl.workbooks.open(MySelectedFile)
- xl.visible := 1
- */
- /*;Accessing worksheet without opening it
- ;----------------------------------------------------
- FilePath := "\\VEBP1359\COGNJK$\MyPersonalData\Desktop\AHK training\Lesson 4\Book1.xlsx"
- xl := ComObjGet(FilePath)
- value_from_worksheet := xl.sheets(1).Range("A1").Value
- value_from_worksheet2 := xl.sheets(1).Range("C3").Value
- MsgBox, Selected values are: %value_from_worksheet% and %value_from_worksheet2%
- */
- /*;Adding values
- ;----------------------------------------------------
- contract := 123456
- xl := ComObjCreate("Excel.Application") ; creating Excel workbook handle
- xl.Visible := true
- FilePath := "\\VEBP1359\COGNJK$\MyPersonalData\Desktop\AHK training\Lesson 4\Book1.xlsx"
- xl.workbooks.open(FilePath)
- Sleep 400
- xl.sheets(1).Range("G1").Value := contract
- Sleep 400
- xl.sheets(1).Range("G2").Value := "5555555"
- MsgBox, Values have been added.
- xl.Quit()
- xl := ""
- */
- /*;Retrieving values from columns
- ;----------------------------------------------------
- FilePath := "\\VEBP1359\COGNJK$\MyPersonalData\Desktop\AHK training\Lesson 4\Book1.xlsx"
- try xl := ComObjActive("Excel.Application")
- catch {
- MsgBox, no existing excel object. need to create one.
- xl := ComObjCreate("Excel.Application")
- xl.Visible := true
- xl.workbooks.open(FilePath)
- }
- xl.workbooks.open(FilePath)
- xl.Visible := true
- if (IsObject(xl) = 1)
- MsgBox, Object is created.
- else
- MsgBox, There is no object.
- ;~ while (xl.sheets(1).Range("A" . A_Index).Value != "")
- ;~ {
- ;~ value := xl.sheets(1).Range("A" . A_Index).Value
- ;~ MsgBox, %value%
- ;~ }
- ;~ MsgBox, No more entries in the column
- ;storing value in a variable
- while (xl.sheets(1).Range("A" . A_Index).Value != "")
- {
- value .= xl.sheets(1).Range("A" . A_Index).Value "|"
- MsgBox, %value%
- }
- */
- /*; Adding workbooks and worksheets, saving document
- ;-----------------------------------------------------------
- xl := ComObjCreate("Excel.Application") ; creating Excel workbook handle
- xl.Visible := true
- xl.workbooks.add()
- xl.sheets.add()
- WhereToSave := "\\VEBP1359\COGNJK$\MyPersonalData\Desktop\AHK training\Lesson 4\new.xlsx"
- MsgBox, File will be saved here: %WhereToSave%
- xl.ActiveWorkbook.SaveAs(WhereToSave)
- Sleep 500
- xl.Quit()
- */
- ; Various commands
- ;----------------------------------------------------
- xl.sheets(1).Range("A:A").Copy ; copies A column to clipboard
- xl.sheets(1).Range("D:D").PasteSpecial(-4163) ; Pastes values only
- xl.sheets(1).Range("A1:H1").Select ; selects the specified range
- xl.sheets(1).Range("A:A").NumberFormat := "@" ; changes column format to text
- xl.sheets(1).Range("F1").Formula := "=SUM(C2:C4)" ; inserts formula
- xl.sheets(1).Range("F5:G9").Interior.ColorIndex := 56 ; fills a range of cells with color
- xl.sheets(2).Select ; activates sheet 2
- xl.sheets("Sheet2").Select ; activates sheet 2
- xl.Quit() ; Closing Excel window
- xl := ""
- ;----------------
- ; HOTKEYS
- ;----------------
- f2::Reload
Advertisement
Add Comment
Please, Sign In to add comment