danrim

AHK COM Excel examples

Aug 3rd, 2020
227
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.92 KB | None | 0 0
  1. #SingleInstance, force
  2.  
  3. /*
  4. xl := ComObjCreate("Excel.Application") ; creating Excel workbook handle
  5. xl.Visible := true ; making window visible
  6.  
  7.  
  8.  
  9. ; Cheching if there is an active window
  10. ;----------------------------------------------------
  11.  
  12. try xl := ComObjActive("Excel.Application")
  13.  
  14. catch {
  15. MsgBox, no existing excel object. need to create one.
  16. xl := ComObjCreate("Excel.Application")
  17. xl.Visible := true
  18. }
  19.  
  20. MsgBox, % IsObject(xl) ;this function let us check if there is a connection to an object (1 - yes, 0 - no)
  21. */
  22.  
  23. /*;Choosing a file from a dialog box
  24. ;----------------------------------------------------
  25.  
  26. FileSelectFile, MySelectedFile ;filepath is stored in a variable
  27. Sleep, 300
  28. MsgBox, We will open this file: %MySelectedFile%
  29.  
  30. try xl := ComObjActive("Excel.Application")
  31.  
  32. catch {
  33. MsgBox, no existing excel object. need to create one.
  34. xl := ComObjCreate("Excel.Application")
  35. xl.Visible := true
  36. }
  37.  
  38. xl.workbooks.open(MySelectedFile)
  39. xl.visible := 1
  40.  
  41. */
  42.  
  43. /*;Accessing worksheet without opening it
  44. ;----------------------------------------------------
  45.  
  46.  
  47. FilePath := "\\VEBP1359\COGNJK$\MyPersonalData\Desktop\AHK training\Lesson 4\Book1.xlsx"
  48. xl := ComObjGet(FilePath)
  49. value_from_worksheet := xl.sheets(1).Range("A1").Value
  50. value_from_worksheet2 := xl.sheets(1).Range("C3").Value
  51.  
  52. MsgBox, Selected values are: %value_from_worksheet% and %value_from_worksheet2%
  53. */
  54.  
  55.  
  56. /*;Adding values
  57. ;----------------------------------------------------
  58.  
  59. contract := 123456
  60. xl := ComObjCreate("Excel.Application") ; creating Excel workbook handle
  61. xl.Visible := true
  62.  
  63. FilePath := "\\VEBP1359\COGNJK$\MyPersonalData\Desktop\AHK training\Lesson 4\Book1.xlsx"
  64. xl.workbooks.open(FilePath)
  65.  
  66. Sleep 400
  67. xl.sheets(1).Range("G1").Value := contract
  68. Sleep 400
  69. xl.sheets(1).Range("G2").Value := "5555555"
  70.  
  71. MsgBox, Values have been added.
  72.  
  73. xl.Quit()
  74. xl := ""
  75.  
  76. */
  77.  
  78. /*;Retrieving values from columns
  79. ;----------------------------------------------------
  80.  
  81. FilePath := "\\VEBP1359\COGNJK$\MyPersonalData\Desktop\AHK training\Lesson 4\Book1.xlsx"
  82. try xl := ComObjActive("Excel.Application")
  83. catch {
  84. MsgBox, no existing excel object. need to create one.
  85. xl := ComObjCreate("Excel.Application")
  86. xl.Visible := true
  87. xl.workbooks.open(FilePath)
  88. }
  89.  
  90. xl.workbooks.open(FilePath)
  91. xl.Visible := true
  92.  
  93. if (IsObject(xl) = 1)
  94. MsgBox, Object is created.
  95. else
  96. MsgBox, There is no object.
  97.  
  98.  
  99. ;~ while (xl.sheets(1).Range("A" . A_Index).Value != "")
  100. ;~ {
  101. ;~ value := xl.sheets(1).Range("A" . A_Index).Value
  102. ;~ MsgBox, %value%
  103. ;~ }
  104.  
  105. ;~ MsgBox, No more entries in the column
  106.  
  107.  
  108. ;storing value in a variable
  109. while (xl.sheets(1).Range("A" . A_Index).Value != "")
  110. {
  111. value .= xl.sheets(1).Range("A" . A_Index).Value "|"
  112. MsgBox, %value%
  113. }
  114. */
  115.  
  116.  
  117. /*; Adding workbooks and worksheets, saving document
  118. ;-----------------------------------------------------------
  119. xl := ComObjCreate("Excel.Application") ; creating Excel workbook handle
  120. xl.Visible := true
  121. xl.workbooks.add()
  122. xl.sheets.add()
  123.  
  124. WhereToSave := "\\VEBP1359\COGNJK$\MyPersonalData\Desktop\AHK training\Lesson 4\new.xlsx"
  125. MsgBox, File will be saved here: %WhereToSave%
  126.  
  127. xl.ActiveWorkbook.SaveAs(WhereToSave)
  128. Sleep 500
  129.  
  130. xl.Quit()
  131.  
  132. */
  133.  
  134. ; Various commands
  135. ;----------------------------------------------------
  136.  
  137. xl.sheets(1).Range("A:A").Copy ; copies A column to clipboard
  138.  
  139. xl.sheets(1).Range("D:D").PasteSpecial(-4163) ; Pastes values only
  140.  
  141. xl.sheets(1).Range("A1:H1").Select ; selects the specified range
  142.  
  143. xl.sheets(1).Range("A:A").NumberFormat := "@" ; changes column format to text
  144.  
  145. xl.sheets(1).Range("F1").Formula := "=SUM(C2:C4)" ; inserts formula
  146.  
  147. xl.sheets(1).Range("F5:G9").Interior.ColorIndex := 56 ; fills a range of cells with color
  148.  
  149. xl.sheets(2).Select ; activates sheet 2
  150. xl.sheets("Sheet2").Select ; activates sheet 2
  151.  
  152. xl.Quit() ; Closing Excel window
  153. xl := ""
  154.  
  155.  
  156.  
  157.  
  158. ;----------------
  159. ; HOTKEYS
  160. ;----------------
  161. f2::Reload
Advertisement
Add Comment
Please, Sign In to add comment