Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Basic Ahk_L COM Tutorial for Excel


  • Please log in to reply
227 replies to this topic
plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011

I have had no end of issues specifically translating colors for cell highlighting.


Why would you need to translate them? You just need to use the Interior.Color property to get the color code and set it later the same way:

; for the active selection
MsgBox %	ComObjActive("Excel.Application").Selection.Interior.Color


Excellent. But when I try to do this, it works, but the value changes in the message box. For example, when I use:

xlSheet.Range("H" . 12).Interior.Color := 255

It gives me my desired color, red. But then when I try to do the same thing for a different cell, the MsgBox comes up with a different number than 255, although my desired cell has been filled with my desired color. Is this normal?

sinkfaze
  • Moderators
  • 6367 posts
  • Last active:
  • Joined: 18 Mar 2008

Is this normal?


Yes. The Color property essentially broadens (and in many ways, supercedes) the older ColorIndex property, which only allowed for a certain amount of colors, such as those in the format like you're using.

You could use the ColorIndex property instead, which is not totally unreasonable since many people are sticking to their 2003 guns. But at some point I believe it will be phased out with the Color property as the sole source to get/set colors.

plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011
I have to ask a dumb question. I looked it up in MSDN and I am still having a problem. Where did I go wrong with this line of thinking?

ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN
window := Acc_ObjectFromWindow(hwnd, -16)
xlBook := window.parent
xlApp := window.application
xlSheet := window.activesheet

xlBook.Save() ;To quick save the excel file

As you can see, I'm having trouble saving the excel file. Any corrections?

sinkfaze
  • Moderators
  • 6367 posts
  • Last active:
  • Joined: 18 Mar 2008

Where did I go wrong with this line of thinking?


Look again at the hierarchy at the top of the Workbooks collection page. The Workbooks collection derives from a single object, the Application object, so you would need to establish a pointer to the application object from the pointer to the window object, then use the application pointer to save the workbook:

ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN
window := Acc_ObjectFromWindow(hwnd, -16)
[color=red]xlApp := window.application
xlApp.Workbooks([/color][color=darkred]<< your workbook name/number here >>[/color][color=red]).Save()[/color]


jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009

As you can see, I'm having trouble saving the excel file.

Actually, I cannot see - the code works fine for me on Excel 2003. You may want to check your Com Objects:
Msgbox, % ComObjType(xlBook, "Name")


  • Guests
  • Last active:
  • Joined: --

Look again at the hierarchy at the top of the Workbooks collection page. The Workbooks collection derives from a single object, the Application object, so you would need to establish a pointer to the application object from the pointer to the window object, then use the application pointer to save the workbook:

ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN
window := Acc_ObjectFromWindow(hwnd, -16)
[color=red]xlApp := window.application
xlApp.Workbooks([/color][color=darkred]<< your workbook name/number here >>[/color][color=red]).Save()[/color]


I tried this to no avail. I put the name with and without the extension 'xlsx'. Did I do something wrong?

Actually, I cannot see - the code works fine for me on Excel 2003. You may want to check your Com Objects:

Msgbox, % ComObjType(xlBook, "Name")



I got an empty MsgBox. This is a problem, right?

plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011
Sorry about that, I didn't realize I wasn't logged in.

jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009
Did you verify the window object? How about the HWND?

plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011

Did you verify the window object? How about the HWND?


I'm going to be honest, I don't really know how to do those. Maybe if I show you my code, we can get a better picture as of now.

^s::
Clipboard =
Send ^c 
Clipwait = 1
Run http://www.wordreference.com/es/en/translation.asp?spen=%Clipboard% 
Sleep 200
IfWinNotExist Microsoft Excel - Unknown4
{
Run "Censored Filepath"
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN
window := Acc_ObjectFromWindow(hwnd, -16)
xlBook := window.parent
xlApp := window.application
xlSheet := window.activesheet
}
Sleep 200
IfWinNotActive Microsoft Excel - Unknown4
    WinActivate Microsoft Excel - Unknown4
Loop
{
    if (xlSheet.Range("A" . A_Index).Value != "")
        continue
    else
        Send %Clipboard%{ENTER}
    Sleep 200
        xlApp.WorkBooks(Unknown4.xlsx).Save()
        break
    }
Return


jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009
MsgBox, %HWND%

MsgBox, % ComObjType(window)

MsgBox, % ComObjType(window, "Name")


plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011

MsgBox, %HWND%
MsgBox, % ComObjType(window)
MsgBox, % ComObjType(window, "Name")


I did this:

^s::
Clipboard =
Send ^c 
Clipwait = 1
Run http://www.wordreference.com/es/en/translation.asp?spen=%Clipboard% 
Sleep 200
IfWinNotExist Microsoft Excel - Unknown4
{
Run "Censored"
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN
window := Acc_ObjectFromWindow(hwnd, -16)
xlBook := window.parent
xlApp := window.application
xlSheet := window.activesheet
MsgBox, %HWND%
MsgBox, % ComObjType(window)
MsgBox, % ComObjType(window, "Name")
}
Sleep 200
IfWinNotActive Microsoft Excel - Unknown4
    WinActivate Microsoft Excel - Unknown4
Loop
{
    if (xlSheet.Range("A" . A_Index).Value != "")
        continue
    else
        Send %Clipboard%{ENTER}
    Sleep 200
        xlApp.WorkBooks(Unknown4.xlsx).Save()
        break
    }
Return

And I'm coming up with empty message boxes. I made sure to have the file open up first.

jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009
If the first MsgBox for the HWND is blank, that means you're never getting the HWND of the WorkBook control - meaning the rest of the code is irrelevant.

plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011

If the first MsgBox for the HWND is blank, that means you're never getting the HWND of the WorkBook control - meaning the rest of the code is irrelevant.


That's strange because the script is able to have the clipboard pasted to the appropriate cell.

Is there a way to fix this problem?

plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011

If the first MsgBox for the HWND is blank, that means you're never getting the HWND of the WorkBook control - meaning the rest of the code is irrelevant.


All is good with the world. Thank you to both of you guys for helping me out.

I put in a sleep command right before the ControlGet command for a split second and HWND + everything else passed through.

The XlBook.Save() also worked! Good times.

Just a thought:

Would it be okay if I post in this thread about these kind of things? I imagined that it would be helpful for other people who could learn off of my ignorance. If so, I had other questions related to this very tiny project which would involve VBA, COM, etc. Would it be okay to ask those as well in the near future in this thread? Or should it be completely different thread altogether?

Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
Yes that's what it's for. :wink: