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 ... 5, 6, 7 ... 18, 19, 20  Next
 
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
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: 2462
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: 2462
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: 166
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: 2462
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: 166
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
Sean



Joined: 12 Feb 2007
Posts: 2462
PostPosted: Thu Apr 16, 2009 11:01 pm    Post subject: Reply with quote
hughman wrote:
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?

You have to use a number for that: XlFileFormat.
Code:
COM_Invoke(oWorkbook, "SaveAs", "C:\1.txt", -4158) ; xlCurrentPlatformText
Back to top
View user's profile Send private message
christopher_rath



Joined: 11 Jun 2009
Posts: 4
Location: Ottawa, Canada
PostPosted: Thu Jun 11, 2009 12:19 am    Post subject: Outlook VBA & message window Reply with quote
I would like to use AutoHotKey to run some VBA in Outlook. From searching this forum it looks like the best way to do this is through use of the COM library. My initial problem, as I look to try and write some AHK code to do this is that I'm not sure how to determine that a message window has focus before I execute the VBA. Any suggestion?

Thanks,
Christopher
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
Astrognaw



Joined: 27 Mar 2009
Posts: 26
PostPosted: Mon Aug 10, 2009 9:38 pm    Post subject: Reply with quote
Thank you for these! Any plans on finishing the Access functions? I'd be grateful!
Back to top
View user's profile Send private message
tank



Joined: 21 Dec 2007
Posts: 3700
Location: Louisville KY USA
PostPosted: Mon Aug 10, 2009 9:45 pm    Post subject: Re: Outlook VBA & message window Reply with quote
christopher_rath wrote:
I would like to use AutoHotKey to run some VBA in Outlook. From searching this forum it looks like the best way to do this is through use of the COM library. My initial problem, as I look to try and write some AHK code to do this is that I'm not sure how to determine that a message window has focus before I execute the VBA. Any suggestion?

Thanks,
Christopher
I cant imagine many peices of VBA that require focus in relation to Outlook but setting focus would depend entirely on what your setting focus on


@ Astrognaw look up ADO in the forum
_________________

We are troubled on every side‚ yet not distressed; we are perplexed‚
but not in despair; Persecuted‚ but not forsaken; cast down‚ but not destroyed;
Back to top
View user's profile Send private message
Sean



Joined: 12 Feb 2007
Posts: 2462
PostPosted: Mon Aug 10, 2009 11:33 pm    Post subject: Reply with quote
If you're determined to do through COM.ahk, you may consider to do the job entirely in it, i.e., may translate the VBA code into AHK.
Back to top
View user's profile Send private message
Question
Guest
PostPosted: Fri Aug 14, 2009 4:58 pm    Post subject: Reply with quote
Is there any way to retrieve the pattern color from a cell in excel?
Back to top
HeWhoWas



Joined: 03 Aug 2009
Posts: 86
Location: Australia (Sydney)
PostPosted: Sat Aug 15, 2009 6:24 am    Post subject: Reply with quote
I think Cells.Interior.ColorIndex will return the colour currently filling the cell.
_________________
There are 10 kinds of people in the world. Those who understand binary and those who don't.
Back to top
View user's profile Send private message AIM Address MSN Messenger
Display posts from previous:   
Reply to topic    AutoHotkey Community Forum Index -> Scripts & Functions All times are GMT
Goto page Previous  1, 2, 3 ... 5, 6, 7 ... 18, 19, 20  Next
Page 6 of 20

 
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