Re: MS Office COM Basics
Posted: 07 Oct 2016, 19:54
Thanks. I appreciate it.
Let's help each other out
https://www.autohotkey.com/boards/
Code: Select all
WorkbookPath := A_ScriptDir "\MyWorkbook.xlsx" ; <-- Change this to the path of your workbook
xlApp := ComObjCreate("Excel.Application") ; Create an Excel application
xlApp.Visible := true ; Optional. Just remember to close Excel at the end if it is not visible
MyWorkbook := xlApp.Workbooks.Open(WorkbookPath) ; Open the workbook
Row := 1 ; This var will keep track of the row number
return ; End of Auto-Execute section
F3:: ; Press F3 to get the next cell
CellValue := MyWorkbook.Worksheets(1).Cells(Row, 1).Value ; Get the value of the current cell
if (CellValue = "") ; If this cell is blank...
{
MsgBox, 48, Blank Cell, The macro has reached a blank cell. Resetting to cell A1.
Row := 1
return
}
MsgBox, % "The value of the current cell is " CellValue
; Do mouse-click macro stuff here. Ex:
;~ Click 44, 55
;~ Send, % CellValue
Row++ ; Add 1 to Row
return
^Esc:: ; Ctrl+Esc closes Excel and this script
MyWorkbook.Close() ; Close the workbook
xlApp.Quit() ; Close Excel
ExitApp
Code: Select all
WorkbookPath := A_ScriptDir "\MyWorkbook.xlsx" ; <-- Change this to the path of your workbook
xlApp := ComObjCreate("Excel.Application") ; Create an Excel application
xlApp.Visible := true ; Optional. Just remember to close Excel at the end if it is not visible
MyWorkbook := xlApp.Workbooks.Open(WorkbookPath) ; Open the workbook
CellA1 := xlApp.Cells(1, 1) ; Store a reference to cell A1
; Find the last cell in Column 'A' that is not blank.
; Start at the last cell in Column 'A' and look upwards for a non-blank cell
LastCell := xlApp.Cells(xlApp.Rows.Count, 1).End(-4162) ; xlUp = -4162
MyRange := xlApp.Range(CellA1, LastCell) ; Store a refence to the Range A1:LastCell
for MyCell, in MyRange ; For each cell in the range...
{
MsgBox, % "The value of cell " MyCell.Address " is " MyCell.Text
; Do mouse-click macro stuff here. Ex:
;~ Click 44, 55
;~ Send, % MyCell.Text
}
MyWorkbook.Close() ; Close the workbook
xlApp.Quit() ; Close Excel
You don't necessarily need that at the start of every shortcut. At a minimum a script needs to get a reference to a COM object at some point prior to using it.okl wrote:i didn't know that [xlApp := ComObjActive("Excel.Application") ; get a reference to the active Excel Application object] had to be in the first row of every shortcut
I don't think I'm the best person to answer this, but in my experience you just have to DO programming. Find something that interests you and would be useful and try to make it. It doesn't matter if you fail, or ask for help, or need to re-examine the initial approach... It matters less WHAT you are programming. It's more about the fact that no matter what you choose to spend your time on, there are skills that you will learn that will help you in the future.okl wrote:and as a question- if i wanted to learn about coding/programming- where should i start? i've read a lot about python, ruby and what not... but somehow, i don't think they make much sense for normal office workers trying to make things more efficient?
kon wrote:I don't think I'm the best person to answer this, but in my experience you just have to DO programming. Find something that interests you and would be useful and try to make it. It doesn't matter if you fail, or ask for help, or need to re-examine the initial approach... It matters less WHAT you are programming. It's more about the fact that no matter what you choose to spend your time on, there are skills that you will learn that will help you in the future.okl wrote:and as a question- if i wanted to learn about coding/programming- where should i start? i've read a lot about python, ruby and what not... but somehow, i don't think they make much sense for normal office workers trying to make things more efficient?
Code: Select all
WorkbookPath := A_ScriptDir "\Book2.xlsx" ; <-- Change this to the path of your workbook
xlApp := ComObjCreate("Excel.Application") ; Create an Excel application
xlApp.Visible := true ; Optional. Just remember to close Excel at the end if it is not visible
MyWorkbook := xlApp.Workbooks.Open(WorkbookPath) ; Open the workbook
Row := 1 ; This var will keep track of the row number
return ; End of Auto-Execute section
F3:: ; Press F3 to get the next cell
CellValue := MyWorkbook.Worksheets(1).Cells(Row, 1).Value ; Get the value of the current cell
if (CellValue = "") ; If this cell is blank...
{
MsgBox, 48, Blank Cell, The macro has reached a blank cell. Resetting to cell A1.
Row := 1
return
}
MsgBox, % "The value of the current cell is " CellValue
mouseclick, left, 3000, 345 ;~ Click 44, 55
sleep, 2000
mouseclick, left, 1075, 148
sleep, 2000
mouseclick, left, 2000, 136
send, % CellValue ;~ Send, % CellValue
send, {Enter}
Row++ ; Add 1 to Row
return
^Esc:: ; Ctrl+Esc closes Excel and this script
MyWorkbook.Close() ; Close the workbook
xlApp.Quit() ; Close Excel
ExitApp
Window Spy. Right-click on the tray icon of a running AHK script and select "Window Spy."okl wrote:does AHK come together with its own "mouse coordinate tracking program"?
Monitors with different resolutions, among other things, will give you different coordinates. Also see CoordMode; coordinates can be relative to the window, screen, or client area.okl wrote:it took me awhile because the "mouseclick, left, xyz,abc" seems to be on different settings for different macro recorders- and i think it doesnt help that my 2 screens are on different resolutions (ultimately, i used pulover's macro creator to get the correct coordinates and somehow it still doesnt work quite right until i entered 3 mouseclicks... hmm).
Code: Select all
^r::
reload
return
#p::
pause
return
#'::
WorkbookPath := A_ScriptDir "\Book2.xlsx" ; <-- Change this to the path of your workbook
xlApp := ComObjCreate("Excel.Application") ; Create an Excel application
xlApp.Visible := true ; Optional. Just remember to close Excel at the end if it is not visible
MyWorkbook := xlApp.Workbooks.Open(WorkbookPath) ; Open the workbook
Row := 1 ; This var will keep track of the row number
; this stored reference need not be A1, it can be any designated 'first cell' in the range
CellA1 := xlApp.Cells(1, 1) ; Store a reference to cell A1
; Find the last cell in Column 'A' that is not blank.
; Start at the last cell in Column 'A' and look upwards for a non-blank cell
LastCell := xlApp.Cells(xlApp.Rows.Count, 1).End(-4162) ; xlUp = -4162
MyRange := xlApp.Range(CellA1, LastCell) ; Store a refence to the Range A1:LastCell
for MyCell, in MyRange ; For each cell in the range...
{
;store as many cellvalues as necessary for the subsequent actions
CellValue := MyWorkbook.Worksheets(1).Cells(Row, 1).Value ; Get the value of the current cell
CellValue2 := MyWorkbook.Worksheets(1).Cells(Row, 2).Text ; Get the value of the current cell
CellValue3 := MyWorkbook.Worksheets(1).Cells(Row, 3).Text ; Get the value of the current cell
CellValue4 := MyWorkbook.Worksheets(1).Cells(Row, 4).Text ; Get the value of the current cell
CellValue5 := MyWorkbook.Worksheets(1).Cells(Row, 5).Text ; Get the value of the current cell
MsgBox, % "The value of the current cells are " CellValue CellValue2 CellValue3 CellValue4 CellValue5
if Cellvalue > 0
{
Coordmode, Pixel, Screen
imagesearch, FoundX, FoundY,915,1400,2500,1790,C:\Users\okl\Desktop\ahk scripts\pics\GMfinder.png
if ErrorLevel = 2
MsgBox Could not conduct the search.
else if ErrorLevel = 1
MsgBox, Icon could not be found on the screen.
else
MsgBox,4,, The icon was found at %FoundX%x%FoundY%. ,1
sleep 1000
Coordmode, mouse, screen
mousemove, FoundX+5,FoundY+5
sleep 1000
mousemove, 220, 740
mouseclick, left
sleep 1000
mousemove, 1155, 125
mouseclick, left
send, {tab}
send, hello
Coordmode, Pixel, Screen
imagesearch, FoundX, FoundY,915,1400,2500,1790,C:\Users\okl\Desktop\ahk scripts\pics\GMfinder.png
if ErrorLevel = 2
MsgBox Could not conduct the search.
else if ErrorLevel = 1
MsgBox, Icon could not be found on the screen.
else
MsgBox,4,, The icon was found at %FoundX%x%FoundY%. ,1
sleep 1000
Coordmode, mouse, screen
mousemove, FoundX+5,FoundY+5
mousemove, 220, 740
mouseclick, left
sleep 1000
send, % Cellvalue hit!
sleep 1000
mousemove, 1155, 125
mouseclick, left
sleep 1000
send, {tab}
send, % Cellvalue
msgbox, 4,, % "the end of " row ,3
Row++ ; adds 1 row
}
}
MyWorkbook.Close() ; Close the workbook
xlApp.Quit() ; Close Excel
Code: Select all
^r::
reload
return
#p::
pause
return
#'::
WorkbookPath := A_ScriptDir "\Book2.xlsx" ; <-- Change this to the path of your workbook
xlApp := ComObjCreate("Excel.Application") ; Create an Excel application
xlApp.Visible := true ; Optional. Just remember to close Excel at the end if it is not visible
MyWorkbook := xlApp.Workbooks.Open(WorkbookPath) ; Open the workbook
Row := 1 ; This var will keep track of the row number
; this stored reference need not be A1, it can be any designated 'first cell' in the range
CellA1 := xlApp.Cells(1, 1) ; Store a reference to cell A1
; Find the last cell in Column 'A' that is not blank.
; Start at the last cell in Column 'A' and look upwards for a non-blank cell
LastCell := xlApp.Cells(xlApp.Rows.Count, 1).End(-4162) ; xlUp = -4162
MyRange := xlApp.Range(CellA1, LastCell) ; Store a refence to the Range A1:LastCell
for MyCell, in MyRange ; For each cell in the range...
{
;store as many cellvalues as necessary for the subsequent actions
CellValue := MyWorkbook.Worksheets(1).Cells(Row, 1).Value ; Get the value of the current cell
CellValue2 := MyWorkbook.Worksheets(1).Cells(Row, 2).Text ; Get the value of the current cell
CellValue3 := MyWorkbook.Worksheets(1).Cells(Row, 3).Text ; Get the value of the current cell
CellValue4 := MyWorkbook.Worksheets(1).Cells(Row, 4).Text ; Get the value of the current cell
CellValue5 := MyWorkbook.Worksheets(1).Cells(Row, 5).Text ; Get the value of the current cell
MsgBox, % "The value of the current cells are " CellValue CellValue2 CellValue3 CellValue4 CellValue5
if Cellvalue > 0
{
Coordmode, Pixel, Screen
imagesearch, FoundX, FoundY,915,1400,2500,1790,C:\Users\okl\Desktop\ahk scripts\pics\GMfinder.png
if ErrorLevel = 2
MsgBox Could not conduct the search.
else if ErrorLevel = 1
MsgBox, Icon could not be found on the screen.
else
MsgBox,4,, The icon was found at %FoundX%x%FoundY%. ,1
sleep 1000
Coordmode, mouse, screen
mousemove, FoundX+5,FoundY+5
sleep 1000
mousemove, 220, 740
mouseclick, left
sleep 1000
mousemove, 1155, 125
mouseclick, left
send, {tab}
send, hello
Coordmode, Pixel, Screen
imagesearch, FoundX, FoundY,915,1400,2500,1790,C:\Users\okl\Desktop\ahk scripts\pics\WBTrans.png
if ErrorLevel = 2
MsgBox Could not conduct the search.
else if ErrorLevel = 1
MsgBox, Icon could not be found on the screen.
else
MsgBox,4,, The icon was found at %FoundX%x%FoundY%. ,1
sleep 1000
Coordmode, mouse, screen
mousemove, FoundX+5,FoundY+5
mousemove, 220, 740
mouseclick, left
sleep 1000
send, % Cellvalue hit!
sleep 1000
mousemove, 1155, 125
mouseclick, left
sleep 1000
send, {tab}
send, % Cellvalue
msgbox, 4,, % "the end of " row ,3
Row++ ; adds 1 row
}
if Cellvalue2 > 0
{
Coordmode, Pixel, Screen
imagesearch, FoundX, FoundY,915,1400,2500,1790,C:\Users\okl\Desktop\ahk scripts\pics\GMfinder.png
if ErrorLevel = 2
MsgBox Could not conduct the search.
else if ErrorLevel = 1
MsgBox, Icon could not be found on the screen.
else
MsgBox,4,, The icon was found at %FoundX%x%FoundY%. ,1
sleep 1000
Coordmode, mouse, screen
mousemove, FoundX+5,FoundY+5
sleep 1000
mousemove, 220, 740
mouseclick, left
sleep 1000
mousemove, 1155, 125
mouseclick, left
send, {tab}
send, % Cellvalue2
Coordmode, Pixel, Screen
imagesearch, FoundX, FoundY,915,1400,2500,1790,C:\Users\okl\Desktop\ahk scripts\pics\WBTrans.png
if ErrorLevel = 2
MsgBox Could not conduct the search.
else if ErrorLevel = 1
MsgBox, Icon could not be found on the screen.
else
MsgBox,4,, The icon was found at %FoundX%x%FoundY%. ,1
sleep 1000
Coordmode, mouse, screen
mousemove, FoundX+5,FoundY+5
mousemove, 220, 740
mouseclick, left
sleep 1000
send, % Cellvalue2
sleep 1000
mousemove, 1155, 125
mouseclick, left
sleep 1000
send, {tab}
send, % Cellvalue2
msgbox, 4,, % "the end of " row ,3
Row++ ; adds 1 row
}
; for the final "else", all the values in the row should be run through first
; after the last cell value has been evaluated to be zero
; the script will just move on to the next row (this should be the final opt for the row)
; in Excel Nested IF function terms, this is the final "if false" entry
; yes, we are creating nested IFs again!
; might have to create one for those hitting the critical criteria as the final outcome is not "EE self rectify"
else
{
msgbox, 4,, % "the end of " row ,3
Row++ ; adds 1 row
}
} ; end of mycell
MyWorkbook.Close() ; Close the workbook
xlApp.Quit() ; Close Excel
Code: Select all
; .CheckSpelling(Word, CustomDictionary, IgnoreUppercase)
MsgBox, % ComObjActive("Excel.Application").CheckSpelling("Test123", "C:\MyDict.dic", ComObj(0xB, 0))
Code: Select all
MsgBox, % ComObjActive("Excel.Application").CheckSpelling("Test", A_Desktop "\MyDict.dic", ComObj(0xB, 0)) ; -1
MsgBox, % ComObjActive("Excel.Application").CheckSpelling("Testa", A_Desktop "\MyDict.dic", ComObj(0xB, 0)) ; 0 (word is not in MyDict.dic)
MsgBox, % ComObjActive("Excel.Application").CheckSpelling("Testb", A_Desktop "\MyDict.dic", ComObj(0xB, 0)) ; -1 (word is in MyDict.dic)
MsgBox, % ComObjActive("Excel.Application").CheckSpelling("Test123", A_Desktop "\MyDict.dic", ComObj(0xB, 0)) ; -1 (word is not in MyDict.dic, but words containing digits always seem to return true)
Code: Select all
ObjExcel := ComObjActive("Excel.Application")
ObjExcel.SpellingOptions.IgnoreMixedDigits := 0
MsgBox, % ObjExcel.CheckSpelling("Test", A_Desktop "\MyDict.dic", ComObj(0xB, 0)) ; -1
MsgBox, % ObjExcel.CheckSpelling("Testa", A_Desktop "\MyDict.dic", ComObj(0xB, 0)) ; 0 (word is not in MyDict.dic)
MsgBox, % ObjExcel.CheckSpelling("Testb", A_Desktop "\MyDict.dic", ComObj(0xB, 0)) ; -1 (word is in MyDict.dic)
MsgBox, % ObjExcel.CheckSpelling("Test123", A_Desktop "\MyDict.dic", ComObj(0xB, 0)) ; 0 (word is not in MyDict.dic and SpellingOptions.IgnoreMixedDigits has been set to false
Code: Select all
If (FileExist(A_Desktop "\MyDict.dic")) {
FileDelete, % A_Desktop "\MyDict.dic"
}
FileAppend, Testb, % A_Desktop "\MyDict.dic"
MsgBox, % ObjExcel.CheckSpelling("Testb", A_Desktop "\MyDict.dic", ComObj(0xB, 0)) ; Always returns '0'
No, I removed the word from the custom dictionary and it goes back to being not found.Maybe the word already existed somehow in your other dictionaries??
Thanks! I'm trying to create as many functions for Excel as I can, working my way through them one by one... I just didn't want to include a function that is not working properly.kon wrote:Word also provides more methods for dealing with dictionaries. (See Dictionaries Object etc.) So that's probably the way to proceed.
Code: Select all
; ==============================================================================
; XlCheckSpell() Handle, Word [, CustomDict, IgnoreUpper]
; ==============================================================================
; Returns True if the word is found in one of the dictionaries; otherwise False.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Word: The word you want to check.
;
; CustomDict: The filename of the custom dictionary to use if the word isn't
; found in the main dictionary. If omitted, the currently specified dictionary
; is used.
;
; IgnoreUpper: True/False. Specifies if words in all uppercase are ignored.
;
; Links
; ------------------------------------------------------------------------------
; Application.CheckSpelling Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff840059.aspx
; ==============================================================================
XlCheckSpell(Handle, Params*) {
return Handle.CheckSpelling(Params*)
}