MS Office COM Basics

Helpful script writing tricks and HowTo's
kon
Posts: 1760
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

07 Oct 2016, 19:54

Thanks. I appreciate it. :)
okl

Re: MS Office COM Basics

09 Oct 2016, 04:04

I'm trying to come up with something that will copy and paste certain unique ID numbers from each cell in Excel to search on the company database and then printing the info displayed from the company webpage; i.e.
i) copy cell A1 in excel
ii) paste cell A1 into "search" field of company database
iii) print data on displayed page of company database

i have about 100+ unique numbers in Excel... is there anyway to edit and automate mouse macro to "copy cell A1" for the first loop, "copy cell A2" for the second loop, "copy cell A3" for the 3rd loop... etc - and then run the mouse click macro in steps (ii) or (iii)?
kon
Posts: 1760
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

09 Oct 2016, 09:31

Yes, that is certainly possible. There are different ways to code it depending on how you want to trigger the macro and whether you have the workbook open already or not.
This, for example, opens a workbook and will show you the next value each time you press F3:

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus



Here's another way to do it. This will look for the last non-blank cell in column A. Then it loops though each cell in column A starting at cell A1 all the way to the last non-blank cell.

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus

okl

Re: MS Office COM Basics

10 Oct 2016, 12:22

Mr Kon!

That is so very kind of you to reply to my post!

Thank you!!

I am really new to this auto hot key thing because I was looking around to solve the upcoming problem that i am going to have as described (a new project is coming my way).

After going through your script in the first post- which i struggled badly because 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 thought i'd just post the main problem i think i might face in coming up with an ahk-script for it!!

(it took me about 2hrs over 2 days to figure that out hahaha)

i certainly was not expecting a reply from my first post on a programming/scripting/coding site!

and it certainly looks like you have posted a workable solution... between my work, read and sleep time- i'll certainly be spending some time on your script to understand and try it out on my laptop--- esp that loop through rows... looks so cool.

thank you so much again!


as a side note, during one of my day-dreaming sessions today- i decided that it will probably be better to use "alt-tab" and "tab" to get to the click points rather than using the mouse to get there... i guess its more reliable given that the number of alt-tab and tab counts is more precise...

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?

finally, thank you so much yet again!! it is just so much more fun to work with something that might really solve a close-to-me problem than running through exercises and finding different solutions haha

cheers!!!
kon
Posts: 1760
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

11 Oct 2016, 09:03

No Problem! :)

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
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: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?
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.
User avatar
Joe Glines
Posts: 442
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: MS Office COM Basics

11 Oct 2016, 18:46

Just an FYI- this coming Tuesday, Oct 18th we'll be focusing the AutoHotkey webinar on driving Excel via COM. We hope to see you there!

You can register for the event at the event here: https://jszapp.com/event/live-ahk-webinar-20161018/
payback87
Posts: 13
Joined: 31 Aug 2016, 01:55

Re: MS Office COM Basics

12 Oct 2016, 02:07

And to add to the answer below: Since you asked what would be useful for an Office worker - well it's that, really. Office VBA. If you work a lot with Excel at work or even at home - go for writing macros in VBA. Excel is a good option since it has a macro recorder that can sometimes help you find out what commands to use on which occasions. Now for starters, just to get a hang of it and see if you like programming and enjoy it, you have two good tools: VBA and AutoHotKey. You can even control Office VBA via AutoHotKey as you know (though it might be a bit confusing to do that due to the different syntaxes - esp. if you are a complete beginner).

VBA is what set me up to get into programming a little more. I'm also only doing rudimentary stuff and I don't have a career goal to become a programmer. But I've done some things in Excel VBA which is relatively straight forward, then in Outlook - which is a bit harder if you don't really know about OOP in the first place. Then I did a little bit of Python introductory courses and especially PyGame. This helped me understand objects and pointers / references to those objects much more. Then I wanted to try AutoHotKey, which enhanced my knowledge in programming even further. After that I wanted to use a Microsoft DLL that had a static method. So I got myself a brief introduction on how to write a wrapper for that class in C# which I can then implement in AutoHotKey via the CLR Library.
Then, after a while, I thought about re writing a AHK script with classes and now it's come full circle and I'm really starting to think in an object oriented way in VBA, too. Suddenly even Outlook VBA is relatively easy for me. I can get things to work - which doesn't mean I'm using the best, most clever way all the times. But that's not what you should aim for in the beginning.
Only yesterday my colleague asked me to help him with an Excel file. And in five minutes I wrote up a short macro by hand which used the shapes collection (that I was sure must exist but didn't really know that it was called "shapes" - but I found that out via the object explorer, which, in the past had always seemed like a mystic book to me that I would certainly never be able to understand... :)) - and there you go it works like a charm.

Long story short: for Office people VBA is certainly a good start. First and foremost because it does something useful for you that can help you increase your efficiency, it's not too hard yet still gives you many options that more complex languages do, albeit not always in the best possible way. But getting started with it helped me to find my "love" for programming and now I try whatever I feel like.

kon wrote:
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?
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

Re: MS Office COM Basics

16 Oct 2016, 10:41

not bad! not bad! i am quite pleased with myself hehe

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).

