 |
AutoHotkey Community
Let's help each other out
|
| View previous topic :: View next topic |
| Should this be continued? |
| Yes |
|
93% |
[ 14 ] |
| No |
|
6% |
[ 1 ] |
|
| Total Votes : 15 |
|
| Author |
Message |
Tyrsius
Joined: 09 Jul 2009
Posts: 140
|
 Posted: Fri Apr 02, 2010 3:09 am Post subject: |
 |
|
Ok, so I did some more testing which resulted in new and even more confusing errors. I will try to be as complete as I can. I tried two different methods of calling the save function. The first was the method I have already posted, this is the complete code I was using during testing (hotkey was random, for testing):
| Code: |
^r::
COM_Init()
oExcel := COM_GetActiveObject("Excel.Application")
COM_Invoke(oExcel, "Save")
COM_Release(oExcel)
COM_Term()
return
|
This code was working earlier, though I don't seem to be able to recreate those conditions. Now every time I run it I get this error (Copied directly from the msgbox):
| Code: |
---------------------------
COM Error Notification
---------------------------
Function Name: "Save"
ERROR: (0x800A03EC)
PROG: Microsoft Office Excel
DESC: Unable to get the Save property of the Application class
HELP: C:\Program Files\Microsoft Office\Office12\1033\XLMAIN11.CHM,0
Will Continue?
---------------------------
Yes No
---------------------------
|
After fighting with that for a while, I took the code you posted to get the pwb, and tried using that.
| Code: |
^t::
COM_Init()
oExcel := GetExcel()
oWorkbook := COM_Invoke(oExcel, "ActiveWorkbook")
COM_Invoke(oExcel, "Save")
COM_Release(oExcel)
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)
}
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
}
|
The first time I run this, it saves the spreadsheet as well creates a new file, named RESUME.XLW, in my documents that appears to be a copy of the current spreadsheet. If this file is not deleted before running the code again, I get the error I posted last time. I don't really understand why.
You said to compare the previous and the current call. I noticed that the line I first posted (when responding to tank) uses pxlb, since I copied it from a block of code on page 1, where the pointer was created from this:
| Code: |
pweb := COM_AtlAxCreateControl(WinExist(), A_Temp . "\test.xls")
pxlb := COM_Invoke(pweb, "Document")
|
I take this to mean that the save method I am trying to call will only work properly when using the COM_AtlAxCreateControl instance, not either of the methods I was testing with. If thats the case, is there a way to call the save method using a pointer collected from either of the methods I used above?
I know this post is long, but I wanted give you what I had done. I know I am asking what probably seem like basic questions from someone who just wants a quick solution, but I really am trying to learn this, and I am grateful to all the help I've gotten on these forums (especially from the work you and tank have done). |
|
| Back to top |
|
 |
