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
amnesiac
  • Members
  • 124 posts
  • Last active: May 01 2014 03:04 AM
  • Joined: 07 Nov 2010
@a4u, in that example the column number can be replace with var easily. It is dynamically.

Click to download Chinese resource for AutoHotkey.

Recommended: AutoHotkey_L My code is based on it or similar versions, e.g. AutoHotkey_H.
Together with AutoHotkey, we grow and march forward. No matter how the future will be, this period of days is still epic.


sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008

@a4u, in that example the column number can be replace with var easily. It is dynamically.


Yes, but I don't see what your point is.

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

Is there a way to have AHK recognize the cell that is currently selected already? And then, is it possible to have it Offset from there to the left? Would it have to be a negative number in the parameter for Offset? Thanks!

sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008
Open a spreadsheet, select a cell with a value and a value in the previous column and run this:

xl :=	ComObj("Excel.Application")
MsgBox %	xl.Selection.Value "`n" xl.Selection.Offset(0,-1).Value


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

Open a spreadsheet, select a cell with a value and a value in the previous column and run this:

xl :=	ComObj("Excel.Application")
MsgBox %	xl.Selection.Value "`n" xl.Selection.Offset(0,-1).Value


Hey sinkfaze, thanks for the quick reply.

I get an error when I try that:

Error: 0x80020006 - Unknown name

Specifically: Selection

ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN
window := Acc_ObjectFromWindow(hwnd, -16)
xlBook2 := window.parent
xlApp2 := window.application
xlSheet2 := window.activesheet 

MsgBox % xlSheet2.Selection.Offset(0,-8).Value


I have two spreadsheets open, is this what might be causing the error? I need to work between the two of them. I thought that ControlGet would pass the hwnd to active spreadsheet, and then I can do the same to the other one by clicking on its window.

sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008
You can't access the Selection property from the (Work)Sheets object because a selection is not part of a worksheet, it's a display effect. You must access it from either the Application object (preferred) or the Window object.

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

You can't access the Selection property from the (Work)Sheets object because a selection is not part of a worksheet, it's a display effect. You must access it from either the Application object (preferred) or the Window object.


Yep, indeed, that was the problem; man, do I feel silly. I used the application object and now I have one manual step less, thank you!

plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011
This is going to be so stupid, but how would you enter variables into a cell? This isn't working for me, escaping the percent signs didn't help.

xlSheet.Range("A" . A_Index).Value := " `%clipboard`% "


sumon
  • Moderators
  • 1317 posts
  • Last active: Dec 05 2016 10:14 PM
  • Joined: 18 May 2010

This is going to be so stupid, but how would you enter variables into a cell? This isn't working for me, escaping the percent signs didn't help.

xlSheet.Range("A" . A_Index).Value := " `%clipboard`% "


Do it like this instead:

xlSheet.Range("A" . A_Index).Value := Clipboard

Literal ("Traditional") variable method is used in for example commands, and although beginner friendly is not recommended for variables (use as little as possible). Function ("Expression") variable method is proper to use in functions, if's, etc.

Msg = Hello world
MsgBox %Msg% ; Traditional method

Msg := "Hello world"
MsgBox % Msg ; Forced "Expression mode"

It's a bit hard to explain, but I hope you will understand.

Read more at <!-- m -->http://www.autohotke...s/Variables.htm<!-- m -->

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

This is going to be so stupid, but how would you enter variables into a cell? This isn't working for me, escaping the percent signs didn't help.

xlSheet.Range("A" . A_Index).Value := " `%clipboard`% "


Do it like this instead:

xlSheet.Range("A" . A_Index).Value := Clipboard

Literal ("Traditional") variable method is used in for example commands, and although beginner friendly is not recommended for variables (use as little as possible). Function ("Expression") variable method is proper to use in functions, if's, etc.

Msg = Hello world
MsgBox %Msg% ; Traditional method

Msg := "Hello world"
MsgBox % Msg ; Forced "Expression mode"

It's a bit hard to explain, but I hope you will understand.

Read more at <!-- m -->http://www.autohotke...s/Variables.htm<!-- m -->


Thanks for the quick reply! I can't say the same for myself :(

Does this mean I should do the original variable like this?

clipboard := ; Empty the clipboard
Send, ^c

Instead of this, which I have right now(although it still works)?

clipboard = ; Empty the clipboard
Send, ^c


dmg
  • Members
  • 2395 posts
  • Last active: Nov 04 2015 06:46 AM
  • Joined: 19 Nov 2010
I think would be correct:
clipboard := [color=#FF0000]""[/color] ; Empty the clipboard

Send, ^c

"My dear Mr Gyrth, I am never more serious than when I am joking."
~Albert Campion

-----------------------------------------------------------------------------------------------
Website | Demo scripts | Blog | External contact

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

I think would be correct:

clipboard := [color=#FF0000]""[/color] ; Empty the clipboard
Send, ^c


Yes, that worked as well, thanks for the proper syntax.

I had another question. The code I have below works great for single sentences, but not so much for multiple ones.

#1::
{
clipboard := "" ; Empty the clipboard
Send, ^c
ClipWait, 2

Run C:\Documents and Settings\username\My Documents\filename.xlsx

Sleep 300

ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN
window := Acc_ObjectFromWindow(hwnd, -16)
xlBook := window.parent
xlApp := window.application
xlSheet := window.activesheet
xlApp.ScreenUpdating :=   False   ; this will improve performance, must be done from the Application object

Loop
{
    if (xlSheet.Range("A" . A_Index).Value != "")
        continue
    else
        xlSheet.Range("A" . A_Index).Value :=  clipboard
        break
}

xlApp.ScreenUpdating :=   True   ; always make sure to turn screen updating back on once complete.

return
}

But when I do multiple sentences with the same code, such as these from <!-- m -->http://www.wordrefer... ... p?spen=aun<!-- m -->

ni ~ trabajando 12 horas al día (not) even if we worked 12 hours a day;
~ así, creo que… even so, I think…;
y ~ así nos costó una fortuna and even then it cost us a fortune

It puts it all in the one cell. But if I do it the manual way, highlight it all and then paste into the empty cell, Excel puts the three separate sentences onto three separate cells; where I can then split the sentences into two cells between the bold and non-bold text.

Could this be because Autohotkey is demanding Excel to put everything into that empty single cell, and the manual way allows Excel to enter the information in a smarter manner? Thanks in advance.

  • Guests
  • Last active:
  • Joined: --
Use a parsing loop? (loop, parse, clipboard, `n, `r and use A_Index/Counter and A_LoopField in your Excel statement)

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

Use a parsing loop? (loop, parse, clipboard, `n, `r and use A_Index/Counter and A_LoopField in your Excel statement)


I took your advice, and came up with this:

#2::
{
clipboard := "" ; Empty the clipboard
Send, ^c
ClipWait, 2

Run C:\Documents and Settings\username\My Documents\filename.xlsx

Sleep 300

ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN
window := Acc_ObjectFromWindow(hwnd, -16)
xlBook := window.parent
xlApp := window.application
xlSheet := window.activesheet
xlApp.ScreenUpdating :=   False   ; this will improve performance, must be done from the Application object

Loop
{
    if (xlSheet.Range("A" . A_Index).Value != "")
        continue
    else
        Loop, parse, clipboard, `n, `r
        {
        xlSheet.Range("A" . A_Index).Value :=  A_LoopField
        }
        break
}

xlApp.ScreenUpdating :=   True   ; always make sure to turn screen updating back on once complete.

return
}

Two problems:

- it seems to ignore that there is already data within the A1 cell, and it plops the data into A1, A2, and A3, although this does not happen with my original script.

-the bold and non-bold text is not preserved.

Thanks in advance!

Leef_me
  • Moderators
  • 8510 posts
  • Last active: Sep 10 2015 05:50 AM
  • Joined: 08 Apr 2009

Open a spreadsheet, select a cell with a value and a value in the previous column and run this:

xl :=	ComObj("Excel.Application")
MsgBox %	xl.Selection.Value "`n" xl.Selection.Offset(0,-1).Value


Doesn't work with
Excel 2007
Win 7
A_ahkversion 1.1.07.03

Btw, listvars shows
xl[Object]: 0x022AE240 <= ComObject(0x0009, 0x20D354)