View topic - MS Office Automation Functions (via COM) [thanks Sean] • AutoHotkey Community

View unanswered posts | View active topics

Board index » AutoHotkey » Scripts

All times are UTC [ DST ]

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

Page 4 of 20

[ 296 posts ]

Go to page Previous 1, 2, 3, 4, 5, 6, 7 ... 20 Next

Print view | E-mail friend

Previous topic | Next topic

Author

Message

Krogdor

Post subject:

Posted: August 25th, 2008, 5:56 am

Joined: April 18th, 2008, 7:57 am
Posts: 1390
Location: The Interwebs

ahklerner -

Made a couple of Word automation functions for myself, figured I might as well post them here. Feel free to add them to the library or not. I used the same format as you did for the function description.

Code:

; **************************************************************************
; Author:   Krogdor
; Language:   AutoHotkey v1.0.47.06
; Creation Date:   08/24/2008
; Function Name:   Word_Align()
;
; Align specified paragraph (Right/Center/Left, and Justify)
;
; Syntax:
;    Word_Align(Paragraph,Alignment)
; Parameters:
; 1)   Paragraph = The paragraph number that is to be aligned. Any places where
;      there is a newline counts as a new paragraph, so blank lines count as well.
; 2)   Alignment  = How to align it:
;        0 = Left (default)
;        1 = Center
;        2 = Right
;        3 = Justify
; Return:
;    Success = nothing
;    Failure = nothing
; **************************************************************************

Word_Align(Para = 1, Alignment = 0) {
  If (Alignment < 0 || Alignment > 3)
    Alignment = 0
  Word_Attach("A")
  ActiveDocument := COM_Invoke(Word,"ActiveDocument")
  Paragraphs     := COM_Invoke(ActiveDocument, "Paragraphs", Para)
  COM_Invoke(Paragraphs,"Alignment=",Alignment)
  COM_Release(Word)
  COM_Release(ActiveDocument)
  COM_Release(Paragraphs)
}


; **************************************************************************
; Author:   Krogdor
; Language:   AutoHotkey v1.0.47.06
; Creation Date:   08/22/2008
; Function Name:   Word_Header()
;
; Insert a header into the document with specified text.
;
; Syntax:
;    Word_Header(Text,Style)
; Parameters:
; 1)   Text   = the text string to insert into the document
; 2)   Style  = which pages to insert on:
;        1 = All pages  (default)
;        2 = First page
;        3 = Even pages
; Return:
;    Success = nothing
;    Failure = nothing
; **************************************************************************

Word_Header(Text, Style = 1, Word = "") {
  If (Style < 1 || Style > 3)
    Style = 1
  Word_Attach("A")
  ActiveDocument := COM_Invoke(Word,"ActiveDocument")
  Section        := COM_Invoke(ActiveDocument,"Sections",1)
  COM_Invoke(COM_Invoke(COM_Invoke(Section,"Headers",Style),"Range"),"Text",Text)
  COM_Release(ActiveDocument)
  COM_Release(Word)
  COM_Release(Section)
}

Top

rares

Post subject: help with script

Posted: August 25th, 2008, 1:14 pm

hello i have the following script:

COM_Init()
Sleep 1000
Word_InsertText("cat")
COM_Term()
exitapp

#include com.ahk
Word_InsertText(Text){
oWord := COM_GetActiveObject("Word.Application") ; Attach to Active Window
oSelection := COM_Invoke(oWord,"Selection") ; Get Insertion Point or Selected text
COM_Invoke(oSelection,"TypeText",dog) ; Put the text there
COM_Release(oSelection) ; cleanup
COM_Release(oWord) ; cleanup
}

Word_Open(){
oWord := COM_CreateObject("Word.Application") ; Attach to New Window
oDocuments := COM_Invoke(oWord, "Documents")
COM_Invoke(oWord,"Visible=",True)
COM_Invoke(oWord,"Activate")
COM_Invoke(oDocuments,"Add") ; Add a new document
COM_Invoke(oDocuments,"Select") ; Select it
COM_Release(oDocuments) ; cleanup
COM_Release(oWord) ; cleanup
}

it's sopose to put the words specified by me after certain words that it finds in the word document . For example, the script finds the word " dog ", I want it to paste the word "cat " after the word "dog"!

I want you guys to help me, if you can. I want to add more words for the computer to search and add after them the words i specify, resembling auto complete in Opera or the magic wand.

Top

ABCza

Post subject:

Posted: November 29th, 2008, 4:09 am

Joined: June 3rd, 2008, 7:34 pm
Posts: 86
Location: Italy

I'm not much involved in COM things; there's a way to loop the document paragraphs in MS Word? I mean something like:

Paragraph_1
ssdfjhsdfjksdhfhsjkdhfkjhfsjkdfkjsdhfjsdfdkhfhsdjfhsdkfjkdfsdj sdfsdjkfkj sdfjkhsdfkhsdjf sdfjksdhfhsdfjksdhf sdfjsdfh

Paragraph_2
sdfsdfhjsdhfsdjkf sdfjkhsdjkfsdjhfjksdhfjkshdfjks sdfjksdjkfdjkfdjk
skdjfjksdfjksjkdhfjkfsdjk sdjksdjkfhjk

Paragraph_3
dkjfgkljdfgjdfjgkljdfgkldfjklgd weruioruu werwuriouwer iowe mklmdgkldfklgjklj werpoweirpwero m,.fng,dfnfm,gm

_________________
All my scripts/snippets are released under the WTFPL: http://sam.zoy.org/wtfpl/COPYING

Top

sinkfaze

Post subject:

Posted: January 20th, 2009, 5:21 am

Joined: March 19th, 2008, 12:43 am
Posts: 5479
Location: the tunnel(?=light)

So if I don't want to load a macro into an Excel spreadsheet but I want to run an existing one (in the interests of creating a hotkey), I can essentially trim the code down to this for example?

Code:

COM_Init()
Excel_Run("PERSONAL.XLS!Anything")
COM_Term()
return

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

You'll have to forgive me, I've tried to read up but COM/expressions are largely beyond me, so the syntax could be obviously incorrect and I wouldn't know.

_________________

Try Quick Search for Autohotkey or see the tutorial for newbies.

Top

enigmatiqk

Post subject:

Posted: March 13th, 2009, 10:52 am

Joined: April 13th, 2007, 8:28 am
Posts: 28

aqtd wrote:

An error occurred while debugging
Function Name:Activate
ERROR

hello, nice job for all work !!!

but i have a lot of error:
1) i have the same error about function name activate (i have passed out with ";")

2) Excel_GetCell : "could not get cell" but i don't know why (she exist and have data), before this i have an com error with an hexa code...

3) excel close don't close my excel instance

Top

shellcode

Post subject:

Posted: March 16th, 2009, 3:53 pm

Joined: February 28th, 2009, 4:24 pm
Posts: 1

so cool.

thanks for script man

Top

truevis

Post subject: Re: MS Office Automation Functions (via COM) [thanks Sean]

Posted: March 18th, 2009, 2:08 am

Joined: September 8th, 2008, 8:38 pm
Posts: 33

ahklerner wrote:

[I will make the post better later..

]
Tested on Office 2003 Pro...

I'm getting a nonexistent function error on this line:

Code:

sCaption := "[" . UUID() . "]"

Please advise. (Where can I get the UUID function?)

Top

truevis

Post subject:

Posted: March 18th, 2009, 2:09 pm

Joined: September 8th, 2008, 8:38 pm
Posts: 33

I added this to my Word.ahk script and it seems to work, now.

Code:

uuid() { ; v1.0 - by Titan 
   static n, l 
   n += l = A_Now 
   f := A_FormatInteger, t := A_Now, s := "" 
   SetFormat, Integer, H 
   t -= 1970, s 
   t := (t . A_MSec) * 10000 + 122192928000000000 
   Random, x, 0x100, 0xfff 
   Random, y, 0x10000, 0xfffff 
   Random, z, 0x100000, 0xffffff 
   l := A_Now, t += n 
   SetFormat, Integer, %f% 
   Return, SubStr(t, 10) . s . SubStr(t, 6, 4) . s . 1 . SubStr(t, 3, 3) 
      . s . 9 . SubStr(x, 3) . s . 1 . SubStr(y, 3). SubStr(z, 3) 
   } 

I can now understand the Id of Unitarian Universalists. :shock:

Top

hughman

Post subject:

Posted: March 28th, 2009, 2:07 pm

Joined: February 11th, 2007, 4:10 pm
Posts: 185

enigmatiqk wrote:

aqtd wrote:

An error occurred while debugging
Function Name:Activate
ERROR

1)The workbooks seems to have no Activate method, I alter it to
COM_Invoke(oWorkbooks, "Worksheets[Sheet1].Activate")

2)I also have tried it. there's the same error. Then I fixed the code as follow:
oCells := COM_Invoke(oWorkbooks,"Worksheets[Sheet1].Cells.Item[1].Item[1]")
Value := COM_Invoke(oCells,"Value")
Msgbox, %Value%

It works correctly.
Maybe the fucntion Cells(col, row) can't be used. However, Cells.Item[col].Item[row] is not so conviencent sometimes.
But I don't know why. Who can tell me?

sorry for my poor english.

Top

Sean

Post subject:

Posted: March 28th, 2009, 4:48 pm

Joined: February 12th, 2007, 7:54 am
Posts: 2462

Use Range.

Code:

Value := COM_Invoke(oWorkbooks,"Worksheets[Sheet1].Range[A1].Value")

Top

hughman

Post subject:

Posted: March 28th, 2009, 6:12 pm

Joined: February 11th, 2007, 4:10 pm
Posts: 185

Sean wrote:

Use Range.

Code:

Value := COM_Invoke(oWorkbooks,"Worksheets[Sheet1].Range[A1].Value")

But why

Code:

Value := COM_Invoke(oWorkbooks,"Worksheets[Sheet1].Cells(1,1).Value"

can't return correct value?
We can write it as above in VBA.
It's easier to loop through every cell using Cells(row,col) method than Range

Top

Sean

Post subject:

Posted: March 29th, 2009, 12:55 am

Joined: February 12th, 2007, 7:54 am
Posts: 2462

Use like this, and read the corresponding thread about what you can or can't use.

Code:

oCell := COM_Invoke(oWorkbooks,"Worksheets[Sheet1].Cells",1,1)
Value := COM_Invoke(oCell,"Value")
COM_Release(oCell)

Top

hughman

Post subject:

Posted: March 29th, 2009, 3:39 pm

Joined: February 11th, 2007, 4:10 pm
Posts: 185

In fact, I have tried both of methods you wrote. They throw the same error notification:

Quote:

Function Name: "Value"
ERROR: The COM Object may not be a valid Dispatch Object!

It seems that oCell return null

I use Office2003, winxp.

Top

Sean

Post subject:

Posted: March 30th, 2009, 1:27 am

Joined: February 12th, 2007, 7:54 am
Posts: 2462

hughman wrote:

It seems that oCell return null

It used to work, so I didn't care about it but the recent change broke it. It's because the property Cells allows variable number of indices, thus the supposed PropertyGet is actually carried out as PropertyPut. It was a logical possibility, however, I thought it was rather unlikely and wasn't detected in my test as I changed the function Cells to Evaluate for some reason in my Excel script, unfortunately.
Now it turns out that there is absolutely no way to completely eliminate an artificial suffix in the name! The remained question is to choose which between two: retain the current one and introduce another suffix, or roll back to the older one. I'll announce it in the thread of COM.ahk if I decided.

Top

hughman

Post subject:

Posted: March 30th, 2009, 6:28 am

Joined: February 11th, 2007, 4:10 pm
Posts: 185

I have noticed that you update the thread of COM.ahk. And I have tried it with the same result.
Now I know that the round brackets can't be used for Com_Invoke. ^_^

My friend remind me of the fact that if in vba Cells is a funtion or not.
Maybe Cells not be treated as a funtion? So we can't use it like other object.function(param) as below? And so the funtion Excel_GetCell supplied by this thread's owner can not work correctly too. This is what I guess. I am just a newbie:)

Code:

oCell := COM_Invoke(oWorkbooks, "Worksheets[Sheet1].Cells", 1, 1)

Top

Page 4 of 20

[ 296 posts ]

Go to page Previous 1, 2, 3, 4, 5, 6, 7 ... 20 Next

Board index » AutoHotkey » Scripts

All times are UTC [ DST ]

Who is online

Users browsing this forum: Google [Bot], maul.esel and 13 guests

You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum