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
hapyman
  • Members
  • 48 posts
  • Last active: Jan 24 2012 08:13 PM
  • Joined: 21 Oct 2009
No not usually and definitely didn't during testing.

Also the other methods you had worked such as

Xl.Range("A7").Value := variable

Which is why I can't figure out why ActiveSheet.PrintOut won't work.

I guess its not a deal breaker but I can't help but try to figure it out :D

Almost forgot I am running Office 2003 but I was using the Excel 2003 Object Reference. I must say though the 2003 Object reference is not as complete as the one for 2007. Regardless though the "ActiveSheet.PrintOut" from the Excel 2003 reference doesn't even work. I think I will just bypass COM for printing and just do a some simple print script such as !fp or ^p

Thanks for the help!

Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
Yes 2003 doesn't have the same methods and properties and has to be treated differently.
PrintOut
Xl.ActiveSheet.PrintOut
:?:

hapyman
  • Members
  • 48 posts
  • Last active: Jan 24 2012 08:13 PM
  • Joined: 21 Oct 2009
Here is something curious. When I use Workbook.Close(false) it looks as if it closes the worksheet out but for some reason behind the scenes it is still running. I tried to open the spreadsheet outside of AHK and Excel is saying the spreadsheet is still open. Sure enough checking Process Manager there are several instances of excel open. Any reason why this is happening?

Thanks.

EDIT: Nevermind I think I figured out why this was happening.

Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
Yes that line closes the Workbook not the application. Use Xl.quit to close the running application.
Scratch that .Quit still leaves ghost windows. Try:
SetTitleMatchMode, fast
DetectHiddenWindows, on

Loop
	IfWinExist, MicroSoft Excel
		WinKill, MicroSoft Excel
	else break
ExitApp
Edit:

EDIT: Nevermind I think I figured out why this was happening.

Care to shed light on why you think it's happening? It's not the first time I, among others, have wondered this. :?:

jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009
Release the pointer after quitting the application:
xl := ""


hapyman
  • Members
  • 48 posts
  • Last active: Jan 24 2012 08:13 PM
  • Joined: 21 Oct 2009
I thought it was because some code I was using to test the script but now I am not so sure because I have not been able to replicate the same scenario again where I have like 7 instances of excel.exe running in the background.

I will just use your provided code to make sure excel closes properly. No reason not to.

Thanks.

Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010

Release the pointer after quitting the application:

xl := ""

@jethrow
What does releasing the pointer do that .quit doesn't? I'm just curious.

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

Fully terminates Excel:

DetectHiddenWindows, On
xl := ComObjCreate("Excel.Application")
str := "ahk_id" xl.hwnd

MsgBox, % WinExist(str)

xl.quit
Sleep 1000
MsgBox, % WinExist(str)

xl := ""
Sleep 1000
MsgBox, % WinExist(str)


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

I was planning on getting back to working on Excel COM stuff, so I wanted to detail out what I wanted to do and just get back some ideas from you guys so that I know where to start.

Should I open a new thread for that, or stick with this one? I thought it would be okay to do it here because it might become a useful resource for other newbies as I progress into what I will be making.

Otherwise, I'll just ask for help in the general thread, but it will involve quite a bit of COM in Excel. Plus, this thread hasn't been active for awhile. Thanks guys.

sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008
Go right ahead, this thread could use a bump anyway.

plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011
Sweet, so this is the deal:

I have two separate Excel workbooks. I want to be able to check the value of the cell of one workbook and be able to find the same or similar value in the other workbook.

Once I have found the value in the other workbook, I want to be able to fill in a color for not only that specific cell, but for its neighboring columns; in this case, from A - G.

This would basically loop through the entire column in the first workbook until it hits a blank.

The only caveat is that the information in the cells of the first workbook is not so cut and dry. I would be finding numbers, usually a 4 or 5 digit number, from the first workbook in the second workbook. In the second workbook, the 4 digit numbers associated with the first workbook would precede with six zeros, and the 5 digit numbers would precede with five zeroes. So "4321" from the first workbook would be "0000004321" in the second workbook, and "54321" would be "0000054321".

