AutoHotkey Homepage AutoHotkey Community
Let's help each other out
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

MS Office Automation Functions (via COM) [thanks Sean]
Goto page Previous  1, 2, 3, 4, 5, 6 ... 12, 13, 14  Next
 
Post new topic   Reply to topic    AutoHotkey Community Forum Index -> Scripts & Functions
View previous topic :: View next topic  

Should this be continued?
Yes
93%
 93%  [ 14 ]
No
6%
 6%  [ 1 ]
Total Votes : 15

Author Message
Sean



Joined: 12 Feb 2007
Posts: 2185
PostPosted: Tue Mar 31, 2009 11:11 am    Post subject: Reply with quote
You can close hanged applications using Process, Close, ....
But, why bother to do through COM_GetActiveObject() when you have the object to use already? As COM_GetActiveObject() is reliable only when one instance is running, use it when it's absolutely necessary. You may use the following instead for office applications.

Code:
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
}
Back to top
View user's profile Send private message
enigmatiqk



Joined: 13 Apr 2007
Posts: 28
PostPosted: Wed Apr 01, 2009 9:23 am    Post subject: Reply with quote
i have place this code everywhere CreateObject is used:

Code:
if !oExcel := COM_GetActiveObject("Excel.Application") { ; Get the Already Running Instance
         oExcel := COM_CreateObject("Excel.Application")
}


and now i haven't any error when more one excel.exe process exit
but ahk don't close him in this case.

someone know how :
- i can check if an sheet exist?
- i can change sheet position?

i remember a post who explain how to find the com command, but i don't retrieve it ...
Back to top
View user's profile Send private message MSN Messenger
enigmatiqk



Joined: 13 Apr 2007
Posts: 28
PostPosted: Thu Apr 02, 2009 3:41 pm    Post subject: Reply with quote
i have done some changes, all work on xppro sp2 and office 2003

excel.com.ahk:
add some method:
- Excel_SetSheet(sheet) : active a sheet (getcell and settext are used on active sheet
- Excel_GetSheet() : get active sheet
- Excel_CheckSheet(thisSheet) : check if a sheet exist, but need
- Excel_AddSheet(position, name) : add to position a sheet (sheetX if name is null)
- Excel_RenameSheet(X,name) : rename sheet(X) to name
- Excel_MoveSheet(pos,sheet1,sheet2) : pos = "B"(efore) or "A"(fter), sheet1 is moved before/after sheet2

Code:

; **************************************************************************
; 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)
 }


Last edited by enigmatiqk on Fri Apr 03, 2009 12:03 pm; edited 5 times in total
Back to top
View user's profile Send private message MSN Messenger
Sean



Joined: 12 Feb 2007
Posts: 2185
PostPosted: Thu Apr 02, 2009 11:00 pm    Post subject: Reply with quote
I strongly request to delete the copied COM.ahk from the post.
You can freely customize it as you want, but, never post it. If you felt the need of it, just post the part you customized it, although your additions here were absolutely unnecessary.
There should be only one source of it in the forum for maintenance. Maintenance already became a headache to me, so don't make it any worse.
Back to top
View user's profile Send private message
enigmatiqk



Joined: 13 Apr 2007
Posts: 28
PostPosted: Fri Apr 03, 2009 7:39 am    Post subject: Reply with quote
Sean wrote:
I strongly request to delete the copied COM.ahk from the post.
You can freely customize it as you want, but, never post it. If you felt the need of it, just post the part you customized it, although your additions here were absolutely unnecessary.
There should be only one source of it in the forum for maintenance. Maintenance already became a headache to me, so don't make it any worse.


i'm sorry, i will delete com and excel code

but the files must be updated in the first post ...
Back to top
View user's profile Send private message MSN Messenger
Sean



Joined: 12 Feb 2007
Posts: 2185
PostPosted: Fri Apr 03, 2009 9:01 am    Post subject: Reply with quote
enigmatiqk wrote:
i'm sorry, i will delete com and excel code
Thanks. COM_Error(0)/COM_Error(1) turns OFF/ON the error message box.
Code:
Set stockSheet = workBook.Sheets.Add( , workBook.Sheets(workBook.Sheets.Count))
I already explained it here:
http://www.autohotkey.com/forum/viewtopic.php?t=16565&start=75

