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
hausinformasi
  • Members
  • 13 posts
  • Last active: Feb 28 2014 11:49 PM
  • Joined: 19 Jan 2014

Hello, I read the post on excel and I'm confused, here is what I wanna do:

 

  1. ahk code to go to a spesific sheet in a spesific excel file
  • ex: go to sheet1 from fileA.xls, then go to sheet2 from fileA.xls, then go to sheet1 from fileB.xls
  1. How to go to spesific cell
  • ex: go from cell A1 to Z10
  1. ahk code to hold a key
  • ex: I wanna copy a range then I will need to hold the shift command right? so I wanna hold shift key in A1 then push the right and down button till z10, then copy it (ctrl-c)

 

I tried to read the tutorial but seems like my newbie brain can't process it  :/

 

Help me anyone, thx alot



arizonabuckeye
  • Members
  • 49 posts
  • Last active: Jun 03 2015 09:19 PM
  • Joined: 13 Mar 2014

Time for a bump and a background color question...

 

I used 

^+r::MsgBox % ComObjActive("Excel.Application").Selection.Interior.Color

To get the color value which when on a white cell gives 16777215.000000 in the message box.

 

Now I am trying to write an if statement so I can switch between no fill and yellow fill however 

if (IntColor = 16777215.000000)

Doesn't catch and the script runs to 

if (IntColor <> 16777215.000000)

Am I using the Interior.Color output incorrectly?

 

Sorry for the noob question



Alpha Bravo
  • Members
  • 1687 posts
  • Last active: Nov 07 2015 03:06 PM
  • Joined: 01 Sep 2011

works fine

!1::
intColor := ComObjActive("Excel.Application").Selection.Interior.Color
if (intColor = 16777215.00000)
	MsgBox white
else
	MsgBox not white
return


arizonabuckeye
  • Members
  • 49 posts
  • Last active: Jun 03 2015 09:19 PM
  • Joined: 13 Mar 2014

Thanks! The following is functional

^+f::
;Toggles a cell between yellow fill and no fill
SetKeyDelay, 75,75
intColor := ComObjActive("Excel.Application").Selection.Interior.Color
if (intColor = 16777215.00000 )
{

SendInput {Alt Down}hh{Alt Up}
Send, {Down 6}
Sendinput, {Right 3}
SendInput,{Enter}
return
}
if (IntColor <> 16777215.00000)
{
Send {Alt}hhn
FillVar:=0
Return
}
IntColor :=
Return

Is there a way to pass the color value back into the cell rather than imitating the key strokes?

 

Maybe something like ComObjValue("intColor").Selection.Interior.Color = 65535.00000 - That doesn't work is there a way to tweak that?



musiu
  • Members
  • 3 posts
  • Last active: Mar 06 2015 11:14 AM
  • Joined: 19 Feb 2015

hey,

i am trying to use COM for excel. when i try to run script i get an error: see picture below.

 

many Thank You in advance and best regards,Pawel Musial

ahk.jpg

 

 

I revoke my question and I apologize - i wasn't even aware that I had old version of AHK. 

Have a good day.

Best regards,Pawel Musial



selong1234
  • Members
  • 4 posts
  • Last active: Dec 06 2015 04:25 AM
  • Joined: 11 Mar 2015

Thanks! The following is functional

^+f::
;Toggles a cell between yellow fill and no fill
SetKeyDelay, 75,75
intColor := ComObjActive("Excel.Application").Selection.Interior.Color
if (intColor = 16777215.00000 )
{

SendInput {Alt Down}hh{Alt Up}
Send, {Down 6}
Sendinput, {Right 3}
SendInput,{Enter}
return
}
if (IntColor <> 16777215.00000)
{
Send {Alt}hhn
FillVar:=0
Return
}
IntColor :=
Return

Is there a way to pass the color value back into the cell rather than imitating the key strokes?

 

Maybe something like ComObjValue("intColor").Selection.Interior.Color = 65535.00000 - That doesn't work is there a way to tweak that?

you can try it like this

xlApp := ComObjActive("Excel.Application")

intColor:=xlApp.Selection.Interior.Color

;MsgBox %intColor%
xlApp.Selection.Interior.Color:= "255,255,0"


cKenny
  • Members
  • 3 posts
  • Last active: Jun 25 2015 09:39 AM
  • Joined: 23 Jun 2015

Hi,

 

Can anyone help me convert below excel code to ahk code?

 

I created an GUI using ahk, which will let user enter some searching criteria, then make use of excel QueryTables object to query data from oracle and save the output into activesheet.

 

however, I do not know how to convert these VBA code into ahk code... Please help, thanks in advance!

strODBC = "ODBC;DSN=MYORDB;UID=useid;PWD=password"
mySQL    = "SELECT * FROM TABLE1"

With ActiveSheet.QueryTables.Add  (Connection:=strODBC, Destination:=ThisWorkBook.ActiveSheet.Range("A1"), Sql:=mySQL)
    .FieldNames = True
    .RefreshStyle = xlOverwriteCells
    .BackgroundQuery = False
    .Refresh
End With


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

Check out the QueryTables.Add Method. This is untested:

ActiveSheet := xlApplication.ActiveSheet
xlOverwriteCells := 0

strODBC := "ODBC;DSN=MYORDB;UID=useid;PWD=password"
mySQL    := "SELECT * FROM TABLE1"

QueryTable := ActiveSheet.QueryTables.Add(strODBC, ActiveSheet.Range("A1"), mySQL)
QueryTable.FieldNames := True
QueryTable.RefreshStyle := xlOverwriteCells
QueryTable.BackgroundQuery := False
QueryTable.Refresh


tbolto00
  • Members
  • 6 posts
  • Last active: Sep 28 2015 09:14 PM
  • Joined: 11 Sep 2015

I worked on this library about a year ago.  I always thought I'd have time to get back to it and better organize it plus work out some of the bugs but just haven't had the time.  I thought I'd post it here in case someone finds it useful. 

 

I'm not a programmmer so some of these are going to seem very simplistic but I've found them very helpful when automating Excel.  XL Library

 

 

The library looks super useful.  Please explain what PXL is?



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

Based on the XL_Handle function, PXL could be an Application, Workbook, or Worksheet Object. joetazz is typically using it to call the application property, which gets the Application object. I would criticize joetazz for his naming convention for these variables ... but since they are only variables, the name doesn't technically matter. On a side note, based on other coding examples, the 'p' typically stands for pointer (even though in this library joetazz is not using raw pointers). The 'xl' stands for Excel.



Joe Glines
  • Members
  • 118 posts
  • Last active: Jan 24 2016 03:08 PM
  • Joined: 23 Dec 2009

@Jethrow- Criticize away!  The beauty of knowing that I don't know what I'm doing allows me to not take things personally!  :)  LOL


Automating the mundane 1 script at a time...
https://www.linkedin.com/in/joeglines
The-Automator

tbolto00
  • Members
  • 6 posts
  • Last active: Sep 28 2015 09:14 PM
  • Joined: 11 Sep 2015

Thanks, Guys!  I figured that out shortly after posting, but I couldn't remove the post due to moderator settings.  Next question...

 

When I create a pivot table, it appropriately opens on a new sheet.  Once the new sheet is active, I can no longer use Xl.Range or other commands.  What do I need to do differently in order to continue using COM commands in the new/other sheet(s)?

 

Example script

 

Xl := ComObjCreate("Excel.Application") ; create Excel Application object
Xl.Workbooks.Open("G:\Accounting\Travis Bolton\Operating Supplies\" A_MM " DISTRIBUTION SUPPLIES " A_MM "" A_DD "" A_YYYY ".xlsx")
Xl.Visible := 1 ; make Excel Application visible
Xl.Range("A1").Select



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

I figured that out shortly after posting ...


Way cool. For your next question, select the cell using the Worksheet object, rather than the Application object.

tbolto00
  • Members
  • 6 posts
  • Last active: Sep 28 2015 09:14 PM
  • Joined: 11 Sep 2015

Okay, so I changed the next to last line to activate the worksheet object, and it works.  Is this the proper way, or would you recommend something else?

 

Xl := ComObjCreate("Excel.Application") ; create Excel Application object
Xl.Workbooks.Open("G:\Accounting\Travis Bolton\Operating Supplies\" A_MM " DISTRIBUTION SUPPLIES " A_MM "" A_DD "" A_YYYY ".xlsx")
Xl.Visible := 1 ; make Excel Application visible
Xl.Sheets("Sheet1").Select  ; this works to activate the script
Xl.Range("B6").Select
 



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

That works, but do you want to activate the sheet? And why go back up to the application object?

xlSheet = Xl.Sheets("Sheet1")
xlSheet.Range("B6").Select

;// or simply
Xl.Sheets("Sheet1").Range("B6").Select