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
abcyourway
  • Guests
  • Last active:
  • Joined: --
Don't forget the Internet Explorer!
It would be much greater help than any other things.

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

Don't forget the Internet Explorer!
It would be much greater help than any other things.


I already posted code to inject javascript and there is already script from Sean to load pages and such....what else do you want?
Posted Image
ʞɔпɟ əɥʇ ʇɐɥʍ

  • Guests
  • Last active:
  • Joined: --

and yeah, dont forget MS Office System is kinda big:
Excel, Groove, InfoPath, OneNote, Outlook, PowerPoint, Project, Publisher, SharePoint Designer, Visio, Word...

No No No ! i'd suggest focus on Word, Excel firstly (as you doing) after that Powerpoint or Outlook or Access. because at least 75% of office users are using only these 5 kings.

Don't forget the Internet Explorer!

remember it's not the whole MS applications wrapper it should be aparted from IE imo, IE is another big challenge don't burden him :p

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

You made me login. :) Thanks for starting up this great and big project.
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.

There should exist a temporary excel file test.xls in A_Temp folder.

Yet another (inconvenient) method to load an office document:
COM_Init()
pxlb := COM_GetObject(A_Temp . "\test.xls")	; Excel Workbook
pxls := COM_Invoke(pxlb, "Worksheets", 1)	; Excel Worksheet
pxla := COM_Invoke(pxlb, "Application")		; Excel Application
pwns := COM_Invoke(pxla, "Windows")
pwin := COM_Invoke(pwns, "Item", COM_Invoke(pwns, "Count"))
COM_Invoke(pwin, "Visible=", True)
COM_Invoke(pxla, "Visible=", True)
Sleep 5000
COM_Invoke(pxlb, "Save")
COM_Invoke(pxla, "Quit")
COM_Term()

Embed/Edit in AHK's GUI an office document:
GoSub, GuiOpen
pweb := COM_AtlAxCreateControl(WinExist(), A_Temp . "\test.xls")
pxlb := COM_Invoke(pweb, "Document")		; Excel Workbook
pxls := COM_Invoke(pxlb, "Worksheets", 1)	; Excel Worksheet
Return

GuiOpen:
Gui, +Resize +LastFound
Gui, Show, w800 h600 Center, Excel
COM_AtlAxWinInit()
Return
GuiClose:
Gui, Destroy
COM_Invoke(pxlb, "Save")
COM_Release(pxls)
COM_Release(pxlb)
COM_Release(pweb)
COM_AtlAxWinTerm()
ExitApp


abcyourway
  • Guests
  • Last active:
  • Joined: --

I already posted code to inject javascript and there is already script from Sean to load pages and such....what else do you want?


I already know that you ans Sean did good jobs.

What I am asking is document methods and properties like

pDoc     := com_invoke(ie, "document")
oTable1 := com_invoke(pDoc, "getElementById", "table1")


Without dom-handling, the real ie-automation can not be done, I think.

remember it's not the whole MS applications wrapper it should be aparted from IE imo, IE is another big challenge don't burden him


From what you said, I see you understood what I was talking about. Yeah, it can be a big burden, but I hope skilled guys like ahklerner 8) get the job done here. Don't you think so?

ahklerner
  • Members
  • 1386 posts
  • Last active: Oct 08 2014 10:29 AM
  • Joined: 26 Jun 2006
that can be done with javascript (whch CAN be inserted into the IE) you can also get values of javascript vars. is there something you need to do that can not be accomplished that way?

maybe you have not seen this post: <!-- m -->http://www.autohotke...pic.php?t=25473<!-- m -->
Posted Image
ʞɔпɟ əɥʇ ʇɐɥʍ

  • Guests
  • Last active:
  • Joined: --
First, thank you for the script and your contriibutions.
I am not pushing you to do it right now.
I just wanted to mention it so that when you you find time, you could try.

And I really liked your javascript solution, really!

About IE_automation, yeah we can do it with javascript, vbscript, even with API Dllcall, right?
We can send keys to some windows and make hotkeys without Autohotkey.

The point is that I and hopefully many people want to do it with Autohotkey, not with API calling, not with vbscript, autoit script. Of course not with javascript^^;

Ok, you got started with Office Apps, so first thing is first thing.
Word, excel part I can understand, but powerpoint and access? who would automate them? Don't we have ADO-related fucntions here?

Anyway I hope I didn't sound like grumbling^^;
Thank you for the script, ahklerner.

  • Guests
  • Last active:
  • Joined: --
@whom who's urging on him to make IE automation

i understand what you're talking about. but please listen me Wrapping the whole Office applications or part of them is not that easy or small job. have you ever look autoit's library for office automation? it's damn huge. it'll take long time. i agree that IE is more widely spreaded application. but as others say there's many other methods to interwork with IE though you don't like. by contrast. afaik there's no other way to do with MS office application atm except hardcoding with COM. automating MS Office has been dearest wish in the community. since you can do what you want atm would you please stop burden him with IE automation on MS Office thread?

Krogdor
  • Members
  • 1391 posts
  • Last active: Jun 08 2011 05:31 AM
  • Joined: 18 Apr 2008
Ooh, very nice... I've always been wondering about something like this for Word.

Although,
sCaption := "[" . UUID() . "]"
causes a nonexistant function call for me. Is there something I'm missing?

n-l-i-d
  • Guests
  • Last active:
  • Joined: --
I found some helpful resources for Word:

Word Macros and VBA FAQ
How to speed up Word Automation by hiding the application (or Office automation in general)

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

Ooh, very nice... I've always been wondering about something like this for Word.

Although,

sCaption := "[" . UUID() . "]"
causes a nonexistant function call for me. Is there something I'm missing?


Replace with
sCaption := "[" . A_TickCount . "]"

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

Laszlo
  • Moderators
  • 4713 posts
  • Last active: Mar 31 2012 03:17 AM
  • Joined: 14 Feb 2005
Is there an easy way to get the character to the left of the insertion point in a Word document? For example
- find the caret position
- define a range consisting of one character
- retrieve the character in the range.

This should not disturb the selection or the clipboard (which is known no to work properly with AHK). Something like this would be essential for a keyboard manager script.

aarondellis
  • Members
  • 57 posts
  • Last active: Feb 11 2013 02:18 PM
  • Joined: 15 Aug 2005
Thank you for this great information. Is it possible to read the contents of a cell in Excel?

Laszlo
  • Moderators
  • 4713 posts
  • Last active: Mar 31 2012 03:17 AM
  • Joined: 14 Feb 2005
It would be also nice to be able to handle (get, put) Unicode text, get the font and text attributes in the selection or at the insertion point.

Does someone have a list of methods supported by the COM interface to Word, like “Selection”, “Text”?

ahklerner
  • Members
  • 1386 posts
  • Last active: Oct 08 2014 10:29 AM
  • Joined: 26 Jun 2006
2 Laszlo:
Here is a link from msdn.
<!-- m -->http://msdn.microsof...ry ... 11).aspx<!-- m -->

I already have code for getting the font specs for the selection or the current ip.

com_CoInitialize() ; Initialize COM
Word_Open()
Return

#a::
GoSub, CreateFonts
Word_SetFont(DefaultFont)
Word_InsertText("`n`nThanks,`n`n")
Word_SetFont(Font1)
Word_InsertText("Whatever My Name Is`n`n")
Word_SetFont(Font2)
Word_InsertText("email: [email protected]`n")
Word_InsertText("phone: +50 123 45 21`n")
Word_InsertText("fax: +50 123 45 22`n`n")
Word_SetFont(Font3)
Word_InsertText("Give Me A call or something like that maybe.`n`n")
Word_SetFont(PrevFont)
return
esc::exitapp


CreateFonts:
PrevFont := Word_GetCurrentFont()
DefaultFont := NewFont()

Font1 := NewFont()
Font2 := NewFont()
Font3 := NewFont()
Font4 := NewFont()

Word_FontSetOpt(Font1,"Name","Arial")
Word_FontSetOpt(Font1,"Size",10)

Word_FontSetOpt(Font2,"Name","Times New Roman")
Word_FontSetOpt(Font2,"Size",8)

Word_FontSetOpt(Font3,"Name","Arial")
Word_FontSetOpt(Font3,"Bold",true)
Word_FontSetOpt(Font3,"Underline",true)

Word_FontSetOpt(Font4,"Name","Arial Black")
Word_FontSetOpt(Font4,"Bold",true)
Word_FontSetOpt(Font4,"Underline",true)

/* 
; OR
Font1 := "Name=Arial;Size=10;"
Font2 := "Name=Times New Roman;Size=8;"
; note that font options stay in effect until they are changed.
; IE - the size of the following two will be 8, whereas above, they will be the default font size. (my default is 12pt.)
Font3 := "Name=Arial;Bold=1;Underline=1;"
Font4 := "Name=Arial Black;Bold=1;Underline=1;"
 */

return

Word_GetCurrentFont(){
	pWord := Word_Attach("A")
	pSelection := COM_Invoke(pWord,"Selection")
	pFont := COM_Invoke(pSelection,"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)
	COM_Release(pSelection)
	COM_Release(pWord)
	return sFont
	}

Word_GetCurrentFontOpt(sOpt){
	pWord := Word_Attach("A")
	pSelection := COM_Invoke(pWord,"Selection")
	pFont := COM_Invoke(pSelection,"Font")
	StyleList := "Name,Size,Bold,Italic,Underline,AllCaps,DoubleStrikeThrough,Shadow,SmallCaps"
	StyleList .= ",StrikeThrough,Subscript,Superscript,Emboss,Position,Spacing"
	if sOpt in %StyleList%
		if !Val := COM_Invoke(pFont,sOpt)
			Val := 0
	COM_Release(pFont)
	COM_Release(pSelection)
	COM_Release(pWord)
	return Val
	}


Word_FontSetOpt(ByRef sFont,sOpt,sVal="¬"){
	StyleList := "Name,Size,Bold,Italic,Underline,AllCaps,DoubleStrikeThrough,Shadow,SmallCaps"
	StyleList .= ",StrikeThrough,Subscript,Superscript,Emboss,Position,Spacing"
	Loop, Parse, StyleList, `,
		{
		if RegExMatch(sFont,A_LoopField . "=([a-zA-Z0-9\-\s]*)", Prop)
			sFont := RegExReplace(sFont,sOpt . "=([a-zA-Z0-9\s]*)",sOpt . "=" . Prop1)
		else if sStyle in %StyleList%
			{
			if (sVal != "¬")
				COM_Invoke(pFont,A_LoopField . "=",sVal)
			else
				COM_Invoke(pFont,A_LoopField . "=",0)
			}
		}
	if RegExMatch(sFont, sOpt . "=([a-zA-Z0-9\s]*)")
	sFont := RegExReplace(sFont,sOpt . "=([a-zA-Z0-9\s]*)",sOpt . "=" . sVal)
	return sFont
	}

Word_FontGetOpt(sFont,sOpt){
	RegExMatch(sFont,sOpt . "=([a-zA-Z0-9\s]*)",Opt)
	return Opt1
	}
	
NewFont(sDefault="¬"){
	static NewFont
	if !NewFont {
		NewFont := "Name=Times New Roman;Size=12;Bold=0;Italic=0;Underline=0;AllCaps=0;DoubleStrikeThrough=0;"
		NewFont .= "Shadow=0;SmallCaps=0;StrikeThrough=0;Subscript=0;Superscript=0;Emboss=0;Position=0;Spacing=0"
		}
	if (sDefault != "¬")
		if (sDefault = "Current")
			NewFont := Word_GetCurrentFont()
		else
			NewFont := sDefault
	return NewFont
	}

Word_SetFont(sStyle,sVal="¬"){
	pWord := Word_Attach("A")
	pSelection := COM_Invoke(pWord,"Selection")
	pFont := COM_Invoke(pSelection,"Font")
	StyleList := "Name,Size,Bold,Italic,Underline,AllCaps,DoubleStrikeThrough,Shadow,SmallCaps"
	StyleList .= ",StrikeThrough,Subscript,Superscript,Emboss,Position,Spacing"
	Loop, Parse, StyleList, `,
		{
		if RegExMatch(sStyle,A_LoopField . "=([a-zA-Z0-9\-\s]*)", Prop)
			COM_Invoke(pFont,A_LoopField . "=",Prop1)
		else if sStyle in %StyleList%
			{
			if (sVal != "¬")
				COM_Invoke(pFont,A_LoopField . "=",sVal)
				;MsgBox % A_LoopField . " = " . sVal 
			else
				COM_Invoke(pFont,A_LoopField . "=",0)
			}
		}
	COM_Release(pFont)
	COM_Release(pSelection)
	COM_Release(pWord)
	Return
	}

#Include MSOFFICE_COM.ahk
#Include COM.ahk

it probably has a bug or 2....
I had not posted before because I am still not sure if I like the way the user has to set the formatting...Do you have any suggestions? (or anyone else for that matter)
Posted Image
ʞɔпɟ əɥʇ ʇɐɥʍ