MS Office COM Basics

Helpful script writing tricks and HowTo's
SmokeyTBear
Posts: 4
Joined: 23 Mar 2015, 18:06

Re: MS Office COM Basics

03 Dec 2016, 16:11

Kon: You are the guru on the mountain! I cant say enough how much I appreciate this!

The code so far works well, I thank you! I have a few questions:

How should I approach setting up the body of the email with either the pivot table itself, or a block of text that is the warnings array, with warnings[n].when in one column, and warnings[n].what in the other? Edit: Think I solved this, but in my sloppy way. (Code Below)

I am wondering, I get intermittent errors during the pasting to Word Editor. What is going on here, and how do I solve it so that down the road I can leave this to do its thing unsupervised?
http://imgur.com/a/fkc2W Edit: upon further testing I cant seem to reproduce the error reliably. idk, seems pretty good, should I just build an If (errorlevel != "0") then try again function?

Edit2 Seems like error is resolved, May have been how I was writing the email, but since setting it up as Email.body := header . body it has calmed down.

What is the best way to close without saving my xl. object after the email is created/sent? Edit: Figured out how to arrange script for best timing of XL.quit to be sent. (solved)

How can I open a saved workbook and loop through column C until cell("c:" & rownumber) contains %city% and then return value of cell("E:" & rownumber) ( lookup email address by city name)


Where can I read up on COM, and start to understand Hierarchy, objects, methods, properties etc, the more simple the better me:="n00b"

Is there a function that exists that I could modify to interact with a subfolder of my inbox named 24 hr reports , that contains these reports. My intention would be to once a day, process each email in that folder ( just these reports, sent there by outlook rule) so that each time it saw no warnings, it would delete the email and move to the next one, or if it was an email with warnings, create the chart, send the email as this script does, then delete, and move on to the next one. if not, how would you approach this?



Thank you so much for your time and knowledge!

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

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

Re: MS Office COM Basics

04 Dec 2016, 10:10

I wrote some of this before I saw your edits, but I think it mostly still applies.

http://www.mrexcel.com/forum/excel-ques ... ost3496819
I mention ^that link because it is doing something very similar to your task, and also because you asked where to read up on COM. Other than the things mentioned in the first post of this tutorial, I have found that knowing what the available objects are, how they work, and how they relate to each other is good. That general knowledge mainly comes from experience. The people on that board have a lot of experience. When they are working in Excel and they want to use Outlook, they do something similar to what we do in AHK.
VBA: Set olApp = CreateObject("Outlook.Application")
AHK: olApp := ComObjCreate("Outlook.Application")
And the similarities persist because the object created is identical.

^That post also mentions using the Word editor. We have also already used the word editor. (wEditor := olApp.ActiveInspector.WordEditor) This page mentions that some methods are not available in the Outlook version. (ie: It would be nice if we could use Shapes.AddChart, but it's not present in Outlook so we need to copy and paste the shape instead.)

This is all untested currently, but I think you can copy a range of cells and paste it into the body of the email the same way you paste the chart.
Also, wEditor.Application.Selection.TypeParagraph creates a new line.
The changes are marked with ; <---.

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


I'm not sure about that error. It may be because you are trying to paste while the window is still being created? Maybe adding a sleep or try as I have done above, will help? (I see from your edits that you've solved this already :) )

Looping through folders in Outlook: No Outlook on this machine currently so I can't test, but you could start here, here, here, here, or here.
Edit2:This seemed to work when I tested it. If you know the folder name, Item("name") works too (as opposed to using the index number).

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

/Edit2

To lookup email address by city name:

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

xlApp := ComObjCreate("Excel.Application")
xlApp.Visible := true
Wbk:= xlApp.Workbooks.Open(A_Desktop "\Book1.xlsx")
FoundCell := Wbk.Sheets(1).Range("A:A").Find("Abc") ; Find "Abc" in column A
MsgBox, % FoundCell.Offset(0, 4).Value ; Get the value of a cell at an offset (0 rows, 4 columns) from the found cell

More info:
Range.Find
Range.Offset

Edit: By the way, you can press Ctrl+C in an error message box to copy the text, then paste it into a plan text code box.

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

Easier than using images in my opinion.
RNDLDVL
Posts: 10
Joined: 04 Oct 2015, 10:48

Re: MS Office COM Basics

08 Dec 2016, 08:18

Hey kon, have you tried using your COM scripts on 32bit operating systems? (eg. 32bit win 7). I'm having problems opening(manually or through COM) workbooks once my script attaches to excel through this:

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

try global xL := ComObjActive("Excel.Application")
catch
global xl := ComObjCreate("Excel.Application")


The error is:
Excel cannot complete this task with available resources. Choose less data or close other applications.

Now, I doubt the cause of it is the computer is out of resources since there's plenty of RAM available, it's not the size of the workbooks either since it the workbooks barely has a file size above 3mb and they are mostly data without formulas. The 32-bit pc's were able to run a macro workbook that eats out around 500-700mb of RAM and never had this error.

Do you happen to know what might be a good work around this or the cause of it? My script works fine if I open the workbooks before I open my script, but my script deals with quite a lot of workbooks so that might not be the ideal scenario. The work around I have thought of is not saving a reference to Excel.Application, but doing something like this:

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



But without attaching to Excel.Application, some of my stuff breaks like:

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

	wbs.Sheets(1).ListObjects(1).Range.AutoFilter(4,arr,7)
wbs.Sheets(1).Range("B2").Select
wbs.Sheets(1).Range(xl.Selection, "C2").Select
wbs.Sheets(1).Range(xl.Selection, xl.Selection.End(-4121)).Select
xl.Selection.Copy

I tried to look for selection property I could use aside from Application.Selection but it seems there's none.

I haven't tried this on the 32bit machine yet since I'm at home and won't be at work for at least 12 hours.
kon
Posts: 1618
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

08 Dec 2016, 09:54

Do you quit excel after doing xl := ComObjCreate("Excel.Application")?
(Check your Task Manager for EXCEL.EXE processes.)
xl.Quit

Try making Excel visible if it is not already. That may help show what the problem is.

Anyway, many objects have an application property. ex: Workbook.Application
JoeWinograd
Posts: 652
Joined: 10 Feb 2014, 20:00

Re: MS Office COM Basics

21 Dec 2016, 13:22

Hi Kon,
Ran into another Word password issue. The solution you provided in a previous post works great for files with an Open password, i.e., Word never opens and never shows its Enter password to open file dialog box. However, I just ran into a file that also has a Modify password. So after my script successfully gets past the Open password, Word puts out this dialog:

Image

Is there any way to avoid this dialog? Maybe a param on the wdApp.Documents.Open(InputFile,,,,Pass) call that will tell it to open Read Only so that it never prompts for the Modify password? If not, any other ideas to solve the problem? Thanks again, Joe
kon
Posts: 1618
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

21 Dec 2016, 14:28

Untested, but the third parameter of Documents.Open is ReadOnly. Pass it true to open the document as read-only.
JoeWinograd
Posts: 652
Joined: 10 Feb 2014, 20:00

Re: MS Office COM Basics

21 Dec 2016, 14:32

Looks very promising! Is True the number 1 in that context?
kon
Posts: 1618
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

21 Dec 2016, 14:39

I would try 1 first and if that doesn't work try ComObject(0xB, -1).
JoeWinograd
Posts: 652
Joined: 10 Feb 2014, 20:00

Re: MS Office COM Basics

21 Dec 2016, 14:44

Good call! 1 works. So does -1. But 0 fails, so that must be False in this context. Thanks again — much appreciated! Regards, Joe
Guest

Re: MS Office COM Basics

31 Jan 2017, 13:23

