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: 1522
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: 1522
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: 591
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: 1522
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: 591
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: 1522
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: 591
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

Return to “Tutorials”

Who is online

Users browsing this forum: Indomito and 4 guests