Page 1 of 1
Open Excel or when already active go to active Excel window
Posted: 23 Jan 2019, 04:53
by AviationGuy
I'm trying to open a specific Excel file. When it's already open I need to get to the Excel file.
It works when Excel is closed (first part of if-statement), Excel opens the right file, is visible and the messagebox with the filename apears.
But when the specific Excel file is already open and I activate my hotkey it still opens it again so I have 2 of the same files opened.
It just doesn't seem to go to the else part of the if-statement when the file is already opened.
The code I have rn is below.
!!This isn't my own code, the good sir Joe Glines wrote it but it doesn't seem to work for me!!
Code: Select all
^!q::
xlWB := "xl"
if(!WinExist(xlWB)) {
XL := ComObjCreate("Excel.Application")
Path = ;fill in path of specific Excel file here
XL.Workbooks.Open(Path)
XL.Visible := 1
MsgBox %Path% `n is now loaded
} else {
XL := ComObjActive("Excel.Application")
WinActivate, %xlWB%
MsgBox % "Excel already active"
}
Any help is appriciated, thanks!
Re: Open Excel or when active go to specified Excel window
Posted: 23 Jan 2019, 09:10
by Datapoint
If WinExist isn't finding the window then check TitleMatchMode. It is probably set to 1. Try 2 instead.
https://www.autohotkey.com/docs/commands/SetTitleMatchMode.htm
Re: Open Excel or when active go to specified Excel window
Posted: 23 Jan 2019, 09:46
by AviationGuy
Thanks for the reply!
It doesn't work for me tho, think I'm doing something wrong here.
I tried pasting the 'SetTitleMatchMode 2' command everywhere around, and in the script.
This is what I have right now, but it still opens Excel while the specific file is already opened.
Code: Select all
Path = C:\...\specificExcelFile.xlsx
OpenExcel(Path)
OpenExcel(Path) {
xlWB := "XL"
if(!WinExist(xlWB)) {
XL := ComObjCreate("Excel.Application")
;Path = Path
XL.Workbooks.Open(Path)
XL.Visible := 1
MsgBox %Path% `n is now loaded
SetTitleMatchMode, 2
} else {
XL := ComObjActive("Excel.Application")
WinActivate, %xlWB%
MsgBox % "Excel already active"
}
}
But, also placing 'SetTitleMatchMode 2' before the if-statement, or even setting it at 1 doesn't seem to do the trick.
EDIT: Putting 'SetTitleMatchMode, 2' inside the if-statement obviously doesn't work but it doesn't anyways :p
Re: Open Excel or when active go to specified Excel window
Posted: 23 Jan 2019, 14:53
by FanaticGuru
There are a lot of different ways to go about this but I like COM so I took a more COM approach.
Code: Select all
Path := A_Desktop "\Test\specificExcelFile.xlsx"
xlWB := OpenExcel(Path)
xlWB.ActiveSheet.Range("B2").Value := "Test"
OpenExcel(Path)
{
SplitPath, Path, FileName
try
{
xlApp := ComObjActive("Excel.Application")
for xlWB in xlApp.Workbooks
if (xlWB.Name = FileName)
{
Found := true
break
}
}
catch
xlApp := ComObjCreate("Excel.Application")
if !Found
xlWB := xlApp.Workbooks.Open(Path)
xlWB.Activate
xlApp.Visible := true
return xlWB
}
FG
Re: Open Excel or when active go to specified Excel window
Posted: 23 Jan 2019, 15:04
by FanaticGuru
Might as well have an option to choose the sheet also.
Code: Select all
Path := A_Desktop "\Test\specificExcelFile.xlsx"
xlWB := OpenExcel(Path, 2)
xlWB.ActiveSheet.Range("B2").Value := "Test"
OpenExcel(Path, Sheet := "ActiveSheet")
{
SplitPath, Path, FileName
try
{
xlApp := ComObjActive("Excel.Application")
for xlWB in xlApp.Workbooks
if (xlWB.Name = FileName)
{
Found := true
break
}
}
catch
xlApp := ComObjCreate("Excel.Application")
if !Found
xlWB := xlApp.Workbooks.Open(Path)
xlWB.Activate
if (Sheet != "ActiveSheet")
try
xlWB.Sheets(Sheet).Activate
xlApp.Visible := true
return xlWB
}
If no sheet specified then ActiveSheet, otherwise a name or index number.
FG
Re: Open Excel or when active go to specified Excel window
Posted: 24 Jan 2019, 04:11
by AviationGuy
Thanks for the reply FG!
I will definitely try your code but I'm still wondering why mine doesn't work (especially because it does work in the YT video of Joe Glines).
Why isn't the code seeing the Excel file? Especially, after WinActivate and SetTitleMatchMode 2, you should expect (or at least I would) that it sees that the Excel file is already opened and thus give the MsgBox with 'Excel already active'.
Re: Open Excel or when active go to specified Excel window
Posted: 24 Jan 2019, 06:48
by gregster
I have only glanced over your code, but I noticed that you mentioned a file named "C:\...\specificExcelFile.
xlsx"
in this post.
But the partial
wintitle (use
SetTitlematchmode 2) you are using there is actually
XL (capital letters). Since
Wintitle is case sensitive by default, this can't work...
Re: Open Excel or when active go to specified Excel window
Posted: 25 Jan 2019, 03:17
by AviationGuy
Still doesn't work unfortunately..
Changed the uppercase to lowercase like this
I was wondering what the paramaters of WinActivate needed to be. Right now the first parameter in the code is WinTitle which is 'xl' in my case.
Does this means that it will activate all the .xl windows?
And, is it also possible to change this to '.xlsx'? -> This also doesn't work btw.
I think the WinTitle parameter is where it goes wrong. The script doesn't seem to recognize that there is already an Excel file ('.xl') opened.
The AutoHotkey docs also aren't really helping..
WinActive:
https://autohotkey.com/docs/commands/WinActivate.htm
WinTitle:
https://autohotkey.com/docs/misc/WinTitle.htm
WinExist:
https://autohotkey.com/docs/commands/WinExist.htm
My code atm:
Re: Open Excel or when already active go to active Excel window
Posted: 25 Jan 2019, 04:21
by AviationGuy
OK, I got it working now!
Code: Select all
^!w::
ExcelPath := "C:\...\ahkFunctionLibrary.xlsx"
OpenExcel(ExcelPath)
;---------------------- Open Excel file ---------------
OpenExcel(ExcelPath) {
SetTitleMatchMode 2
if(!WinExist("Excel")) {
XL := ComObjCreate("Excel.Application")
XL.Workbooks.Open(ExcelPath)
XL.Visible := 1
MsgBox Loaded: `n %ExcelPath%
} else {
XL := ComObjActive("Excel.Application")
WinActivate, Excel
MsgBox % "Excel already active"
}
}
Return
However, as I mentioned before, it still doesn't work when I use
xlWB := "xl"
But, this will work until Excel releases an update which removes the '- Excel' part after the title in the header of the window
Re: Open Excel or when already active go to active Excel window
Posted: 05 Nov 2019, 08:17
by DRocks
I needed to add this condition to make the example OpenExcel() function provided by FanaticGuru:
precisely:
Code: Select all
try
{
xlApp := ComObjActive("Excel.Application")
if (xlApp) {
for xlWB in xlApp.Workbooks
if (xlWB.Name = FileName)
{
Found := true
break
}
}
}
Without this condition I get un-handled exception when it tries to acess a void xlApp
Thanks
Re: Open Excel or when already active go to active Excel window
Posted: 07 Nov 2019, 15:14
by FanaticGuru
DRocks wrote: ↑05 Nov 2019, 08:17
I needed to add this condition to make the example OpenExcel() function provided by FanaticGuru:
precisely:
Code: Select all
try
{
xlApp := ComObjActive("Excel.Application")
if (xlApp) {
for xlWB in xlApp.Workbooks
if (xlWB.Name = FileName)
{
Found := true
break
}
}
}
Without this condition I get un-handled exception when it tries to acess a void xlApp
Thanks
Are you using the function as posted?
The whole point is for the
xlApp := ComObjActive("Excel.Application") to throw an error which then try/catch causes
xlApp := ComObjCreate("Excel.Application") to be used instead.
Here is an updated version that I use that has a try/catch to handle when you try to open a file that does not exist.
Code: Select all
Path := A_Desktop "\Test\specificExcelFile.xlsx"
xlWB := OpenExcel(Path, 2)
xlWB.ActiveSheet.Range("B2").Value := "Test"
OpenExcel(Path, Sheet := "ActiveSheet")
{
SplitPath, Path, FileName
try
{
xlApp := ComObjActive("Excel.Application")
for xlWB in xlApp.Workbooks
if (xlWB.Name = FileName)
{
Found := true
break
}
}
catch
xlApp := ComObjCreate("Excel.Application")
if !Found
try
xlWB := xlApp.Workbooks.Open(Path)
catch
return
xlWB.Activate
if (Sheet != "ActiveSheet")
try
xlWB.Sheets(Sheet).Activate
xlApp.Visible := true
return xlWB
}
FG
Re: Open Excel or when already active go to active Excel window
Posted: 07 Nov 2019, 19:33
by nitrofenix
AviationGuy wrote: ↑25 Jan 2019, 04:21
But, this will work until Excel releases an update which removes the '- Excel' part after the title in the header of the window
Adding my $0.02 -- I used this for bringing Excel to the front and switching to the required workbook, matching title by handle rather than a string:
Code: Select all
oWB := oXL.Workbooks(wbName)
xlHwnd := oXL.Hwnd
WinActivate, ahk_id %xlHwnd%
oWB.Activate
Re: Open Excel or when already active go to active Excel window
Posted: 07 Nov 2019, 20:48
by DRocks
Thanks for your reply.
With your latest example I've not been able to reproduce the need to add the if (xlApp) condition.
Although I do wonder why you return xlWB instead of xlApp ?
I'm not able to use xlWB.Range() directly is this normal ?
Re: Open Excel or when already active go to active Excel window
Posted: 12 Nov 2019, 07:38
by DRocks
Re OpenExcel() @FanaticGuru
Have you ever encountered a exception where you have a minimized Excel workbook that isn't Found when using OpenExcel()?
I do not understand why the COM object works fine when the workbook is already openned and NOT in minimized window state.
But it doesn't work when minimized.
In this later case, it throws an exception when I first try to use the excelObject.Range() function.
wierd.
Ah you know what. I think it's my misunderstanding of xlApp vs xlWB relationship.
I was returning xlApp instead of xlWB with OpenExcel() function but there was a problem linked to this particular usage that made it impossible to use obj.Range() function if minimized.
When I correctly return xlWB and then use xlWB.ActiveSheet.Range() it works also in minimized state.
Still wierd but atleast it has a solution.
Thanks anyway
Re: Open Excel or when already active go to active Excel window
Posted: 14 Nov 2019, 14:47
by FanaticGuru
DRocks wrote: ↑12 Nov 2019, 07:38
Re
OpenExcel() @FanaticGuru
Although I do wonder why you return xlWB instead of xlApp ?
I'm not able to use xlWB.Range() directly is this normal ?
...
Ah you know what. I think it's my misunderstanding of xlApp vs xlWB relationship.
I was returning xlApp instead of xlWB with OpenExcel() function but there was a problem linked to this particular usage that made it impossible to use obj.Range() function if minimized.
Yes, it is important to realize this function returns a Workbook object not an Application object.
One of the points of the function is to get the right workbook when you have multiply workbooks open at the same time.
To get the Application object from the Workbook object, you can do:
xlApp := xlWB.Parent. This is often needed as there are many times you need to work with the Application object too.
Might be better to rename the function for more clarity.
Just looked in my library to change the name and noticed this is the actual version in my library that I already renamed at some point:
Code: Select all
; [Function] Excel_GetWB
; Fanatic Guru
; 2019 01 28
;
; Get Excel Workbook COM object
; start Excel and open WB if needed
;
;{-----------------------------------------------
;
; Excel_GetWB(Path, Sheet, Visible)
;
; Parameters:
; Path path or file name of Excel workbook
; Sheet sheet name or index number to activate (Default: ActiveSheet)
; Visible visible state (Default: true)
; "" results in current visible state with no change
;
; Example:
; xlWB := Excel_GetWB(A_Desktop "\Test\specificExcelFile.xlsx", "Sheet4")
;
;}
Excel_GetWB(Path, Sheet := "ActiveSheet", Visible := true)
{
SplitPath, Path, FileName
try
{
xlApp := ComObjActive("Excel.Application")
for xlWB in xlApp.Workbooks
if (xlWB.Name = FileName)
{
Found := true
break
}
}
catch
xlApp := ComObjCreate("Excel.Application")
if !Found
try
xlWB := xlApp.Workbooks.Open(Path)
catch
return
xlWB.Activate
if (Sheet != "ActiveSheet")
try
xlWB.Sheets(Sheet).Activate
if (Visible != "")
xlApp.Visible := Visible
return xlWB
}
FG
Re: Open Excel or when already active go to active Excel window
Posted: 14 Nov 2019, 18:01
by DRocks
Very nice and clear, thank you FG.