sinkfaze
Joined: 18 Mar 2008
Posts: 5044
Location: the tunnel(?=light)
|
| Posted: Fri Apr 02, 2010 3:50 am Post subject: |
|
|
Why aren't you using your created pointer?
| Code: | ^t::
COM_Init()
oExcel := GetExcel()
oWorkbook := COM_Invoke(oExcel, "ActiveWorkbook")
COM_Invoke(oWorkbook, "Save")
COM_Release(oWorkbook), COM_Release(oExcel)
COM_Term()
return
|
_________________
Try Quick Search for Autohotkey or see the tutorial for newbies. |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2462
|
| Posted: Fri Apr 02, 2010 6:42 am Post subject: |
|
|
| I'm always puzzled how/why users are missing the obvious resources to learn from. As you seem to use Office 2007, the definite place you have to visit is 2007 Microsoft Office System. |
|
| Back to top |
|
|
tank
Joined: 21 Dec 2007
Posts: 3700
Location: Louisville KY USA
|
| Posted: Fri Apr 02, 2010 2:40 pm Post subject: |
|
|
in fairness Sean you must admit it is only recently that the MSDN has been so much more friendly. I remember a time (not but a few years ago) that much of it was like jibberish to all but the more skilled developer.
the very latest version of the msdn tho is very user friednly
_________________
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 |
|
|
Tyrsius
Joined: 09 Jul 2009
Posts: 140
|
| Posted: Fri Apr 02, 2010 7:39 pm Post subject: |
|
|
| sinkfaze wrote: | Why aren't you using your created pointer?
|
I thought I had tried that earlier with the same result, but going back it seems to be working now.Thats the pointer that the example I was borrowing from uses, too, I don't know how I skipped it. I certainly feel foolish for spending so much time with the answer right in front of me. I guess that makes sense though, the workbook being the one with the save method and not the application. Its the workbook thats being saved.
@Sean, thank you for the link, I will definitely be spending time there this week. |
|
| Back to top |
|
|
sinkfaze
Joined: 18 Mar 2008
Posts: 5044
Location: the tunnel(?=light)
|
| Posted: Fri Apr 02, 2010 8:06 pm Post subject: |
|
|
| Tyrsius wrote: | | I certainly feel foolish for spending so much time with the answer right in front of me. |
Welcome to most of our lives when we started using COM. 
By the way, there's also a CHM reference for Excel 2003 that you could download and read in addition to the website Sean referenced above. The core of what is required to access Excel hasn't changed much between the two versions so it won't hurt.
_________________
Try Quick Search for Autohotkey or see the tutorial for newbies. |
|
| Back to top |
|
|
Tyrsius
Joined: 09 Jul 2009
Posts: 140
|
| Posted: Wed Apr 07, 2010 2:03 am Post subject: |
|
|
In the Excel.ahk code posted on the front page, the author has Excel_SetText accept Column references as letters. I was trying to use it in a loop, and instead of trying to get the next letter, I found it easier to modify the function to just accepts numbers for the column reference (in addition to letters).
| Code: |
; **************************************************************************
; 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) ColumnID = The column letter/number 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(ColumnID, 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 !RegExMatch(ColumnID, "\d+") ;if not a number, get the Index from the letter - Tyrsius
ColumnID := Excel_GetColumnIndex(ColumnID)
if !oCells := COM_Invoke(oExcel,"Cells",RowNumber,ColumnID) {
MsgBox Could not get cell range.
Return
}
COM_Invoke(oCells,"Formula=",Text) ;"
COM_Release(oCells)
COM_Release(oExcel)
}
|
|
|
| Back to top |
|
|
Tyrsius
Joined: 09 Jul 2009
Posts: 140
|
| Posted: Thu Apr 15, 2010 4:54 pm Post subject: |
|
|
Sean, thank you so much for that link. I have been using it quite a lot, and I've learned how to translate from at least a dozen methods into COM for ahk.
That is one of the single largest time savers ever. 
| Code: |
COM_Invoke(oWorkbook, "ActiveSheet.PivotTables['Unaccepted Issues'].PivotCache.Refresh")
|
For the curious, this was the hardest to translate, as the MSDN lists the method as "RefreshTable" which does not work. I took this command from Excel's macro recorder, which did work. |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2462
|
| Posted: Fri Apr 16, 2010 4:46 am Post subject: |
|
|
| Tyrsius wrote: | | For the curious, this was the hardest to translate, as the MSDN lists the method as "RefreshTable" which does not work. I took this command from Excel's macro recorder, which did work. |
RefreshTable is a function of the interface PivotTable, not that of PivotCache which you're using.
BTW, AutoHotkey_COM has a built-in function to show the type of the object:
| Code: | obj := ComObjActive("Excel.Application").ActiveWorkbook.ActiveSheet.PivotTables("Unaccepted Issues").PivotCache
MsgBox % ComObjType(obj, "name")
|
|
|
| Back to top |
|
|
zhuluobin
Joined: 11 May 2009
Posts: 28
|
| Posted: Mon Apr 19, 2010 2:25 pm Post subject: |
|
|
| so this post means i don't need to learn MS VBA for excel? |
|
| Back to top |
|
|
tank
Joined: 21 Dec 2007
Posts: 3700
Location: Louisville KY USA
|
| Posted: Mon Apr 19, 2010 2:29 pm Post subject: |
|
|
if anything you need more to learn it since it will carry over and Sean or any one else for that matter isnt likely to document an objects methods properties and child objects
_________________
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 |
|
|
sinkfaze
Joined: 18 Mar 2008
Posts: 5044
Location: the tunnel(?=light)
|
| Posted: Thu Apr 29, 2010 8:42 pm Post subject: |
|
|
Here's a function for the Excel collection, it will save and close an Excel document. You can optionally specify the title of the document, if no title is specified the "active" instance will be saved/closed.
AHK:
| Code: | Excel_Close(title="") {
f := "is)Microsoft Excel - ", t := "is)\.xlsx?.*$"
if !xl := COM_GetActiveObject("Excel.Application") {
MsgBox, 48, Excel_Close, Could not obtain a reference to Excel.
return False
}
title := !title ? COM_Invoke(xl,"ActiveWorkbook.Name")
: RegExReplace(RegExReplace(title,t),f)
Loop % COM_Invoke(xl,"Workbooks.Count")
if InStr(COM_Invoke(xl,"Workbooks[" A_Index "].Name"),title) {
COM_Invoke(xl,"Workbooks[" A_Index "].Save")
COM_Invoke(xl,"Workbooks[" A_Index "].Close")
break
}
COM_Release(xl)
return True
} |
AHK_L:
| Code: | Excel_Close(title="") {
f := "is)Microsoft Excel - ", t := "is)\.xlsx?.*$"
if !xl := COM_GetActiveObject("Excel.Application") {
MsgBox, 48, Excel_Close, Could not obtain a reference to Excel.
return False
}
title := !title ? xl.ActiveWorkbook.Name
: RegExReplace(RegExReplace(title,t),f)
Loop % xl.Workbooks.Count
if InStr(xl.Workbooks[A_Index].Name,title) {
xl.Workbooks[A_Index].Save, xl.Workbooks[A_Index].Close
break
}
xl := ""
return True
} |
_________________
Try Quick Search for Autohotkey or see the tutorial for newbies. |
|
| Back to top |
|
|
zhuluobin
Joined: 11 May 2009
Posts: 28
|
| Posted: Wed May 05, 2010 10:06 am Post subject: |
|
|
| i think it should be more functions for excel as operations on chart. after all i can use formula of excel to get/calculate data. |
|
| Back to top |
|
|
sinkfaze
Joined: 18 Mar 2008
Posts: 5044
Location: the tunnel(?=light)
|
| Posted: Wed May 05, 2010 2:11 pm Post subject: |
|
|
| zhuluobin wrote: | | i think it should be more functions for excel as operations on chart. after all i can use formula of excel to get/calculate data. |
If you have such functions you are more than welcome to contribute them. The people who have been and are contributing to this thread are doing so because the functions here are the ones they find the most use for. I rarely have a need to create charts so writing functions for something I would barely use doesn't make much sense.
_________________
Try Quick Search for Autohotkey or see the tutorial for newbies. |
|
| Back to top |
|
|
jethrow
Joined: 24 May 2009
Posts: 1907
Location: Iowa, USA
|
| Posted: Wed May 05, 2010 2:21 pm Post subject: |
|
|
Dare I say ( especially with AHKL & COM_L ) that it's almost as easy to learn some basic VBScript/COM & use the Excel Object Model as it is to learn how to use a bunch of user created functions?
_________________
- in case I forgot to smile
Basic Webpage Controls
COM Object Reference |
|
| Back to top |
|
|
|
|
You can post new topics in this forum
You can reply to topics in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Register
Profile
Log in to check your private messages
Log in
