AutoHotkey Community
It is currently May 26th, 2012, 4:42 pm

View unanswered posts | View active topics


Board index » AutoHotkey » Scripts

All times are UTC [ DST ]



Post new topic Reply to topic  Page 5 of 20
  [ 296 posts ]  Go to page Previous  1, 2, 3, 4, 5, 6, 7, 8 ... 20  Next
  Print view | E-mail friend Previous topic | Next topic 

Should this be continued?
Poll ended at June 18th, 2008, 11:34 pm
Yes 93%  93%  [ 14 ]
No 7%  7%  [ 1 ]
Total votes : 15
Author Message
Sean
 Post subject:
PostPosted: March 30th, 2009, 9:08 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
hughman wrote:
I have noticed that you update the thread of COM.ahk. And I have tried it with the same result.
Be sure you overwrite the older one with the newer COM.ahk.

Quote:
Now I know that the round brackets can't be used for Com_Invoke.
Actually you can use the round brackets too, although I didn't explicitly mention it as I'd like to keep the delimiters to the minimum. As VBS uses the round brackets exclusively and reserves the square bracket for other purpose, however, I couldn't ignore it.

Maybe, Sheet1 is not the name of the sheet. I recommend using index over name which is language-dependent.
Code:
oCell := COM_Invoke(oWorkbooks, "Worksheets[1].Cells", 1, 1)

And I noticed that you used oWorkbooks. How did you obtain it? Is it Workbook collection or actually a Workbook?


Report this post
Top
 Profile  
Reply with quote  
enigmatiqk
 Post subject:
PostPosted: March 30th, 2009, 9:52 am 
Offline

Joined: April 13th, 2007, 8:28 am
Posts: 28
Sean wrote:
And I noticed that you used oWorkbooks. How did you obtain it? Is it Workbook collection or actually a Workbook?


Code:
oWorkbooks := COM_Invoke(oExcel,"Workbooks")

this line is in openDoc and in other functions



sorry but i can't set this line :
Code:
COM_Invoke(oWorkbooks, "Worksheets[1].Activate")

i have tried to replace "sheet1" by the sheet's name or number but it's not right : "Worksheets" is not recognized :(

the complete function:
Code:
Excel_OpenDoc(FileName){
   oExcel := COM_CreateObject("Excel.Application")
   COM_Invoke(oExcel,"Visible=",True)
   ;DEBUG ne marche pas sous 2003?
   ;COM_Invoke(oExcel,"Activate=",True)
   oWorkbooks := COM_Invoke(oExcel,"Workbooks")
   COM_Invoke(oWorkbooks,"Open",FileName) ; open the file
   COM_Invoke(oWorkbooks, "Worksheets[1].Activate")
   COM_Release(oWorkbooks)
   COM_Release(oExcel)
   }


it's possible to update the excel.com.ahk in 2nd post with all working updates please?


Report this post
Top
 Profile  
Reply with quote  
Sean
 Post subject:
PostPosted: March 30th, 2009, 12:43 pm 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
enigmatiqk wrote:
sorry but i can't set this line :
Code:
COM_Invoke(oWorkbooks, "Worksheets[1].Activate")
Workbooks is just a Workbook-Collection, not a Workbook itself, so doesn't have Worksheets.

Replace
Code:
oWorkbooks := COM_Invoke(oExcel,"Workbooks")
COM_Invoke(oWorkbooks,"Open",FileName) ; open the file
COM_Invoke(oWorkbooks, "Worksheets[1].Activate")
COM_Release(oWorkbooks)

with
Code:
oWorkbooks := COM_Invoke(oExcel,"Workbooks")
oWorkbook  := COM_Invoke(oWorkbooks,"Open",FileName)
COM_Invoke(oWorkbook, "Worksheets[1].Activate")
COM_Release(oWorkbook)
COM_Release(oWorkbooks)


Report this post
Top
 Profile  
Reply with quote  
enigmatiqk
 Post subject:
PostPosted: March 30th, 2009, 1:53 pm 
Offline

Joined: April 13th, 2007, 8:28 am
Posts: 28
thank u !!

so i have use that to get the value of a cell:

Code:
   if !oExcel := COM_GetActiveObject("Excel.Application") { ; Get the Already Running Instance
      MsgBox Could not find Excel Instance.
      Return
      }
    
[b]oWorkbook := COM_Invoke(oExcel,"ActiveWorkbook")[/b]

   if !oCells := COM_Invoke(oWorkbook, "Worksheets[1].Cells", 1, 1) {
      MsgBox Could not get cells.
      Return
      }


but i would like to know how to declare oWorkbook as global variable
i seem like stupid to get it in every function

[EDIT]
i just want alert other about some error:
check EXCEL.EXE process, an close him/them if open
[/EDIT]


Report this post
Top
 Profile  
Reply with quote  
Sean
 Post subject:
PostPosted: March 31st, 2009, 12:00 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
enigmatiqk wrote:
i seem like stupid to get it in every function
Not stupid at all. I actually recommend it, always newly retrieve it whenever need it.


Report this post
Top
 Profile  
Reply with quote  
hughman
 Post subject:
PostPosted: March 31st, 2009, 3:56 am 
Offline

Joined: February 11th, 2007, 4:10 pm
Posts: 185
Sean wrote:
Be sure you overwrite the older one with the newer COM.ahk.


Yes, It's my fault. I have two instances of Autohotkey one of which I have only overwrote :)

Now, It works fine. Thank u for your patience.

Howerver， if I use the function Excel_GetCell to get the value of a cell, another error is throwed -- The calling of the function Cells is injected. why?

This is my test code:
Code:
COM_Init()
file := A_ScriptDir . "\test.xls"
oExcel := COM_CreateObject("Excel.Application")
COM_Invoke(oExcel,"Visible",false)
oWorkbook := COM_Invoke(oExcel,"Workbooks.Open",file)
COM_Invoke(oWorkbook, "Activate")

;~ oCell := COM_Invoke(oExcel, "Cells", 1, 1)
;~ Value := COM_Invoke(oCell,"Value")

MsgBox, % Excel_GetCell("A",1)
;~ MsgBox, % Value

COM_Invoke(oExcel,"Quit")
COM_Release(oExcel)
COM_Release(oWorkbook)
COM_Release(oCell)
Sleep, 1000
COM_Term()
ExitApp
[/code]


Report this post
Top
 Profile  
Reply with quote  
enigmatiqk
 Post subject:
PostPosted: March 31st, 2009, 8:34 am 
Offline

Joined: April 13th, 2007, 8:28 am
Posts: 28
my get_cell methode after correction :

Code:
Excel_GetCell(ColumnLetter, RowNumber){

   if !oExcel := COM_GetActiveObject("Excel.Application") { ; Get the Already Running Instance
      MsgBox Could not find Excel Instance.
      Return
      }
    
     oWorkbook := COM_Invoke(oExcel,"ActiveWorkbook")
   if !oCells := COM_Invoke(oWorkbook, "Worksheets[1].Cells", RowNumber,Excel_GetColumnIndex(ColumnLetter)) {
      MsgBox Could not get cells.
      Return
      }
    
   Value := COM_Invoke(oCells,"Value")
   
   COM_Release(oCells)
   COM_Release(oExcel)
   return Value
}


someone have an idea to bypass the case where an excel.exe process already exist?
this process aren't closed when the app get error or be kill (or ...)

[EDIT]
i would like get and set text on other sheet but i don't know how i can change sheet ...

set function:
Code:
COM_Invoke(oExcel,"Cells",RowNumber,Excel_GetColumnIndex(ColumnLetter))

sheet isn't a parameter in the invoke

get function:
Code:
COM_Invoke(oWorkbook, "Worksheets[1].Cells", RowNumber,Excel_GetColumnIndex(ColumnLetter))

i have tried to use a parameter to change Worksheets[1] to Worksheets[ws] but i have got errors ...

someone have any idea?
[/EDIT]


Report this post
Top
 Profile  
Reply with quote  
pajenn
 Post subject:
PostPosted: March 31st, 2009, 11:56 am 
Offline

Joined: February 7th, 2009, 11:28 pm
Posts: 384
Laszlo wrote:
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):
Code:
   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)?


