Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Open Excel specific sheet


  • Please log in to reply
6 replies to this topic
darioc112
  • Members
  • 3 posts
  • Last active: May 17 2012 07:29 AM
  • Joined: 17 May 2012
Hello,

I've started using AHK, and it's really awesome!

In a script I need to open a specific sheet of an Excel file with a button.
Gui, Add, Button, grun1 x11 y6 w180 h80, TEST
return
run1:
IfWinExist TEST.xlsx
{
WinClose [color=#0040FF];the file must be reopen just in case it was update.[/color]
}
else
{
Run path\TEST.xlsx
WinMaximize, TEST.xlsx
}
return

Now I don't find a solution to open directly the 2nd or 3th sheet of this Excel file... and how to make an action to close this file?
I've tried with WinClose but no reaction...

I'm using Win7, but script will be run in XP, both with Excel Viewer 2007.

Thx for your help!!!
Dario

engunneer
  • Moderators
  • 9162 posts
  • Last active: Sep 12 2014 10:36 PM
  • Joined: 30 Aug 2005
you can send ^{PgUp} and ^{PgDn} to the excel window to change the active sheet. Sed ^{PgUp} as many times as you have sheets, then ^{PgDn} to get the the sheet you want.

as far as WinClose, are you sure your window is called TEST.xlsx? That's probably not the right window title. Look at SetTitleMatchMode and also the window name show in Window Spy to see what it should look like.

darioc112
  • Members
  • 3 posts
  • Last active: May 17 2012 07:29 AM
  • Joined: 17 May 2012
Hello engunneer,

nice idea, I just thinked about it but the only problem is if the sheet's order change the script is to change...
I really think i've no other solution!

Can I replace in the script the name of the Excel file with a line of a .txt file, so the final user must just create the .txt and specify into the path of the file?

jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009
Here is an example using AutoHotkey_L:
xlFile := A_ScriptDir "\MyWorkBook.xls"



try xl := ComObjActive("Excel.Application")

catch

	xl := ComObjCreate("Excel.Application")

Book := xl.WorkBooks.Open(xlFile)

Book.Sheets("Sheet2").Select

xl.Visible := True


darioc112
  • Members
  • 3 posts
  • Last active: May 17 2012 07:29 AM
  • Joined: 17 May 2012
wow...
I just downloaded AutoHotkey_L to try!!!

Thank you.

Xx7
  • Members
  • 674 posts
  • Last active: Mar 24 2015 10:48 PM
  • Joined: 19 Apr 2011

Here is an example using AutoHotkey_L:

xlFile := A_ScriptDir "\MyWorkBook.xls"

try xl := ComObjActive("Excel.Application")
catch
	xl := ComObjCreate("Excel.Application")
Book := xl.WorkBooks.Open(xlFile)
Book.Sheets("Sheet2").Select
xl.Visible := True

Jethrow, is there any difference between the "select" and "activate" method? I've used the activate method below.
name_1 := "C:\Book1.xlsx"

:*:555::
XL := ComObjCreate("Excel.Application")
Book := XL.WorkBooks.Open(name_1)
Book.Worksheets("Sheet2").Activate
XL.Visible := True
return


Also, how would I go to a specific range on a sheet?
For instance this works:
Book.Activesheet.Range("A10:B14").Select
But, this doesn't:
Book.Sheets("Sheet2").Range("A10:B14").Select


sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008
Because Select isn't a function of the worksheet itself, it's a visual function related to the window. ActiveSheet indicates that the worksheet is actively being viewed in the window and thus Select can be used, whereas the other method will only work if the worksheet happens to be actively viewed in the window.