Joe and kon, thanks for your interchange on this topic.
Joe, I think I ran across a post from you on SO that helped me understand this:
http://stackoverflow.com/questions/1268 ... t-fired-on

As you can see below, I added a MsgBox to the Word example on page 1 to help myself out.
As was pointed out in the StackOverflow post I linked to, "Neither the NewDocument nor the DocumentOpen event is fired when Microsoft Word first loads."
(I've got Word set to open a new doc in the Options, and NewDocument still doesn't fire from the example.
You can do that by disabling the Start Screen in General options. When I set it back to default, NewDocument still doesn't fire.)
I also changed wdApp.Visible := -1, but it works fine with "true".

I still feel I'm missing something very important, though. I haven't noticed any comments about this example not working, so
maybe it's something in my setup. Although even as written, and with the problems I'm having it's still perfectly valid -
it just doesn't say you need to open a document before the script will continue.

#Persistent
wdApp := ComObjCreate("Word.Application") ; Create an application object
wdApp.Visible := -1 ; Make the application visible
ComObjConnect(wdApp, "wd_") ; Connect to the application's events
;MsgBox, Open a NEW, blank MS Word document. ; <= Added this to make it more obvious that the document isn't going to open itself
return ; End of Auto-execute section

wd_NewDocument(Doc, App) ; This function responds to Word's 'NewDocument' event
{
MsgBox, % Doc.Name ; Show the name of the new document
App.Selection.TypeText("Hello world") ; Type text at the current selection
}
/*
wd_Quit(App) ; This function responds to Word's 'Quit' event
{
MsgBox, % App.Name ; Show the name of the application
ComObjConnect(App) ; Disconnect from App events
}

Thanks as always,
W.
kon
Posts: 1618
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

31 Jan 2017, 18:37

Hello, :wave:

Since you are already creating the Word application, you could also have your script create a new document at that time.

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


This may be more robust, but I don't think it is required. My thoughts are explained in the comments.

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


it just doesn't say you need to open a document before the script will continue.
...

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

;MsgBox, Open a NEW, blank MS Word document. ; <= Added this to make it more obvious that the document isn't going to open itself
Remove the semicolon from the start of the line?
MsgBox, Open a NEW, blank MS Word document.
burque505
Posts: 37
Joined: 22 Jan 2017, 19:37

Re: MS Office COM Basics

31 Jan 2017, 19:58

Thanks, kon. I'd gotten as far as the code below when I came back to read your post, and breathed a heavy sigh of relief. Thanks again! I hadn't put braces after the "add" command, and it fires anyway.
You are doing great work for the community and I really appreciate it. p.s. That semicolon in my earlier post was after I used that MsgBox for debugging.

#Persistent
wdApp := ComObjCreate("Word.Application") ; Create an application object
wdApp.Visible := true ; Make the application visible
ComObjConnect(wdApp, "wd_") ; Connect to the application's events
wdApp.Documents.Add ; <==On my Win7-x64 box, Office 64-bit, I had to add this before
; the wd_NewDocument(Doc, App) function would fire
return ; End of Auto-execute section

wd_NewDocument(Doc, App) ; This function responds to Word's 'NewDocument' event
{
MsgBox, % Doc.Name ; Show the name of the new document
App.Selection.TypeText("Hello world") ; Type text at the current selection
}
/*
wd_Quit(App) ; This function responds to Word's 'Quit' event
{
MsgBox, % App.Name ; Show the name of the application
ComObjConnect(App) ; Disconnect from App events
}
kon
Posts: 1618
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

16 Feb 2017, 11:43

The source code (BBCode) for this tutorial is now on github. Feel free to make a pull request or start an issue if you have a suggestion; I will merge it and update this tutorial if I agree with the changes. :)

https://github.com/ahkon/MS-Office-COM-Basics
JoeWinograd
Posts: 652
Joined: 10 Feb 2014, 20:00

Re: MS Office COM Basics

20 Feb 2017, 13:23

Guest wrote:Joe, I think I ran across a post from you on SO that helped me understand this

Nope — that was a different Joe W. Btw, sorry for the late reply on this, but I did not see a notification of your post even though I'm subscribed to the thread. I just noticed it when I came here to ask kon a question. Regards, Joe
JoeWinograd
Posts: 652
Joined: 10 Feb 2014, 20:00

Re: MS Office COM Basics

20 Feb 2017, 13:40

Hi kon,

I'm trying to determine if an Excel cell has a formula. In Excel 2013 and 2016, there's a new function called ISFORMULA, documented here:
https://support.office.com/en-us/Articl ... 39bfd6b1e5

Two questions for you:

(1) How would I call this via AHK COM? I tried CellIsFormula:=oWorkbook.Worksheets(1).Range(CellNumber).ISFORMULA, hoping that it would return True or False in the CellIsFormula variable, but that call gives Error 0x80020006 - Unknown name. I'm sure that's a syntax error on my part and that there's a way to call it from AHK.

(2) How would I achieve the same result (i.e., determine with AHK code if an Excel cell has a formula) in earlier versions of Excel, before the existence of the ISFORMULA function?

Thanks much, Joe
kon
Posts: 1618
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

20 Feb 2017, 18:29

I don't think you can access functions like that.

Have a look here though: Range.HasFormula Property (Excel)

Let me know how it goes :)
JoeWinograd
Posts: 652
Joined: 10 Feb 2014, 20:00

Re: MS Office COM Basics

20 Feb 2017, 20:47

kon wrote:Let me know how it goes

Worked perfectly! CellHasFormula:=oWorkbook.Worksheets(1).Range(CellNumber).HasFormula returns 0 if the cell does not have a formula and -1 if it does (in everything I've tested so far with Excel 2016). Thanks very much for pointing me in the right direction. Regards, Joe
FanaticGuru
Posts: 534
Joined: 30 Sep 2013, 22:25

Re: MS Office COM Basics

23 Feb 2017, 17:13

kon wrote:I don't think you can access functions like that.

Just for general knowledge you can access just about any of the functions built-in to Excel with WorksheetFunction.

https://msdn.microsoft.com/en-us/library/office/jj229043.aspx

It can be useful to have access to all of Excel's built-in function in AHK even if you are not really working with Excel.

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

Xl := ComObjActive("Excel.Application")
Payment := Xl.WorksheetFunction.Pmt(5 / 1200, 6 * 12, -10000) ; Monthly Payment for $10,000 loan at 5% for 6 years.
MsgBox % Payment


FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

Google Search, Dictionary, Thesaurus - Quickly Get Information from Specific Web Resources

[Function] Timer - Create and Manage Timers
JoeWinograd
Posts: 652
Joined: 10 Feb 2014, 20:00

Re: MS Office COM Basics

23 Feb 2017, 20:48

Hi FG,
That will be great stuff if I can get it to work, but so far haven't been able to. Here's what I tried:

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

InputFile:="c:\temp\FGtest.xlsx"
CellNumber:="A1"
oExcel:=ComObjCreate("Excel.Application")
oWorkbook:=oExcel.Workbooks.Open(InputFile)
CellIsFormula:=oExcel.WorksheetFunction.IsFormula(CellNumber)

It says that it is unable to get the IsFormula property of the WorksheetFunction class:

Image

What is wrong with the code above? Thanks, Joe
JoeWinograd
Posts: 652
Joined: 10 Feb 2014, 20:00

Re: MS Office COM Basics

23 Feb 2017, 22:33

Hi kon and FG (or anyone else who wants to jump in),
Quick question regarding Office COM basics — what is the difference between ComObjActive and ComObjCreate? The documentation isn't clear to me, as the write-ups sound similar:
ComObjActive
Creates an object representing a typed value to be passed as a parameter or return value.

ComObjCreate
Creates a COM object.

Thanks, Joe

Return to “Tutorials”

Who is online

Users browsing this forum: No registered users and 1 guest