227 replies to this topic

[plastikglass]

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]

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]

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]

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]

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")

[plastikglass]

Sorry about that, I didn't realize I wasn't logged in.

[jethrow]

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

[plastikglass]

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]

MsgBox, %HWND%

MsgBox, % ComObjType(window)

MsgBox, % ComObjType(window, "Name")

[plastikglass]

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]

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]

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]

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]

Yes that's what it's for. :wink: