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
ahklerner
  • Members
  • 1386 posts
  • Last active: Oct 08 2014 10:29 AM
  • Joined: 26 Jun 2006
[I will make the post better later.. :) ]
Tested on Office 2003 Pro

Requires COM.ahk
<!-- m -->http://www.autohotke...pic.php?t=22923<!-- m -->

MS Word:
Test Script:
; WORD TEST SCRIPT
com_CoInitialize() ; Initialize COM

; File Name to be created / opened
FileName = test_word_document.doc

Word_Open() ; Opens a New Document
; Word_OpenDoc(FileName) ; Opens an existing document

;Set Initial Values
; They are toggled later
Bold 	:= True
Italic 	:= False
Caps 	:= True
FontSize = 4

Loop, 20
	{
	FontSize += 2 ; Increase the Font Size
	; Set the Current Font
	; Word_SetCurrentFont(Font,FontSize,Bold,Italic,Caps)
	Word_SetCurrentFont("Arial",FontSize,Bold := !Bold,Italic := !Italic,Caps := !Caps)
	Word_InsertText("abcdefg`n") ; Insert The Text
	}

MsgBox Going to save and exit.

;SaveAs DOES NOT PROMPT FOR OVERWRITE IF THE FILE NAME ALREADY EXISTS
Word_SaveAs(A_ScriptDir . "\" . FileName)
;Close Word
Word_Close()

com_CoUnInitialize()
exitapp

; **************************************************************************
; Author:	ahklerner
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	14:15
; Function Name:	Word_InsertText()
; 
; Insert text into the current document at the insertion point or overwriting selected area
;
; Syntax:
; 	Word_InsertText(Text)
; Parameters:
; 1)	Text	= the text string to insert into the document	
; Return:
; 	Success = nothing
; 	Failure = nothing
; **************************************************************************
Word_InsertText(Text){	
	Word := Word_Attach("A") ; Attach to Active Window
	Selection := com_invoke(Word,"Selection") ; Get Insertion Point or Selected text
	com_invoke(Selection,"TypeText", Text) ; Put the text there
	com_release(Selection) ; cleanup
	com_release(Word) ; cleanup
	}


; **************************************************************************
; Author:	ahklerner
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	14:16
; Function Name:	Word_Open()
; 
; Start a new instance of word
; 
; Syntax:
; 	Word_Open()
; Parameters:
;	None
; Return:
; 	Success = nothing
; 	Failure = nothing
; **************************************************************************
Word_Open(){
	Word := Word_Attach("N") ; Attach to New Window
	Documents := com_Invoke(Word, "Documents") 
	com_invoke(Documents,"Add") ; 	Add a new document
	com_invoke(Documents,"Select") ; Select it
	com_release(Documents) ; cleanup
	com_release(Word) ; cleanup
	}


; **************************************************************************
; Author:	ahklerner
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	14:17
; Function Name:	Word_Close()
; 
; Close all instances of word
; may prompt for save dialog.
; 
; Syntax:
; 	Word_Close()
; Parameters:
;	None
; Return:
; 	Success = nothing
; 	Failure = nothing
; **************************************************************************
Word_Close(){
	Word := Word_Attach("A") ; Attach to Active Window
	com_invoke(Word,"Quit")	; quit all instances
	com_release(Word) ; cleanup
	}

; **************************************************************************
; Author:	ahklerner
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	14:18
; Function Name:	Word_SaveAs()
; 
; Syntax:
; 	Word_SaveAs(SaveFileName)
; Parameters:
; 1)	SaveFileName	= The name to save the file as. Must be Full Path	
; Return:
; 	Success = 
; 	Failure = 
; **************************************************************************
Word_SaveAs(SaveFileName){
	Word := Word_Attach("A") ; Attach to Active Window
	ActiveDocument := com_invoke(Word,"ActiveDocument") ; get the active document
	com_invoke(ActiveDocument,"SaveAs", SaveFileName, wdFormatDocument := 0) ; Save the file
	com_release(ActiveDocument) ; cleanup
	com_release(Word) ; cleanup
	}


; **************************************************************************
; Author:	ahklerner
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	14:18
; Function Name:	Word_Save()
; 
; Save the current file
; 
; Syntax:
; 	Word_Save()
; Parameters:
;	None
; Return:
; 	Success = nothing
; 	Failure = nothing
; **************************************************************************
Word_Save(){
	Word := Word_Attach("A") ; Attach to Active Window
	ActiveDocument := com_invoke(Word,"ActiveDocument")
	com_invoke(ActiveDocument,"Save") ; save file
	com_release(ActiveDocument) ; cleanup
	com_release(Word) ; cleanup
	}

; **************************************************************************
; Author:	ahklerner
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	14:19
; Function Name:	Word_OpenDoc()
; 
; Syntax:
; 	Word_OpenDoc(FileName)
; Parameters:
; 1)	FileName	= The file name to open	
; Return:
; 	Success = nothing
; 	Failure = nothing
; **************************************************************************
Word_OpenDoc(FileName){
	Word := Word_Attach("N") ; Attach to New Window
	Documents := com_Invoke(Word, "Documents") 
	com_invoke(Documents,"Open",FileName) ; open file
	com_release(Documents) ; cleanup
	com_release(Word) ; cleanup
	}


; **************************************************************************
; Author:	ahklerner
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	14:19
; Function Name:	Word_GetActiveTitle()
; 
; Gets the title of the Active document
; 
; Syntax:
; 	Word_GetActiveTitle()
; Parameters:
;	None
; Return:
; 	Success = The title of the active document
; 	Failure = nothing
; **************************************************************************
Word_GetActiveTitle(){
	Word := Word_Attach("A") ; Attach to Active Window
	ActiveDocument := com_invoke(Word,"ActiveDocument")
	Name := com_invoke(ActiveDocument,"Name") ; get the document name
	com_release(ActiveDocument) ; cleanup
	com_release(Word) ; cleanup
	return Name
	}

;get document text


; **************************************************************************
; Author:	ahklerner
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	14:21
; Function Name:	Word_GetText()
; 
; Get the text of the Active Document
; 
; Syntax:
; 	Word_GetText()
; Parameters:
;	None
; Return:
; 	Success = The document text
; 	Failure = nothing
; **************************************************************************
Word_GetText(){
	Word := Word_Attach("A") ; Attach to Active Window
	ActiveDocument := com_invoke(Word,"ActiveDocument")
	Range := com_invoke(ActiveDocument,"Range")
	DocText := com_invoke(Range,"Text") ; get the document text
	com_release(Range) ; cleanup
	com_release(ActiveDocument) ; cleanup
	com_release(Word) ; cleanup
	return DocText
	}

;Get selected text

; **************************************************************************
; Author:	ahklerner
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	14:21
; Function Name:	Word_GetSelection()
; 
; Get the currently selected text in the active document
; 
; Syntax:
; 	Word_GetSelection()
; Parameters:
;	None
; Return:
; 	Success = the currently selected text
; 	Failure = nothing
; **************************************************************************
Word_GetSelection(){
	Word := Word_Attach("A") ; Attach to Active Window
	Selection := COM_Invoke(Word,"Selection")
	SelText := com_invoke(Selection,"Text") ; get the selected text
	com_release(Selection) ; cleanup
	com_release(Word) ; cleanup
	return SelText
	}



; **************************************************************************
; Author:	ahklerner
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	14:22
; Function Name:	Word_SetCurrentFont()
; 
; Set the font of the selected text or the current font
; 
; Syntax:
; 	Word_SetCurrentFont(sFontName="Times New Roman",sFontSize="10",bBold=0,bItalic=0,bCaps=0)
; Parameters:
; 1)	sFontName="Times New Roman"	= 	The name of the font to use
; 2)	sFontSize="10"	= 	The font size
; 3)	bBold=0	= 	True or False Bold
; 4)	bItalic=0	= 	True or False Italic
; 5)	bCaps=0	= 	True or False All Caps
; Return:
; 	Success = nothing
; 	Failure = nothing
; **************************************************************************
Word_SetCurrentFont(sFontName="Times New Roman",sFontSize="10",bBold=0,bItalic=0,bCaps=0){
	Word := Word_Attach("A") ; Attach to Active Window
	Selection := COM_Invoke(Word,"Selection")
	Font := COM_Invoke(Selection,"Font")
	COM_Invoke(Font,"Name=", sFontName) ; Font Name
	COM_Invoke(Font,"Size=", sFontSize) ; Font Size
	COM_Invoke(Font,"Bold=", bBold) ; Bold 
	COM_Invoke(Font,"Italic=", bItalic) ; Italic
	COM_Invoke(Font,"AllCaps=", bCaps) ; Caps
	com_release(Font) ; cleanup
	com_release(Selection) ; cleanup
	com_release(Word) ; cleanup
	}

; **************************************************************************
; Author:	ahklerner
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	14:24
; Function Name:	Word_Detach()
; 
; com_release wrapper for consistency
; 
; Syntax:
; 	Word_Detach(ObjWord)
; Parameters:
; 1)	ObjWord	= 	Handle to already opened object
; Return:
; 	Success = nothing
; 	Failure = nothing
; **************************************************************************
Word_Detach(ObjWord){
	com_release(ObjWord)
	}


; **************************************************************************
; Author:	ahklerner
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	14:26
; Function Name:	Word_Attach()
; 
; Gets the active Word Object or creates a new one
; 
; Syntax:
; 	Word_Attach(sInstance="A")
; Parameters:
; 1)	sInstance="A"	= Values:	
;                	A - Active Object - Default
;                	N - New Object
; Return:
; 	Success = handle to a word object
; 	Failure = Nothing
; **************************************************************************
Word_Attach(sInstance="A"){
	SetWinDelay, 0
	;Get The Last Used Instance 
	if (sInstance = "A") { ; Active
		ObjWord := COM_GetActiveObject("Word.Application")
		COM_Invoke(ObjWord,"Visible=",True)
		COM_Invoke(ObjWord,"Activate")
		}
	Else if (sInstance = "N") { ; New
		ObjWord := com_CreateObject("Word.Application")
		COM_Invoke(ObjWord,"Visible=",True)
		COM_Invoke(ObjWord,"Activate")
		}
	return ObjWord
	}
	


; **************************************************************************
; Author:	ahklerner
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	14:31
; Function Name:	Word_ObjGetHwnd()
; 
; Gets the hWnd for a given Word object
; 
; Syntax:
; 	Word_ObjGetHwnd(hObj)
; Parameters:
; 1)	hObj	= Handle to word object
; Return:
; 	Success = hWnd to hObj 's parent window
; 	Failure = nothing
; **************************************************************************
Word_ObjGetHwnd(hObj){
	DetectHiddenWindows, On
	SetTitleMatchMode, 2
	;http://support.microsoft.com/kb/310744
	;Generate a unique title
	sCaption := "[" . UUID() . "]"
	;set the title of the window
	com_invoke(hObj,"Caption=",sCaption)
	;get the current caption
	sCaption := com_invoke(hObj,"Caption") ; Should not be necessary, may not be
	if !hWnd := WinExist(sCaption) ; Should not Happen !?
		MsgBox No Window Could Be found!`nhWnd: %hWnd%`nsCaption: %sCaption%
	;return the caption to MS Word
	com_invoke(hObj,"Caption=","")
	return hWnd
	}


; **************************************************************************
; Author:	ahklerner
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	14:36
; Function Name:	Word_GetActiveHwnd()
; 
; Returns the hWnd for the active Word Object's Window
; 
; Syntax:
; 	Word_GetActiveHwnd()
; Parameters:
;	None
; Return:
; 	Success = hWnd
; 	Failure = nothing
; **************************************************************************
Word_GetActiveHwnd(){
	hWnd := Word_ObjGetHwnd(hObj := Word_Attach())
	Word_Detach(hObj)
	return hWnd
	}

; **************************************************************************
; Author:	ahklerner
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	14:45
; Function Name:	Word_Activate()
; 
; Activate & Show the Active Word Object
; 
; Syntax:
; 	Word_Activate()
; Parameters:
;	None
; Return:
; 	Success = 
; 	Failure = 
; **************************************************************************
Word_Activate(){
	WinActivate, % "ahk_id " . hWnd := Word_GetActiveHwnd()
	WinShow, % "ahk_id " . hWnd
	}	


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

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

A test script:

;
COM_Init()
Excel_Open()
Columns = ABCDEFGH
Rows = 8
Loop, Parse, Columns
	{
	a := A_LoopField
	Loop, %Rows%
		Excel_SetText(a, A_Index, a . A_Index)
	}
COM_Term()
;
a test script for getting value of a cell
;
COM_Init() ; Initialize COM
Excel_Open()
MsgBox Type some text in cell A1`nThen press #a
Return

#a::
MsgBox, % Excel_GetCell("A",1) ; <- change this
return

esc::
Excel_Close()
Sleep, 1000 ; sometimes i get rogue excel processes without this....why??
COM_Term()
exitapp

;#Include Excel.com.ahk
;#Include COM.ahk
;
;
example for inserting vbscript and executing it.
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)
}
;
;
; **************************************************************************
; Author:	NKRUZAN
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	06/29/2008	21:35
; Function Name:	Excel_GetCell()
; 
; get text from the specified cell
; 
; Syntax:
; 	Excel_GetCell(ColumnLetter, RowNumber)
;
; Example:
;    MsgBox % Excel_GetCell("A", 1)
;
; Parameters:
; 1)	 ColumnLetter	= 	The column letter 
; 2) RowNumber	= 	The row Number 
; Return:
; 	Success = the value of the cell
; 	Failure = nothing
; **************************************************************************

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
}

; **************************************************************************
; Author:	NKRUZAN
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	16:47
; Function Name:	Excel_SetText()
; 
; Syntax:
; 	Excel_SetText(ColumnLetter, RowNumber, Text)
; Parameters:
; 1)	 ColumnLetter	= 	The column letter to put the text in
; 2) RowNumber	= 	The row Number to put the text in
; 3) Text 		= 	The text to put in the cell
; Return:
; 	Success = nothing
; 	Failure = nothing
; **************************************************************************
Excel_SetText(ColumnLetter, RowNumber, Text){
	if !oExcel := COM_GetActiveObject("Excel.Application") { ; Get the Already Running Instance
		MsgBox Could not find Excel Instance.
		Return 
		}
	;Get the cell range
	if !oCells := COM_Invoke(oExcel,"Cells",RowNumber,Excel_GetColumnIndex(ColumnLetter)) {
		MsgBox Could not get cell range.
		Return 
		}
	COM_Invoke(oCells,"Formula=",Text)
	COM_Release(oCells)
	COM_Release(oExcel)
}

; get the index corresponding to the column

; **************************************************************************
; Author:	NKRUZAN
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	16:55
; Function Name:	Excel_GetColumnIndex()
; 
; Syntax:
; 	Excel_GetColumnIndex(ColumnLetter)
; Parameters:
; 1)	ColumnLetter	= 	
; Return:
; 	Success = 
; 	Failure = 
; **************************************************************************
Excel_GetColumnIndex(ColumnLetter){
	local t1 := 0, t2 = 0
	StringUpper, ColumnLetter, ColumnLetter
	Loop, Parse, ColumnLetter
		t%A_Index% := Asc(A_LoopField) - 64 
	t2 != 0 ? Col := (26 * t1) + t2 : Col := t1
	Return Col
	} 

; **************************************************************************
; Author:	NKRUZAN
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	16:54
; Function Name:	Excel_Open()
; 
; Create a new excel instance
; 
; Syntax:
; 	Excel_Open()
; Parameters:
;	None
; Return:
; 	Success = nothing
; 	Failure = nothing
; **************************************************************************

Excel_Open(){
	oExcel := COM_CreateObject("Excel.Application")
	COM_Invoke(oExcel,"Visible=",True)
	COM_Invoke(oExcel,"Activate")
	oWorkbooks := COM_Invoke(oExcel,"Workbooks")
	COM_Invoke(oWorkbooks,"Add")
	COM_Release(oWorkbooks)
	COM_Release(oExcel)
	}
	
Excel_Close(){
	oExcel := COM_GetActiveObject("Excel.Application")
	COM_Invoke(oExcel,"Quit")
	COM_Release(oExcel)
	Sleep, 1000
	}


; **************************************************************************
; Author:	NKRUZAN
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	16:55
; Function Name:	Excel_OpenDoc()
; 
; Open the specified document....
; 
; Syntax:
; 	Excel_OpenDoc(FileName)
; Parameters:
; 1)	FileName	= 	should be a FULL PATH
; Return:
; 	Success = 
; 	Failure = 
; **************************************************************************
Excel_OpenDoc(FileName){
	oExcel := COM_CreateObject("Excel.Application")
	COM_Invoke(oExcel,"Visible=",True)
	COM_Invoke(oExcel,"Activate")
	oWorkbooks := COM_Invoke(oExcel,"Workbooks")
	COM_Invoke(oWorkbooks,"Open",FileName) ; open the file
	COM_Release(oWorkbooks)
	COM_Release(oExcel)
	}


; **************************************************************************
; Author:	NKRUZAN
; Language:	AutoHotkey v1.0.47.06
; Creation Date:	05/19/2008	16:55
; Function Name:	Excel_GetActiveTitle()
; 
;Get the title of the Active workbook open in Excel
; 
; Syntax:
; 	Excel_GetActiveTitle()
; Parameters:
;	None
; Return:
; 	Success = 
; 	Failure = 
; **************************************************************************
Excel_GetActiveTitle(){
	oExcel := COM_CreateObject("Excel.Application")
	COM_Invoke(oExcel,"Visible=",True)
	COM_Invoke(oExcel,"Activate")
	oActiveWorkbook := COM_Invoke(oExcel,"ActiveWorkbook")
	Name := COM_Invoke(oActiveWorkbook,"Name") ; Get the name
	COM_Release(oActiveWorkbook)
	COM_Release(oExcel)
	return Name
	}

;

;
Code snippets from later posts in this thread
;


;
Sean - Yet another (inconvenient) method to load an office document:
;

<!-- m -->http://www.autohotke... ... 771#197771<!-- m -->

;
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:
;

<!-- m -->http://www.autohotke... ... 771#197771<!-- m -->

;
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
;

;
Sean - Getting active excel instance
;

<!-- m -->http://www.autohotke... ... 761#259761<!-- m -->

;
COM_Init()
oExcel := GetExcel()
oWord  := GetWord()
COM_Term()
Return

GetExcel(hWnd = 0)
{
   DetectHiddenWindows, On
   If Not   hWnd
   WinGet, hWnd, ID, ahk_class XLMAIN
   ControlGet, hWnd, hWnd,, EXCEL71, ahk_id %hWnd%
   If   hWnd
   Return   GetNativeOM(hWnd)
}

GetWord(hWnd = 0)
{
   DetectHiddenWindows, On
   If Not   hWnd
   WinGet, hWnd, IDLast, ahk_class OpusApp
   ControlGet, hWnd, hWnd,, _WwG1, ahk_id %hWnd%
   If   hWnd
   Return   GetNativeOM(hWnd)
}

GetNativeOM(hWnd)
{
   Static   pfn,iid
   If Not   pfn
      pfn := DllCall("GetProcAddress", "Uint", DllCall("LoadLibrary", "str", "oleacc"), "str", "AccessibleObjectFromWindow"), COM_GUID4String(iid, "{00020400-0000-0000-C000-000000000046}")
   If   DllCall(pfn,"Uint",hWnd,"Uint",-16,"Uint",&iid,"UintP",pwin)=0
      papp := COM_Invoke(pwin,"Application"), COM_Release(pwin)
   Return   papp
}
;

;
enigmatiqk - various functions
;

<!-- m -->http://www.autohotke... ... 285#260285<!-- m -->

;
; **************************************************************************
; Author:   BENJAMIN
; Language:   AutoHotkey v1.0.47.06
; Creation Date:   02/04/2009   16:55
; Function Name:   Excel_SetSheet(sheet) / Excel_GetSheet()
;
;Get or Set the working sheet used
;
; Syntax:
;    Excel_SetSheet(sheet) / Excel_GetSheet()
; Parameters:
;   thisSheet
; Return:
;    Success =
;    Failure =
; **************************************************************************
 Excel_SetSheet(thisSheet){
   global
    if !oExcel := COM_GetActiveObject("Excel.Application") { ; Get the Already Running Instance
         MsgBox Could not find Excel Instance.
         Return
      }
   
   sheet := thisSheet
   
   oWorkbook := COM_Invoke(oExcel,"ActiveWorkbook")
   ws := COM_Invoke(oWorkbook, "Worksheets",sheet)
   COM_Invoke(ws, "activate")
 }

 Excel_GetSheet(){
   global
   return sheet
 }
 
 Excel_CheckSheet(thisSheet){
    if !oExcel := COM_GetActiveObject("Excel.Application") { ; Get the Already Running Instance
         MsgBox Could not find Excel Instance.
         Return
      }
   
   test := thisSheet

   oWorkbook := COM_Invoke(oExcel,"ActiveWorkbook")
   
   if COM_Invoke(oWorkbook, "Worksheets",test)
      return 1
   else
      return 0
 }
 
 ; **************************************************************************
; Author:   BENJAMIN
; Language:   AutoHotkey v1.0.47.06
; Creation Date:   02/04/2009   16:55
; Function Name:   Excel_AddSheet()
;
; Add a sheet in first position with name sheetX
;
; Syntax:
;    Excel_AddSheet()
; Parameters:
;   thisSheet
; Return:
;    Success =
;    Failure =
; **************************************************************************
  Excel_AddSheet(thispos, name=""){
    if !oExcel := COM_GetActiveObject("Excel.Application") { ; Get the Already Running Instance
         MsgBox Could not find Excel Instance.
         Return
      }

   oWorkbook := COM_Invoke(oExcel,"ActiveWorkbook")
   oWorksheets := COM_Invoke(oWorkbook, "Worksheets")
   
   pos := thispos
   Excel_SetSheet(pos)
   
   oWorksheet := COM_Invoke(oWorksheets,"Add")
   
   ifNotEqual,name,
      COM_Invoke(oWorksheet, "Name",name)
 }
 
 
 ;position = B (before) or A(after)
  Excel_MoveSheet(position,thisSheet,thisSheet2){
   global
    if !oExcel := COM_GetActiveObject("Excel.Application") { ; Get the Already Running Instance
         MsgBox Could not find Excel Instance.
         Return
      }
   
   sheet = Sheets[%thisSheet%].Move
   sheet2 := thisSheet2
   
   
   oWorkbook := COM_Invoke(oExcel,"ActiveWorkbook")
   
   ws2 := COM_Invoke(oWorkbook, "Sheets",sheet2)
   
   ifEqual,position,B
      COM_Invoke(oWorkbook, sheet,"+" ws2)
   else
      COM_Invoke(oWorkbook, sheet,"-0", "+" ws2)
 }
 
Excel_RenameSheet(thisSheet,name){
   global
    if !oExcel := COM_GetActiveObject("Excel.Application") { ; Get the Already Running Instance
         MsgBox Could not find Excel Instance.
         Return
      }
   
   sheet := thisSheet
   
   oWorkbook := COM_Invoke(oExcel,"ActiveWorkbook")
   ws := COM_Invoke(oWorkbook, "Worksheets",sheet)
   COM_Invoke(ws, "Name",name)
 }
;

;
enigmatiqk - Font styling
;


<!-- m -->http://www.autohotke... ... 500#260500<!-- m -->

;
 ; put param=1 set Style = true, param=2 set Style = false
 Excel_StyleFont(ColumnLetter, RowNumber, Style,param){
   if !oExcel := COM_GetActiveObject("Excel.Application") { ; Get the Already Running Instance
      MsgBox Could not find Excel Instance.
      Return
      }
   ;Get the cell range
   if !oCells := COM_Invoke(oExcel,"Cells",RowNumber,Excel_GetColumnIndex(ColumnLetter)) {
      MsgBox Could not get cell range.
      Return
      }
   oFont := COM_Invoke(oCells,"Font")
   COM_Invoke(oFont,Style,param)
   
   COM_Release(oFont)
   COM_Release(oCells)
   COM_Release(oExcel)
}
;

;
enigmatiqk - Cell styling
;

<!-- m -->http://www.autohotke... ... 559#260559<!-- m -->

;
Excel_StyleCell(ColumnLetter, RowNumber, Style,param){
   if !oExcel := COM_GetActiveObject("Excel.Application") { ; Get the Already Running Instance
      MsgBox Could not find Excel Instance.
      Return
      }
   ;Get the cell range
   if !oCells := COM_Invoke(oExcel,"Cells",RowNumber,Excel_GetColumnIndex(ColumnLetter)) {
      MsgBox Could not get cell range.
      Return
      }

   IfInString,Style,Color
   {
      hex = 0x%param%
      SetFormat, integer, d
      hex -= 0
   }
    
   oInterior := COM_Invoke(oCells,"Interior")
   COM_Invoke(oInterior,"Color",hex)
   
   COM_Release(oInterior)
   COM_Release(oCells)
   COM_Release(oExcel)
}
;

;
Sean - SaveAs text
;

<!-- m -->http://www.autohotke... ... 373#263373<!-- m -->

;
COM_Invoke(oWorkbook, "SaveAs", "C:\1.txt", -4158) ; xlCurrentPlatformText
;

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

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

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

ahklerner
  • Members
  • 1386 posts
  • Last active: Oct 08 2014 10:29 AM
  • Joined: 26 Jun 2006
MS Outlook:
Please read this page if you get an access error.
<!-- m -->http://office.micros... ... 91033.aspx<!-- m -->

Test script: (you need to have outlook open and double click on an email so that it is in it's own window, then run the script)
;OUTLOOK TEST SCRIPT
com_CoInitialize()
MsgBox % Outlook_GetMessageText()
com_CoUnInitialize()
exitapp

;Get Currently Opened Email Text 
Outlook_GetMessageText(){
	if !Outlook := COM_GetActiveObject("Outlook.Application") {
		MsgBox Could not start Outlook Instance.
		Return 
		}
	if !ActiveInspector := com_invoke(Outlook,"ActiveInspector"){
		MsgBox Could not get Inspector.
		Return 
		}
	if !CurrentItem := com_invoke(ActiveInspector,"CurrentItem"){
		MsgBox Could not load current Document.
		Return 
		}
	if !Body := com_invoke(CurrentItem,"Body"){
		MsgBox Could not get document text.
		Return 
		}
	com_release(CurrentItem)
	com_release(ActiveInspector)
	com_release(Outlook)
	return Body
	}

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

wygd
  • Members
  • 10 posts
  • Last active: Aug 02 2016 06:02 AM
  • Joined: 04 Nov 2007
thanks fo your work.

I need some examples.

  • Guests
  • Last active:
  • Joined: --
Thanks ahklerner.

Laszlo
  • Moderators
  • 4713 posts
  • Last active: Mar 31 2012 03:17 AM
  • Joined: 14 Feb 2005
Excellent! I have always struggled in MS Word to get (and change) the selected text, without the clipboard (which has to be saved and restored each time). It looks like we have a good solution now. Thank you for sharing your work!

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

I need some examples.

What Kind of examples are you looking for? Please provide some example examples. :D

Thank you for sharing your work!

Glad it was useful to you. :)
Posted Image
ʞɔпɟ əɥʇ ʇɐɥʍ

heresy
  • Members
  • 291 posts
  • Last active: Sep 26 2008 10:47 PM
  • Joined: 11 Mar 2008
hey ahklerner
i haven't check it yet but seems you made necessary one
when i get on desk, i'll test your functions with Word/Excel 2007 versions
it would be my favorite one thanks for sharing!

EDIT : very cool.
confirmed on 2007 version too.
i'll keep looking and cheer for this
you've done a great job ahklerner
we don't need VBA now :lol:
Easy WinAPI - Dive into Windows API World
Benchmark your AutoHotkey skills at PlayAHK.com

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

n-l-i-d
  • Guests
  • Last active:
  • Joined: --
This might help too: Office 2003 VBA language reference @ MSDN (older Office versions might user other calls)

ahklerner
  • Members
  • 1386 posts
  • Last active: Oct 08 2014 10:29 AM
  • Joined: 26 Jun 2006
added
; Word_SetCurrentFont(Font,FontSize,Bold,Italic,Caps)
and updated test script. other various changes.
Posted Image
ʞɔпɟ əɥʇ ʇɐɥʍ

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

danalec
  • Members
  • 225 posts
  • Last active: Oct 03 2014 05:31 PM
  • Joined: 20 Jul 2006
Excel one is interesting, thanks for it ;) keep working!

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