The code in that thread is the exact one which you have to resort to when the following code will not work for you.
Code:
stockSheet := COM_Invoke(WorkBook, "Sheets.Add", "-0", "+" COM_Invoke(WorkBook, "Sheets", COM_Invoke(WorkBook, "Sheets.Count")))
Back to top
View user's profile Send private message
n-l-i-d
Guest
PostPosted: Fri Apr 03, 2009 9:18 am    Post subject: Reply with quote
Might be interesting too: OpenOffice Automation Bridge (ActiveX)

Cool
Back to top
enigmatiqk



Joined: 13 Apr 2007
Posts: 28
PostPosted: Fri Apr 03, 2009 9:30 am    Post subject: Reply with quote
Sean wrote:
enigmatiqk wrote:
i'm sorry, i will delete com and excel code
Thanks. COM_Error(0)/COM_Error(1) turns OFF/ON the error message box.


its ok if i want create a method in com.ahk
but i want use current com_invoke

i understand thats a stupid method (resumenext), but i would not copy the big method com_invoke just to don't show msgbox ...
just a noob method because i'm afraid to do something into com_invoke

Sean wrote:

Code:
Set stockSheet = workBook.Sheets.Add( , workBook.Sheets(workBook.Sheets.Count))
I already explained it here:
http://www.autohotkey.com/forum/viewtopic.php?t=16565&start=75


i haven't read all post, but i have seen the copy (so move too) sheet method from your link

Sean wrote:

The code in that thread is the exact one which you have to resort to when the following code will not work for you.
Code:
stockSheet := COM_Invoke(WorkBook, "Sheets.Add", "-0", "+" COM_Invoke(WorkBook, "Sheets", COM_Invoke(WorkBook, "Sheets.Count")))


i think its the code to add a sheet into defined position, but i haven't understand : "The code in that thread is the exact one which you have to resort to when the following code will not work for you.", sorry -_-


thanks for all, specially to Sean Smile

n-l-i-d: i'm too busy to read it now, but first line are interested Smile Very Happy Very Happy Very Happy
Back to top
View user's profile Send private message MSN Messenger
Sean



Joined: 12 Feb 2007
Posts: 2185
PostPosted: Fri Apr 03, 2009 10:17 am    Post subject: Reply with quote
enigmatiqk wrote:
its ok if i want create a method in com.ahk
No, you didn't/don't need new functions. Just call COM_Error(0), afterwards no error message box will ever pop up.
Code:
stockSheet := COM_Invoke(WorkBook, "Sheets.Add", "-0", "+" COM_Invoke(WorkBook, "Sheets", COM_Invoke(WorkBook, "Sheets.Count")))
Just use/adopt this code to your need. Yes, this code will add a new sheet after the last one. Move is similar.
Code:
COM_Invoke(Workbook, "Sheets('Data').Move", "+" COM_Invoke(Workbook, "Sheets", 1)) ; Before
COM_Invoke(Workbook, "Sheets('Data').Move", "-0", "+" COM_Invoke(Workbook, "Sheets", COM_Invoke(Workbook, "Sheets.Count"))) ; After
Back to top
View user's profile Send private message
enigmatiqk



Joined: 13 Apr 2007
Posts: 28
PostPosted: Fri Apr 03, 2009 12:06 pm    Post subject: Reply with quote
thank you, all works fine now Smile

i haved updated my last message with excel.com.ahk added code.

[EDIT]
oh ! , i don't understand param "-0" would mean

and about "+", i know the COM.AHK post:
Quote:
There are some occasions where another COM Object ObjPrm should be a parameter. In that case, prefix it with "+" like:
Code:
COM_Invoke(Object, "Function", "+" . ObjPrm) ; never directly prefix it like +ObjPrm.


but i don't see the difference with "+" or without "+", can you have a simple method un vbs/vba and the adapter method un ahk (with "+") please?
[/EDIT]


[EDIT]
i have found how change font style:
Style have been test with bold and italic (if you use bold then italic, the text will be bold-italic)

Excel_StyleFont("A", 1, "Bold",1) set Bold = true
Excel_StyleFont("A", 1, "Bold",0) set Bold = false
Excel_StyleFont("A", 1, "Bold",?) i don't know if the fourth param can have other value

Code:
 ; 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)
}


i don't know how change color, color function show error msg
and its the same if i try to change background color of a cell ("Interior")


[/EDIT]
Back to top
View user's profile Send private message MSN Messenger
Sean



Joined: 12 Feb 2007
Posts: 2185
PostPosted: Fri Apr 03, 2009 1:59 pm    Post subject: Reply with quote
enigmatiqk wrote:
but i don't see the difference with "+" or without "+", can you have a simple method un vbs/vba and the adapter method un ahk (with "+") please?
Without the prefix "+", the numeric parameter will be interpreted just as a 32-bit integer, not as an object.
For example, "0" will be just an integer zero, "+0" will be Nothing in VB. BTW, "-0" will be NULL in VB.
I suppose now you got the picture needed to code with COM_Invoke().
Back to top
View user's profile Send private message
enigmatiqk



Joined: 13 Apr 2007
Posts: 28
PostPosted: Fri Apr 03, 2009 3:20 pm    Post subject: Reply with quote
thanks Smile

but i don't found how i can change the background color Sad
functions color, background, color-background isn't known Sad

[EDIT]
i have found it:

to change background color:
Excel_StyleCell("A", 1, "Color","AB12F5")

Code:
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)
}

[/EDIT]
Back to top
View user's profile Send private message MSN Messenger
hughman



Joined: 11 Feb 2007
Posts: 92
PostPosted: Thu Apr 16, 2009 10:44 am    Post subject: Reply with quote
Code:

ActiveWorkbook.Saveas Filename="C:\list.txt" Format="xlText"


How to convert the VBA code like above to COM expression?
Back to top
View user's profile Send private message
Sean



Joined: 12 Feb 2007
Posts: 2185
PostPosted: Thu Apr 16, 2009 2:26 pm    Post subject: Reply with quote
hughman wrote:
Code:
ActiveWorkbook.Saveas Filename="C:\list.txt" Format="xlText"
Is it correct? Anyway read the documentation, all are there.
http://msdn.microsoft.com/en-us/library/bb214129.aspx
Back to top
View user's profile Send private message
hughman



Joined: 11 Feb 2007
Posts: 92
PostPosted: Thu Apr 16, 2009 3:27 pm    Post subject: Reply with quote
Sean wrote:
Is it correct? Anyway read the documentation, all are there.
http://msdn.microsoft.com/en-us/library/bb214129.aspx


Thx. That's the Excel macro I copied.

According to the msdn
Code:
COM_Invoke(oWorkbook, "SaveAs", "C:\1.txt")

This can saveas correctly. But I have to change the file format, so I write:
Code:
COM_Invoke(oWorkbook, "SaveAs", "C:\1.txt", "xlText")

It throw a error. why?

MSDN syntax:
expression.SaveAs(Filename,FileFormat,Password,WriteResPassword,ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru,TextCodePage, TextVisualLayout)

My code:
Code:

COM_Init()
oExcel := COM_CreateObject("Excel.Application")
COM_Invoke(oExcel,"Visible=",True)
oWorkbook := COM_Invoke(oExcel,"Workbooks.Open", "C:\1.xls")
COM_Invoke(oWorkbook, "SaveAs", "C:\1.txt", "xlText")
COM_Release(oWorkbook)
COM_Release(oExcel)
COM_Term()
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    AutoHotkey Community Forum Index -> Scripts & Functions All times are GMT
Goto page Previous  1, 2, 3, 4, 5, 6 ... 12, 13, 14  Next
Page 5 of 14

 
Jump to:  
You can post new topics in this forum
You can reply to topics in this forum


Powered by phpBB © 2001, 2005 phpBB Group