AutoHotkey Community

It is currently May 27th, 2012, 9:13 am

All times are UTC [ DST ]




Post new topic Reply to topic  [ 144 posts ]  Go to page Previous  1 ... 3, 4, 5, 6, 7, 8, 9, 10  Next
Author Message
 Post subject:
PostPosted: November 22nd, 2011, 8:23 pm 
Offline

Joined: October 11th, 2010, 6:15 pm
Posts: 1211
Location: Right behind you
Code:
FileSelectFile, Path
If ErrorLevel
   ExitApp
Xl := ComObjCreate("Excel.Application")
Workbook := Xl.Workbooks.Open(Path)
Xl.Visible := True

Workbook.PrintOut()
Untested but I believe this should work as well.
Query: do you have multiple workbooks open at the same time?

Check out the Workbook Object Members

_________________
COM Tutorial for Webpages
COM Tutorial for Excel


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: November 22nd, 2011, 8:36 pm 
Offline

Joined: October 21st, 2009, 6:55 pm
Posts: 48
No not usually and definitely didn't during testing.

Also the other methods you had worked such as

Code:
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!


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: November 22nd, 2011, 9:48 pm 
Offline

Joined: October 11th, 2010, 6:15 pm
Posts: 1211
Location: Right behind you
Yes 2003 doesn't have the same methods and properties and has to be treated differently.
PrintOut
Code:
Xl.ActiveSheet.PrintOut
:?:

_________________
COM Tutorial for Webpages
COM Tutorial for Excel


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: December 1st, 2011, 5:36 pm 
Offline

Joined: October 21st, 2009, 6:55 pm
Posts: 48
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.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: December 1st, 2011, 6:05 pm 
Offline

Joined: October 11th, 2010, 6:15 pm
Posts: 1211
Location: Right behind you
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:
Code:
SetTitleMatchMode, fast
DetectHiddenWindows, on

Loop
   IfWinExist, MicroSoft Excel
      WinKill, MicroSoft Excel
   else break
ExitApp
Edit:
Quote:
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. :?:

_________________
COM Tutorial for Webpages
COM Tutorial for Excel


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: December 2nd, 2011, 12:35 am 
Offline
User avatar

Joined: May 24th, 2009, 5:35 am
Posts: 2099
Location: Iowa, USA
Release the pointer after quitting the application:
Code:
xl := ""

_________________
Image
Recommended: AutoHotkey_L
Basic Webpage Controls


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: December 2nd, 2011, 12:59 am 
Offline

Joined: October 21st, 2009, 6:55 pm
Posts: 48
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.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: December 2nd, 2011, 3:26 pm 
Offline

Joined: October 11th, 2010, 6:15 pm
Posts: 1211
Location: Right behind you
jethrow wrote:
Release the pointer after quitting the application:
Code:
xl := ""
@jethrow
What does releasing the pointer do that .quit doesn't? I'm just curious.

_________________
COM Tutorial for Webpages
COM Tutorial for Excel


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: December 2nd, 2011, 3:32 pm 
Offline
User avatar

Joined: May 24th, 2009, 5:35 am
Posts: 2099
Location: Iowa, USA
Fully terminates Excel:
Code:
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)

_________________
Image
Recommended: AutoHotkey_L
Basic Webpage Controls


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: March 5th, 2012, 10:59 pm 
Offline

Joined: August 24th, 2011, 3:30 pm
Posts: 84
Location: United States
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.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: March 6th, 2012, 12:35 am 
Offline
User avatar

Joined: March 19th, 2008, 12:43 am
Posts: 5482
Location: the tunnel(?=light)
Go right ahead, this thread could use a bump anyway.

_________________
Image
Try Quick Search for Autohotkey or see the tutorial for newbies.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: March 6th, 2012, 3:29 pm 
Offline

Joined: August 24th, 2011, 3:30 pm
Posts: 84
Location: United States
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.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: March 9th, 2012, 5:47 pm 
Offline

Joined: August 24th, 2011, 3:30 pm
Posts: 84
Location: United States
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.

Code:
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


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: March 9th, 2012, 6:33 pm 
Offline
User avatar

Joined: March 19th, 2008, 12:43 am
Posts: 5482
Location: the tunnel(?=light)
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:

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.

_________________
Image
Try Quick Search for Autohotkey or see the tutorial for newbies.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: March 9th, 2012, 9:31 pm 
Offline

Joined: August 24th, 2011, 3:30 pm
Posts: 84
Location: United States
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

sinkfaze wrote:
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:


Report this post
Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 144 posts ]  Go to page Previous  1 ... 3, 4, 5, 6, 7, 8, 9, 10  Next

All times are UTC [ DST ]


Who is online

Users browsing this forum: garry and 11 guests


You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Powered by phpBB® Forum Software © phpBB Group