to be frank, i was wondering whether you forgot to "cut and paste"; i.e. until i got it to "send", i wasnt sure if cellvalue managed to capture the content in the cell because i was used to seeing the dotted line for a copied cell- dealing with internal doubts lol.

ok this is so cool haha; now let's see if i can understand script two... hopefully i get to the part where i can print the whole darn list with one key stroke hehe.

one more thing- does AHK come together with its own "mouse coordinate tracking program"? i mean, it did take me awhile to find out a program to retrieve the correct coordinates.

thanks alot Mr. Kon!!


and Payback87; thanks for the advice! i work ALOT with MS Word and Excel- and it has been in my mind for the longest time to automate certain reports; i.e.
- create a report template in MS Word
- create tables in MS Excel for auto-computation
- at the click of a button/s, port over the excel tables relevant to the word template
- the user edits the report as he deems necessary

basically, i'm trying to reduce the number of "calculations" and "copy and paste" work from excel to word.

so yeah, VBA is something i look forward to learning... now with AHK- wow, i do see quite a bit of room for increased productivity.

now i need to spend time working on both!!

then convince my IT dept to enable macros and apply to download AHK as a office productivity tool LOL- security reasons.


Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus

kon
Posts: 1760
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

17 Oct 2016, 11:03

okl wrote:does AHK come together with its own "mouse coordinate tracking program"?
Window Spy. Right-click on the tray icon of a running AHK script and select "Window Spy."

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).
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.

You could try Send, {Tab} and Send, +{Tab} to focus different controls and move around your program.

Maybe try using ControlClick, ControlSend, ControlSetText, etc. instead of mouse clicks and Send. This works with some windows (depending on the program it might not work). The Control-type commands are able to directly interact with controls. ie: You don't need to use coordinates, you just need the control name as shown by Window Spy (if Window Spy does not show a control name, this probably won't work).
okl

Re: MS Office COM Basics

23 Oct 2016, 03:35

ah yes, window spy! it occurred to me a few hours after my last post...

its all going okay for now.. what's the difference between +{Tab} and {Tab}?

i tried going through the AHK help for this but there's just too much entries!

and as for the "ControlSend", ControlClick etc... it seems like a good idea to be able to select the specific control on the program, but unfortunately i can't find such control boxes. i mean, i tried using on firefox, but even the URL box or the search box doesnt have a control for that.


the codes you posted are pretty cool; i've managed to get both working on my own PC using excel and firefox as proxies... i can't wait to test it proper!
okl

Re: MS Office COM Basics

29 Oct 2016, 14:07

Mr. Kon,

just wanted to let you know that i've already started automating some of work using the code you provided and adapting portions of it to work!

feels really good to automate a good portion of data entry work!

here's a 3rd script i came up with that merges both your 1st and 2nd script that allows it to loop according to the defined range [CellA1 (user defined) to the last filled cell in Column A].
i'll try out this script to automate more parts of my work soon! it's a lot of "if this, then do [that], [this], [that]."

i hope it does make you a little happy to see someone taking your help and doing more with it! =)
[i am a senior at work and it does make me happy to see the junior people doing the same =), so i thought i would let you know also]

so many thanks for your help!

a few questions i seek your assistance with;
i) what is "MyCell"? i can see that "MyRange" was defined already... but where in the world is "MyCell" defined in your script?

ii) from the following code, note that i used "imagesearch" to look for buttons on the webpage itself... but this method is not exactly reliable because for some reason, i dont manage to "locate" the icon everytime.

this is also a problem because from the 3rd script example that you see, i will be adding many more "Cell Values" and adding if functions and subsequent actions for each different Cell Value; i.e. if CellValue >0, do [this], [that]... if Cellvalue2 >0, do [this], [that] etc.

i would like to use webpage elements to locate certain buttons / checkboxes / dropdown menus on the webpage itself for greater reliability to click on it- any idea where i can get some examples for this? or are there other alternatives from using webpage elements?
(while i havent tried the "controlget" commands, i really dont think the elements on my company webapge belong to "Windows Control"; i've found some AHK examples on the forum and tested out those codes- those work on "Windows" windows, not "Webpage" elements- but do let me know if im mistaken)

finally, many many thanks again for providing help and pointing me in the right direction!!

cheers!!


Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus

okl

Re: MS Office COM Basics

29 Oct 2016, 15:15

oops, i think this code makes better sense cos the previous one didnt have options should the IF statement evaluate to false.

cheers

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus

kon
Posts: 1760
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

30 Oct 2016, 08:56

Nice work, you're learning fast. :thumbup:

i) MyCell is populated by the for-loop: for MyCell, in MyRange
See section "5.2 Enumerating Collections" of this tutorial and for-loops in the docs for a bit more info.
"MyRange" is a collection of items (cells). A for-loop can be used to get each item in the Range one at at time. In each iteration of the for-loop, "MyCell" will contain a reference to the current cell.

ii) I wasn't aware that you were trying to interact with a webpage. I would not have suggested using the Control-type commands had I known.