And to make it a bit more tricky, there is more than one number in the cells of the first workbook, delimited by a semi-colon. Also, there are some cells that just say "keep", which can be skipped.

Now, I will attempt to start this on my own; I just wanted to plant this project here so I can see if people can give me some clues while I work on it. I haven't done AHK_L nor COM in a long time, so I would appreciate all the help I can get. Thanks, and if you need more clarification, please do not hesitate to ask.

plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011
Okay, so I have been playing around with COM code for Excel for practice. I just have a question about the sheets. I know you guys discussed it a bit early in the thread, but I am still confused.

The way I did it was tag one sheet with an object, move the sheet "manually" and then tag that one with another object. Obviously, this is not the best way to do it, but I did not know of a more precise method.

So, is there a way to call up a different sheet within the same Excel application, and then have that call also point to an object? Thanks.

Settitlematchmode, 2





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

Sleep 500


WinWait, Microsoft Excel - Book1, 
IfWinNotActive, Microsoft Excel - Book1, , WinActivate, Microsoft Excel - Book1, 
WinWaitActive, Microsoft Excel - Book1, 
Send, {CTRLDOWN}{PGDN}{CTRLUP} ;this moves to the next sheet


Sleep 500

xlSheet2 := window.activesheet ;object points to the new active sheet

Sleep 500

xlSheet.Range("C1").Value := 123
xlSheet.Range("C2").Value := 456
xlSheet.Range("C3").Formula := "=SUM(C1:C2)"

xlSheet.Range("C1:C3").Font.Bold := 1



xlSheet.Range("E1:K1").Value := " test successful"

Sleep 100

xlSheet.Range("C1:C3").Interior.ColorIndex := 19

Sleep 100

xlSheet2.Range("A1").Value := " this is working"
xlSheet2.Range("B1").Value := 123
xlSheet2.Range("B2").Value := 456
xlSheet2.Range("B3").Formula := "=SUM(C1:C2)"

xlSheet2.Range("C1:C3").Interior.ColorIndex := 19


sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008
The xlbook pointer has the workbook object already, so you can use a for-loop to access the sheets. Using the for-loop is a valuable skill to develop in using Excel. Here's a shorter version of your code:

ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN
window := Acc_ObjectFromWindow(hwnd, -16)
xlBook := window.parent, xlApp := window.application
xlApp.ScreenUpdating :=	False	; this will improve performance, must be done from the Application object
For sheet in xlBook.Worksheets	; will go through each sheet in the workbook
{
	sheet.Range("A1").Value :=	"this is working"
	For row, contents in [123,456,"=SUM(C1:C2)"]
		sheet.Range("C" row).Formula :=	contents
	sheet.Range("C1:C3").Interior.ColorIndex :=	19
}
xlApp.ScreenUpdating :=	True	; always make sure to turn screen updating back on once complete.


plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011
That is interesting, I need to go over "For" loops again. But this code allows me to go through each sheet and perform the same operation, right?

Is there a code where you can access the specific sheet directly to enter unique information? That way I can go back and forth between whichever sheet, whenever I want. Maybe the code or clue is in what you have just given me, but just so we can document the instructions directly.

Thanks so much :D

The xlbook pointer has the workbook object already, so you can use a for-loop to access the sheets. Using the for-loop is a valuable skill to develop in using Excel. Here's a shorter version of your code:



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

But this code allows me to go through each sheet and perform the same operation, right?


Correct.

Is there a code where you can access the specific sheet directly to enter unique information?


As long as you know the tab's name or which tab it is in the order, you can select a specific sheet that way:

; if the tab "Ooga Booga" is the 3rd tab in a workbook
xlSheet :=	xlBook.Worksheets("Ooga Booga")
; or
xlSheet :=	xlBook.Worksheets(3)