I have a few general questions about COM if someone would indulge me; that is to say, I don't understand it, but I've found Sean's COM scripts very useful in the past.

Questions: What applications can ahk-COM be used on? In particular, I use a program called Scientific Workplace 5.5 (SWP), which does not work with AHK's built-in A_CaretX/Y variables. The post I quote above mentions using COM in Word to get "pos after caret". Might it be possible to use COM to get caret in SWP too? How to check, if maybe? If yes, can it be done by mimicking COM scripts here for Word etc., or would it require understanding COM?

_________________
Hardware: 1.8 GHz laptop with 4 GB ram, Windows XP/SP3
Software: Prevx, Privatefirewall, KeyScrambler.


Report this post
Top
 Profile  
Reply with quote  
Sean
 Post subject:
PostPosted: March 31st, 2009, 12:11 pm 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
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
}


Report this post
Top
 Profile  
Reply with quote  
enigmatiqk
 Post subject:
PostPosted: April 1st, 2009, 10:23 am 
Offline

Joined: April 13th, 2007, 8:28 am
Posts: 28
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 ...


Report this post
Top
 Profile  
Reply with quote  
enigmatiqk
 Post subject:
PostPosted: April 2nd, 2009, 4:41 pm 
Offline

Joined: April 13th, 2007, 8:28 am
Posts: 28
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 April 3rd, 2009, 1:03 pm, edited 5 times in total.

Report this post
Top
 Profile  
Reply with quote  
Sean
 Post subject:
PostPosted: April 3rd, 2009, 12:00 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
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.


Report this post
Top
 Profile  
Reply with quote  
enigmatiqk
 Post subject:
PostPosted: April 3rd, 2009, 8:39 am 
Offline

Joined: April 13th, 2007, 8:28 am
Posts: 28
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 ...


Report this post
Top
 Profile  
Reply with quote  
Sean
 Post subject:
PostPosted: April 3rd, 2009, 10:01 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
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/viewtop ... 5&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")))


Report this post
Top
 Profile  
Reply with quote  
n-l-i-d
 Post subject:
PostPosted: April 3rd, 2009, 10:18 am 
Might be interesting too: OpenOffice Automation Bridge (ActiveX)

8)


Report this post
Top
  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  Page 5 of 20
  [ 296 posts ]  Go to page Previous  1, 2, 3, 4, 5, 6, 7, 8 ... 20  Next

Board index » AutoHotkey » Scripts

All times are UTC [ DST ]

Who is online

Users browsing this forum: Rajat and 11 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

Search for:
Powered by phpBB® Forum Software © phpBB Group