If ImageSearch is a bit unreliable, you could try looping until the image is found. ie: If the image is not found, repeat the ImageSearch.

But, the quickest and most reliable solution will probably use Internet Explorer and COM:
TheDewd
Posts: 762
Joined: 19 Dec 2013, 11:16
Location: USA

Re: MS Office COM Basics

03 Nov 2016, 13:18

@kon,

Would you be able to help me figure out why Excel's CheckSpelling method is not looking at the CustomDictionary parameter that I specified?

It is only looking in the dictionaries that are available and selected in the Custom Dictionary options window. It doesn't seem to be using the custom dictionary file at the specified location.

Code: [Select all] [Download] (Untitled.ahk)GeSHi © Codebox Plus

; .CheckSpelling(Word, CustomDictionary, IgnoreUppercase)
MsgBox, % ComObjActive("Excel.Application").CheckSpelling("Test123", "C:\MyDict.dic", ComObj(0xB, 0))

Application.CheckSpelling Method (Excel)
https://msdn.microsoft.com/en-us/library/office/ff840059.aspx
kon
Posts: 1760
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

03 Nov 2016, 13:57

It looks like spell check ignores words that contain digits. I just tested it and this behavior is consistent with the spell check when it is run manually.

Code: [Select all] [Download] GeSHi © Codebox Plus

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)
kon
Posts: 1760
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

03 Nov 2016, 14:11

I changed the file path to one that was convenient for me, so be aware that may be confusing things...

Also, it looks like you can change the spell check options with the SpellingOptions object.

Code: [Select all] [Download] GeSHi © Codebox Plus

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
TheDewd
Posts: 762
Joined: 19 Dec 2013, 11:16
Location: USA

Re: MS Office COM Basics

03 Nov 2016, 14:14

Code: [Select all] [Download] (Untitled.ahk)GeSHi © Codebox Plus

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'

This always returns '0' for me, even though the text exists in the dictionary file. I have no idea what the issue is... Maybe the word already existed somehow in your other dictionaries??
kon
Posts: 1760
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

03 Nov 2016, 16:15

Maybe the word already existed somehow in your other dictionaries??
No, I removed the word from the custom dictionary and it goes back to being not found.

I can't explain it. It seems to ignore the file path...

I need to have A_Desktop "\MyDict.dic" set as a custom dictionary in the Custom Dictionaries dialog box.
If it is not one of the dictionaries listed there I get 0 too.

Furthermore, if the word is in A_Desktop "\AnotherDict.dic" but not in A_Desktop "\MyDict.dic", and A_Desktop "\AnotherDict.dic" is listed in the Custom Dictionaries dialog box, CheckSpelling returns true (as if the word was in A_Desktop "\MyDict.dic").

I tried setting the SpellingOptions.UserDict, but it doesn't seem to stick.

This thread uses Word for dealing with custom dictionaries: http://www.mrexcel.com/forum/excel-ques ... onary.html
I opened Word, and it uses the same dictionaries (listed in the Custom Dictionaries dialog box). Word also provides more methods for dealing with dictionaries. (See Dictionaries Object etc.) So that's probably the way to proceed.
TheDewd
Posts: 762
Joined: 19 Dec 2013, 11:16
Location: USA

Re: MS Office COM Basics

03 Nov 2016, 16:26

kon wrote:Word also provides more methods for dealing with dictionaries. (See Dictionaries Object etc.) So that's probably the way to proceed.
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.

So I guess I need to find a way to temporarily add the dictionary to the Custom Dictionaries window while the function is being executed... Not sure if that's the best option... From MSDN it seems that you would be able to point to any file. Oh well... :(

Code: [Select all] [Expand] [Download] (Untitled.ahk)GeSHi © Codebox Plus

hugin
Posts: 11
Joined: 17 Aug 2016, 01:33

Re: MS Office COM Basics

12 Nov 2016, 19:36

Hello!

Kudos to OP for making this, I have some experience with VB and VBA, and have made several useful Excel - Form based (Read: GUI) Excel Program for my work and also for my main exam in Offshore Search & Oil Drilling course. (Mud/ pipe - Weight program for the full pipeline including BOP and Xmas tree, for those who knows what that is)
Had a lot of fun while learning VBA and pretty quickly found out not many had any experience with Excel VBA over any novice level when seeking help with the code, even they were guru's in tutoring the Excel Sheet itself.
So back then I had to tutor myself, with some luck at least. Not to boast myself, but to explain back then (Mid. 90's) many did not use VBA as a tool to really enhance the great tool it can be to make great Visual, tailored "calculators" for their own businesses. A pity really.

(Even my boss back then, did not see the use of automating manually labor tailoring a sheet roof with screws and bibs and bobs we could do in 30-40 min typing in everything in my customized Excel program. Manually it took 4-5 hours to do on paper, sigh...)

With VBA, and AHK the automation can go in to the infinity ( ∞ ) and beyond!

Anyway, thank you for setting this up, and I will dig into this asap, as I am totally hooked on AHK now also! :bravo:

Erik

Return to “Tutorials”

Who is online

Users browsing this forum: No registered users and 3 guests