Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

MS Office Automation Functions (via COM) [thanks Sean]


  • Please log in to reply
132 replies to this topic

Poll: Should this be continued? (29 member(s) have cast votes)

Should this be continued?

  1. Yes (28 votes [96.55%])

    Percentage of vote: 96.55%

  2. No (1 votes [3.45%])

    Percentage of vote: 3.45%

Vote Guests cannot vote
Laszlo
  • Moderators
  • 4713 posts
  • Last active: Mar 31 2012 03:17 AM
  • Joined: 14 Feb 2005
Thanks for the reply! More questions to the experts:

I could not find any better method for retrieving the character to the left of the insertion point in Word than the following (not disturbing the Clipboard):
Word := COM_GetActiveObject("Word.Application")

   Sel  := COM_Invoke(Word,"Selection")      ; handle to the selection
   End  := COM_Invoke(Sel,"End")             ; get the end of selection, or pos after caret

   Doc  := COM_Invoke(Word,"ActiveDocument") ; handle to ActiveDocument
   Range:= COM_Invoke(Doc,"Range",End-1,End) ; handle to a range = last char in selection
   Text := COM_Invoke(Range,"Text")          ; get the text from the range
   MsgBox % Text

   COM_Release(Sel)                          ; cleanup
   COM_Release(Range)
   COM_Release(Doc)
   COM_Release(Word)
- Do we need to release every object, which had a handle returned?
- Is there a better way to get the desired character (without the clipboard)?
- How could we get Unicode (as integer code)?

Laszlo
  • Moderators
  • 4713 posts
  • Last active: Mar 31 2012 03:17 AM
  • Joined: 14 Feb 2005
... inserting a Unicode symbol can be done with
Word := COM_GetActiveObject("Word.Application") 

Sel  := COM_Invoke(Word,"Selection")

COM_Invoke(Sel,"InsertSymbol",333,"Unicode",1) ; replace selection with ASC(0333)


ahklerner
  • Members
  • 1386 posts
  • Last active: Oct 08 2014 10:29 AM
  • Joined: 26 Jun 2006

I could not find any better method ... than the following

That seems like the most straight-forward way to me. The only thing that I can think of to make it any faster would be to keep the handle to the app between calls (if there will be multiple calls and you are going to use the same document the entire time)...I really don't understand what sort of benefit there is to this...could you please explain a little?

- Do we need to release every object, which had a handle returned?

I just assumed it was the same as GDI objects (cause memory leaks if in released properly, or at all)... so that is how I coded it.

If a script retrieves a pointer to an object, it should always be released at some point. Usually it is not necessary if you are exiting the script, but it is still good practice.
<snip>
Releasing pwb will only release that object pointer, not any other objects retrieved through pwb. (This may not apply to some flawed implementations.)


Posted Image
ʞɔпɟ əɥʇ ʇɐɥʍ

ahklerner
  • Members
  • 1386 posts
  • Last active: Oct 08 2014 10:29 AM
  • Joined: 26 Jun 2006

Thank you for this great information. Is it possible to read the contents of a cell in Excel?

yes it is:
;MsgBox % Excel_GetCell("A", 1)
Excel_GetCell(ColumnLetter, RowNumber){
	if !oExcel := COM_GetActiveObject("Excel.Application") { ; Get the Already Running Instance
		MsgBox Could not find Excel Instance.
		Return 
		}
	if !oCells := com_invoke(oExcel,"Cells",RowNumber,Excel_GetColumnIndex(ColumnLetter)) {
		MsgBox Could not get cells.
		Return 
		}
	Value := COM_Invoke(oCells,"Value")
	com_release(oCells)
	com_release(oExcel)
	return Value
}


Posted Image
ʞɔпɟ əɥʇ ʇɐɥʍ

ahklerner
  • Members
  • 1386 posts
  • Last active: Oct 08 2014 10:29 AM
  • Joined: 26 Jun 2006
updated Excel functions.
Posted Image
ʞɔпɟ əɥʇ ʇɐɥʍ

ahklerner
  • Members
  • 1386 posts
  • Last active: Oct 08 2014 10:29 AM
  • Joined: 26 Jun 2006

...
And I'd like to contribute a little too. It'll use Excel as an example here, but I believe similar methods will also apply to other office applications.
...

do you know anything to fix the redrawing issues when the window is minimized and such?...maybe the winset trick will work here.

[edit]

redrawing issue does not show your excel example, but I tried word and it is not good....any ideas or tricks?

