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

        f1::Reload
        Pause::Pause
        f2::ListVars
        Pause


        !`::
#SingleInstance
        SetKeyDelay, -1 




        

        TMP:=""
        RMRKS:=""
        Date1:=""
        Date2:=""
        City:=""
        Chart:=""
        Warnings:=""


        EMAIL:=clipboard   
        city:=Getcity(EMAIL)  ; Get city name
        Warnings := ParseWarnings(Email)
            if Warnings.MinIndex()                              ; If there was stuff to put into array
                    CreateChart(Warnings, chart, city)
            
            
            
              
            else
                msgbox , No Warnings
            
            
            
            
            
        ;###################################################################################################################################################################################################################;
        ;##########################################################################################      Functions      ####################################################################################################; 
        ;###################################################################################################################################################################################################################;


        ParseWarnings(Email)
        {
            Warnings := []
            EMAIL := StrReplace(EMAIL, """")                 ; Email text var. Remove all quotes.
            Loop, Parse, EMAIL, `n, `r                           ; Parse email by line
            {
                tmp := StrSplit(A_LoopField, ";")               ; For each line break it into chunks by ";" 
                rmrks := tmp.6                                  ; Warn code is in 6th index     
                If InStr(rmrks, "Warning")                      ; If this is a warning line
                {
                    date1:=StrSplit(tmp.1, "/")                 ; Date/time is in DD/MM/YYYY , split it up by "/"
                    Warnings.Push( {"When": date1.2 "/" date1.1 "/" date1.3, "What": rmrks} )  ;Warnings[1].when  //// Warnings[1].what
                }
            }
            return Warnings
        }

        CreateChart(Warnings, chart, city)
        {
            static xlColumnClustered := 51
                 , xlColumnField := 2
                 , xlCount := -4112
                 , xlDatabase := 1
                 , xlHidden := 0
                 , xlPivotTableVersion12 := 3
                 , xlRowField := 1
            
            XL := ComObjCreate("Excel.Application")         ; Create an excel object
            Wbk := XL.Workbooks.Add                         ; Add a workbook to the object
            Xl.Visible := True                              ; Make it visible
            XL.DisplayAlerts := False 
            Sheet := xl.Sheets(1)                           ; Save a reference to this sheet
            
            ; Set Column headings and width
            Sheet.Range("A1").Value := "Time"               ; Create Time header
            Sheet.Range("A:A").columnwidth := "20" 
            Sheet.Range("B1").Value := "Warning"            ; Create Warning Header
            Sheet.Range("B:B").columnwidth := "56.86"
            
            ; Create a safe array and copy data into it. Then put the safe array into a range.
            nRows := Warnings.MaxIndex()                    ; The number of rows
            SafeArray := ComObjArray(12, nRows, 2)          ; Create a safearray of the correct size. (Type = 12, Rows = nRows, Columns = 2)
            for i, Warning in Warnings
            {
                SafeArray[i - 1, 0] := Warning.When         ; SafeArray[RowNumber, ColumnNumber] := Value
                SafeArray[i - 1, 1] := Warning.What         ; SafeArray index starts at 0 (not 1)
            }
            Cell := Sheet.Range("A2")                       ; The top left cell of the range
            Sheet.Range(Cell, Cell.Offset(nRows - 1, 1)).Value := SafeArray  ; Put the SafeArray into the Range

            rng := Sheet.UsedRange.address
            trgt := Sheet.range("c1")
            pvt := xl.ActiveWorkbook.PivotCaches
                .Create(xlDatabase, rng, xlPivotTableVersion12)
                .CreatePivotTable(trgt, "PivotTable1",, xlPivotTableVersion12)
                
            pfWarning := pvt.PivotFields("warning")
                pfWarning.Orientation := xlColumnField
                pfWarning.Position := 1
                pvt.AddDataField(pfWarning, "Count of Warning",  xlCount)






            ; **Is it necessary to set 'pfTime.Orientation' multiple times?
            pfTime := pvt.PivotFields("time")       ; VBA = With ActiveChart.PivotLayout.PivotTable.PivotFields("Time")
                pfTime.Orientation := xlHidden      ; VBA = ActiveChart.PivotLayout.PivotTable.PivotFields("Time").Orientation = xlHidden
                pfTime.Orientation := xlRowField    ; VBA = .Orientation = xlRowField
                pfTime.Position := 1                ; VBA = .Position = 1
                pfTime.AutoGroup                    ; Must be Office version >= 2016
            pvt.PivotFields("Minutes").Orientation := xlHidden  ; ???
                pfTime.Orientation := xlHidden      ; ???
           
            Sheet.Shapes.AddChart
            wbk.ShowPivotChartActiveFields := false
            
            Sheet.ChartObjects(1).Activate
            Chart := wbk.ActiveChart
                Chart.ChartTitle.Delete
                Chart.ChartType := xlColumnClustered
              Chart.PivotLayout.PivotTable.PivotFields("Warning").Orientation := xlColumnField
               Chart.PivotLayout.PivotTable.PivotFields("Warning").Position := 1
              CreateEmail(Chart, city,Warnings)
                 XL.quit
            return Chart
        }
        CreateEmail(Chart, city,warnings)
        {
            ; Reference: http://stackoverflow.com/questions/25603864/copy-excel-chart-to-outlook-mail-message
            ; Alternative method: http://www.mrexcel.com/forum/excel-questions/562877-paste-chart-into-email-body.html
            static olMailItem := 0
            
            olApp := ComObjCreate("Outlook.Application")
            Email := olApp.CreateItem(olMailItem)
            Email.Display
            Email.To := "test64413@gmail.com"
            Email.Subject := "*** Todays Warnings for Your Gain Site in " city " ***"
            body:=warndata(warnings)
            header:="`n" "`n" "Data:" "`n" 
            Email.body := header . body  
            Chart.ChartArea.Copy	
            wEditor := olApp.ActiveInspector.WordEditor
            wEditor.Application.Selection.Paste
            
            
        }
        GetCity(EMAIL)
        {
                Split := StrSplit(EMAIL, "`n", "`r") 
                City := Split[Split.Length()-5] 
            IfNotInString, City, ,
                City := Split[Split.Length()-6] 
                City:=strsplit(city,",")
                City:=City.1
                Return City
            }
            
            warndata(warnings) 
            {		
                for i, Warning in Warnings	
                  body .= "`n" Warning.When "`t" Warning.What "`n"    ; This is not right
            return body
            }
            
            
            

            
          
kon
Posts: 1756
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

Clipboard := GetTestText()  ; <-- This line is for testing only and should be removed
function()
ExitApp

function()
{
    Warnings := ParseWarnings()
    Nothing := "Nothing to report, have a wonderful day."
    
    if Warnings.MinIndex()                              ; If there was stuff to put into array
    {
        Obj := CreateChart(Warnings)  ; <---
        CreateEmail(Obj)  ; <---
    }
}

ParseWarnings()
{
    Warnings := []
    EMIN := StrReplace(Clipboard, """")                 ; Email text var. Remove all quotes.
    Loop, Parse, EMIN, `n, `r                           ; Parse email by line
    {
        tmp := StrSplit(A_LoopField, ";")               ; For each line break it into chunks by ";" 
        rmrks := tmp.6                                  ; Warn code is in 6th index     
        If InStr(rmrks, "Warning")                      ; If this is a warning line
        {
            date1:=StrSplit(tmp.1, "/")                 ; Date/time is in DD/MM/YYYY , split it up by "/"
            Warnings.Push( {"When": date1.2 "/" date1.1 "/" date1.3, "What": rmrks} )
        }
    }
    return Warnings
}

CreateChart(Warnings)
{
    static xlColumnClustered := 51
         , xlColumnField := 2
         , xlCount := -4112
         , xlDatabase := 1
         , xlHidden := 0
         , xlPivotTableVersion12 := 3
         , xlRowField := 1
    
    XL := ComObjCreate("Excel.Application")         ; Create an excel object
    Wbk := XL.Workbooks.Add                         ; Add a workbook to the object
    Xl.Visible := True                              ; Make it visible
    Sheet := xl.Sheets(1)                           ; Save a reference to this sheet
    
    ; Set Column headings and width
    Sheet.Range("A1").Value := "Time"               ; Create Time header
    Sheet.Range("A:A").columnwidth := "20" 
    Sheet.Range("B1").Value := "Warning"            ; Create Warning Header
    Sheet.Range("B:B").columnwidth := "56.86"
    
    ; Create a safe array and copy data into it. Then put the safe array into a range.
    nRows := Warnings.MaxIndex()                    ; The number of rows
    SafeArray := ComObjArray(12, nRows, 2)          ; Create a safearray of the correct size. (Type = 12, Rows = nRows, Columns = 2)
    for i, Warning in Warnings
    {
        SafeArray[i - 1, 0] := Warning.When         ; SafeArray[RowNumber, ColumnNumber] := Value
        SafeArray[i - 1, 1] := Warning.What         ; SafeArray index starts at 0 (not 1)
    }
    Cell := Sheet.Range("A2")                       ; The top left cell of the range
    MyRange := Sheet.Range(Cell, Cell.Offset(nRows - 1, 1))  ; <--- Save MyRange so we can insert it in the email
    MyRange.Value := SafeArray  ; Put the SafeArray into the Range
    

    rng := Sheet.UsedRange.address
    trgt := Sheet.range("c1")
    pvt := xl.ActiveWorkbook.PivotCaches
        .Create(xlDatabase, rng, xlPivotTableVersion12)
        .CreatePivotTable(trgt, "PivotTable1",, xlPivotTableVersion12)
        
    pfWarning := pvt.PivotFields("warning")
        pfWarning.Orientation := xlColumnField
        pfWarning.Position := 1
        pvt.AddDataField(pfWarning, "Count of Warning",  xlCount)

    ; **Is it necessary to set 'pfTime.Orientation' multiple times?
    pfTime := pvt.PivotFields("time")       ; VBA = With ActiveChart.PivotLayout.PivotTable.PivotFields("Time")
        pfTime.Orientation := xlHidden      ; VBA = ActiveChart.PivotLayout.PivotTable.PivotFields("Time").Orientation = xlHidden
        pfTime.Orientation := xlRowField    ; VBA = .Orientation = xlRowField
        pfTime.Position := 1                ; VBA = .Position = 1
        pfTime.AutoGroup                    ; Must be Office version >= 2016
    pvt.PivotFields("Minutes").Orientation := xlHidden  ; ???
        pfTime.Orientation := xlHidden      ; ???
    
    Sheet.Shapes.AddChart
    wbk.ShowPivotChartActiveFields := false
    
    Sheet.ChartObjects(1).Activate
    Chart := wbk.ActiveChart
        Chart.ChartTitle.Delete
        Chart.ChartType := xlColumnClustered
    
    return {"Chart": Chart, "Rng": MyRange}  ; <--- return both a chart and a range
}

CreateEmail(Obj)  ; <---
{
    ; Reference: http://stackoverflow.com/questions/25603864/copy-excel-chart-to-outlook-mail-message
    ; Alternative method: http://www.mrexcel.com/forum/excel-questions/562877-paste-chart-into-email-body.html
    static olMailItem := 0
    
    olApp := ComObjCreate("Outlook.Application")
    Email := olApp.CreateItem(olMailItem)
    Email.To := "someone@something.foo"
    Email.Subject := "Test"
    Email.Display
    
    Obj.Chart.ChartArea.Copy  ; <---
    wEditor := olApp.ActiveInspector.WordEditor
    Loop  ; <--- If an error occurs, try again
    {
        try  ; Edit: This part has been revised now that I've had the chance to test it.
        {
            wEditor.Application.Selection.Paste
            break  ; Break the loop
        }
        catch
        {
            Sleep, 200
            if (A_Index > 10)  ; to avoid an infinite loop, we only try a certain number of times
            {
                ; Failed to paste chart. Do error handling here.
                MsgBox, Failed to Paste chart.
                break  ; Break the loop
            }
        }
    }
    wEditor.Application.Selection.TypeParagraph  ; <---
    Obj.Rng.Copy  ; <--- 
    wEditor.Application.Selection.Paste  ; <---
}

GetTestText()  ; <-- This function is for testing only and should be removed
{
    TestText =
    (LTrim Join`r`n
        At: 25/11/2016 23:59:01


        ACTIVITY HISTORY - PAST 24 HOURS:
        NOTE: All times are in Coordinated Standard Time (UTC).
        $"EventDate";"TagName";"Status";"Type";"UserAck";"Description"
        "25/11/2016 00:41:08";"Message_320";"ALM";"LVL";"";"Compressor 1 Running"
        "25/11/2016 10:25:16";"Message_320";"END";"";"";"Compressor 1 Running"
        "25/11/2016 10:25:16";"Message_321";"END";"";"";"Compressor 2 Running"
        "25/11/2016 10:25:16";"Message_324";"ALM";"LVL";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 10:31:28";"Message_325";"END";"";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 10:31:28";"Message_321";"ALM";"LVL";"";"Compressor 2 Running"
        "25/11/2016 10:31:40";"Message_320";"ALM";"LVL";"";"Compressor 1 Running"
        "25/11/2016 10:31:40";"Message_324";"END";"";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 11:12:04";"Message_200";"ALM";"LVL";"";"Oil Injector Temperature Warning (Compressor 2)"
        "25/11/2016 12:13:28";"Message_200";"END";"";"";"Oil Injector Temperature Warning (Compressor 2)"
        "25/11/2016 12:13:28";"Message_325";"ALM";"LVL";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 12:13:28";"Message_320";"END";"";"";"Compressor 1 Running"
        "25/11/2016 20:50:41";"Message_321";"END";"";"";"Compressor 2 Running"
        "25/11/2016 20:50:41";"Message_324";"ALM";"LVL";"";"Compressor 1 Ready To Run (Automatic Start)"

        +++++++++++++++++++++++++++++++++++++++++++++++++++
        This Email has been generated automatically by:
        *** Device Identification:
        47GS Unit #017 (219)
        *** Device Description:
        Fake Fuel
        1234 Plant fake Road 
        fake, FA
        *** Device TCP/IP addresses
        Ethernet: http://192.168.101.666
        PPP:      http://192.168.101.666
        +++++++++++++++++++++++++++++++++++++++++++++++++++
    )
    return TestText
}
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

olApp := ComObjActive("Outlook.Application")  ; Get a reference to the running Outlook application
olns := olApp.Session  ; Get reference to MAPI namespace
f := olns.Folders
TopLevel := "", SecondLevel := ""



; Top level folder (NameSpace.Folders)
for SubFolder, in f
{
    TopLevel .= SubFolder.Name "`n"
    ;~ MsgBox, 64, olns.Folders.Item(A_Index), % "Folder Name: " SubFolder.Name
}
MsgBox, 64, Top Level, % TopLevel



; Second level folder (NameSpace.Folders.Item(1).Folders)
for SubFolder, in f.Item(1).Folders  ; Get all sub-fodlers in the first top level folder.
{
    SecondLevel .= SubFolder.Name "`n"
    ;~ MsgBox, 64, olns.Folders.Item(1).Folders.Item(%A_Index%), % "Folder Name: " SubFolder.Name
}
MsgBox, 64, Second Level, % SecondLevel
/Edit2

To lookup email address by city name:

Code: Select all

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

---------------------------
Test.ahk
---------------------------
Error:  0x800401E3 - Operation unavailable


	Line#
--->	001: olApp := ComObjActive("Excel.Application")
	002: Exit
	003: Exit
	003: Exit

Continue running the script?
---------------------------
Yes   No   
---------------------------
Easier than using images in my opinion.
RNDLDVL
Posts: 12
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

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

testFunc(){
	wbs := XL_IsOpen(xlpath)
	wbs. Do.Stuff		
}

XL_IsOpen(path){ ;scan objects for workbooks, returns object ptr if found/null if none
	Loop, 2 {
		SplitPath, path, tFN, tDir
		xlt := ""
		For name, obj in GetActiveObjects() {
			If (ComObjType(obj, "Name") = "_Workbook") {
				If (obj.Path = tDir && obj.Name = tFN) {
					Return, obj
				}
			}
		}
		Run, %tFN%, %tDir%, Min
		Sleep, 500 
	}
}


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

Code: Select all

	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: 1756
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: 1081
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: 1756
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: 1081
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: 1756
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: 1081
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: 1756
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

#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()                           ; Create a new document
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
}
This may be more robust, but I don't think it is required. My thoughts are explained in the comments.

Code: Select all

#Persistent
wdApp := ComObjCreate("Word.Application")      	; Create an application object
; My copy of Word does not create a new document when Word is opened with 'wdApp := ComObjCreate("Word.Application")'
; I don't think your copy of Word will either, even if it usually does create a new document when you open Word
; manually. At this point, if your copy of Word DOES create a new document, I would expect the 'NewDocument' event to
; fire. The problem is that you are not connected to the events yet so your script does not detect this event.
wdApp.Visible := true							; Make the application visible
ComObjConnect(wdApp, "wd_")                     ; Connect to the application's events
try
{   ; Get the active document (if one exists). This never works on my system. No new document is created when AHK
    ; creates an instance of Word (ie: After 'ComObjCreate("Word.Application")', no new document is present ). Unless
    ; your copy of Word behaves differently than mine, I expect 'wdApp.ActiveDocument' to always fail and trigger the
    ; execution of the 'catch' block.
    MyDoc := wdApp.ActiveDocument               ; This line throws an error if no ActiveDocument exists.
    wd_NewDocument(MyDoc, wdApp)                ; Call the wd_NewDocument function directly
    MsgBox, % "There is already an active document. (" MyDoc.Name ")"
}
catch  
{   ; This 'catch' block is executed in the event of an error in the preceding 'try' block.
    ; If there is NOT an active document, then 'wdApp.ActiveDocument' will throw an error. 'try' allows the script to
    ; handle the error instead of displaying a MsgBox to ask the user what to do.
    MyDoc := wdApp.Documents.Add()              ; Since no ActiveDocument exists, create a new document
    ; The 'wd_NewDocument' function should be triggered at this point.
    MsgBox, % "Created a new document. (" MyDoc.Name ")"
}
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
    Doc.Activate                                ; Activate the document because 'Selection' is used next
    App.Selection.TypeText("Hello world")       ; Type text at the current selection
}
it just doesn't say you need to open a document before the script will continue.
...

Code: Select all

;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: 681
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: 1756
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: 1081
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: 1081
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: 1756
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: 1081
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: 1230
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/librar ... 29043.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

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

[Function] Timer - Create and Manage Timers
JoeWinograd
Posts: 1081
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

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: 1081
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 4 guests