[edit 2: here is what I tried.]
FileName = test_word_document.doc

;Derived from Sean's simple example [for excel]
;http://www.autohotkey.com/forum/viewtopic.php?p=197771#197771
Title = Word Test
WinW = 800
WinH = 600
FileNameFullPath := File_GetFullPath(FileName)
WS_CLIPSIBLINGS := 0x4000000
WS_CLIPCHILDREN := 0x2000000
Gui, +Resize +LastFound ;+0x2000000
;WinSet, Trans, 255
Gui, Show, w%WinW% h%WinH% Center, %Title%

COM_AtlAxWinInit()

;container size
cOffsetLeft := 50
cOffsetTop := 50
cWidth := WinW - (cOffsetLeft * 2)
cHeight := WinH - (cOffsetTop * 2)

;create a container for the object so it does not take up the whole gui
hContainer := COM_AtlAxCreateContainer(WinExist(), cOffsetLeft, cOffsetTop, cWidth, cHeight)

;put the browser in the container
pweb := COM_AtlAxCreateControl(hContainer, FileNameFullPath)
pwdoc := COM_Invoke(pweb, "Document") 
pwrd := COM_Invoke(pwdoc, "Application") 

;Enable Web View
pawin := COM_Invoke(pwrd, "ActiveWindow") 
COM_Invoke(pawin, "View=", 6) 

;toolbars

prcbar := COM_Invoke(pwdoc, "CommandBars", "Reviewing") 
COM_Invoke(prcbar, "Visible=", false) ; Show it 

pscbar := COM_Invoke(pwdoc, "CommandBars", "Standard") 
COM_Invoke(pscbar, "Visible=", true) ; Show it 

pfcbar := COM_Invoke(pwdoc, "CommandBars", "Formatting") 
COM_Invoke(pfcbar, "Visible=", true) ; Show it 

pdoc := COM_Invoke(pwrd, "Documents") 
COM_Invoke(pdoc,"Select") ; Select it
;padoc := COM_Invoke(pwrd, "ActiveDocument") 

Selection := COM_Invoke(pwrd,"Selection")
MsgBox % Word_GetCurrentFont(Selection)
COM_Release(Selection)
;sWordCount := COM_Invoke(padoc, "WordCount") 
/* 
text=
(
pscbar : %pscbar%
pfcbar : %prcbar%
prcbar : %prcbar%
pawin : %pawin%
padoc : %padoc%
pdoc : %pdoc%
pwrd : %pwrd%
pwdoc : %pwdoc%
pweb : %pweb%
sWordCount : %sWordCount%
)
MsgBox %text%
 */
Return

Word_GetCurrentFont(psel){
	pFont := COM_Invoke(psel,"Font")
	StyleList := "Name,Size,Bold,Italic,Underline,AllCaps,DoubleStrikeThrough,Shadow,SmallCaps"
	StyleList .= ",StrikeThrough,Subscript,Superscript,Emboss,Position,Spacing"
	Loop, Parse, StyleList, `,
		{
		if !Val := COM_Invoke(pFont,A_LoopField)
			Val := 0
		sFont .= A_LoopField . "=" . Val . ";"
		}
	COM_Release(pFont) 	; formatting toolbar
	return sFont
	}

GuiClose:
;Save the Active Document
COM_Invoke(padoc, "Save")
Gui, Destroy
COM_Invoke(pwrd, "Quit")
COM_Release(pfcbar) 	; formatting toolbar
COM_Release(pscbar) 	; standard toolbar
COM_Release(prcbar) 	; reviewing toolbar
COM_Release(pawin) 	; active window
COM_Release(padoc) 	; active document
COM_Release(pdoc) 	; documents
COM_Release(pwrd) 	; word.application
COM_Release(pwdoc) 	; browser.document
COM_Release(pweb) 	; browser
COM_AtlAxWinTerm()
ExitApp

File_GetFullPath(File){
	Loop %File%
		Return A_LoopFileLongPath
	}

#Include COM.ahk

Posted Image
ʞɔпɟ əɥʇ ʇɐɥʍ

Sean
  • Members
  • 2462 posts
  • Last active: Feb 07 2012 04:00 AM
  • Joined: 12 Feb 2007

do you know anything to fix the redrawing issues when the window is minimized and such?...maybe the winset trick will work here.

Have you ever experienced the same symptom with, e.g. WebBrowser/DOM control too? My guess is that AtlAxWin doesn't handle well the situation like this. I mean, unlike DOM case, the Excel window embedded in AHK's GUI/AtlAxWin isn't actually the window of AHK, it's owned/created by Excel.exe, so in its simplest term it can be viewed as SetParent across process boundary. The simplest solution, my favorite indeed, may be to avoid involving AtlAxWin, i.e., directly embed into GUI.
pweb := COM_AtlAxCreateControl(WinExist(), FileNameFullPath)
If it's not a feasible solution for you, then may have to manually update the excel window when restored via setting OnMessage.

ahklerner
  • Members
  • 1386 posts
  • Last active: Oct 08 2014 10:29 AM
  • Joined: 26 Jun 2006
i worked around by adding a guisize label and hiding then reshow the toolbars....but i was really looking for something like WinSet Redraw for it....
Posted Image
ʞɔпɟ əɥʇ ʇɐɥʍ

Laszlo
  • Moderators
  • 4713 posts
  • Last active: Mar 31 2012 03:17 AM
  • Joined: 14 Feb 2005
Thanks for the answers. Nobody knows about getting Unicode from Word?

Sean
  • Members
  • 2462 posts
  • Last active: Feb 07 2012 04:00 AM
  • Joined: 12 Feb 2007

i worked around by adding a guisize label and hiding then reshow the toolbars....but i was really looking for something like WinSet Redraw for it....

You may use WinMove or ControlMove. It works fine with me.
ControlMove,,,, 0, 0, ahk_id %hContainer%
ControlMove,,,, cWidth, cHeight, ahk_id %hContainer%


ahklerner
  • Members
  • 1386 posts
  • Last active: Oct 08 2014 10:29 AM
  • Joined: 26 Jun 2006
hey cool, i never thought of that. thx
Posted Image
ʞɔпɟ əɥʇ ʇɐɥʍ

irontomato
  • Members
  • 64 posts
  • Last active: Jul 17 2015 06:23 PM
  • Joined: 23 Jun 2008
I just want to thank everyone who has worked on this and the COM/CoHelper files because they have been a huge help to me.

I've been using a function for flipping between excel and copy and pasting to the desired location, needless to say this wasn't effecient at all. This method works great!

Currently I'm using code from the older ADO com/CoHelper thread but hope to switch to this code. Is there an advantage to using these commands instead of the Invoke commands? Is it worth me switching them today?

I'm a intern at a big company and because of everyone on the forums I have been able to really help them out.

Thanks so much!

irontomato
  • Members
  • 64 posts
  • Last active: Jul 17 2015 06:23 PM
  • Joined: 23 Jun 2008
Hey everyone.

I'm trying to use this Excel function and I need some help.

How does it know which excel file to load?

ahklerner
  • Members
  • 1386 posts
  • Last active: Oct 08 2014 10:29 AM
  • Joined: 26 Jun 2006
here is an example of loading vbscript code into an excel sheet and running it.

thanks to heresy for the idea on IRC

VBcode=
(
Sub Anything()
	MsgBox("YEAH")
End Sub
)

COM_Init()
Excel_ImportCode(VBcode) ; you cant call this twice with the same code
Excel_Run("Anything")
COM_Term()
return

Excel_Run(sFunction){
	if oExcel := COM_GetActiveObject("Excel.Application")
		COM_Invoke(oExcel,"Run", sFunction)
	COM_Release(oExcel)
}

Excel_ImportCode(VBcode){
	if fileexist(A_ScriptDir . "\tempvbcode.txt")
		FileDelete, %A_ScriptDir%\tempvbcode.txt
	FileAppend, %VBcode%, %A_ScriptDir%\tempvbcode.txt
	if oExcel := COM_GetActiveObject("Excel.Application")
		if oActiveWorkbook := COM_Invoke(oExcel,"ActiveWorkbook")
			if oVBProject := COM_Invoke(oActiveWorkbook,"VBProject")
				if oVBComponents := COM_Invoke(oVBProject,"VBComponents")
					COM_Invoke(oVBComponents,"Import", A_ScriptDir . "\tempvbcode.txt")
	COM_Release(oVBComponents)
	COM_Release(oVBProject)
	COM_Release(oWorkbooks)
	COM_Release(oExcel)
}

Posted Image
ʞɔпɟ əɥʇ ʇɐɥʍ

aqtd
  • Guests
  • Last active:
  • Joined: --
An error occurred while debugging
Function Name:Activate
ERROR