Open Excel or when already active go to active Excel window

[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!

[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

[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

[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

[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

[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'.

[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...

Window titles are case sensitive, except when using the i) modifier in a RegEx pattern.

[AviationGuy]

Still doesn't work unfortunately..
Changed the uppercase to lowercase like this

Code: Select all

xlWB := "xl"

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:

Spoiler

Code: Select all

Path = C:\...\ahkFunctionLibrary.xlsx
OpenExcel(Path)

;---------------------- Open Excel file ---------------
OpenExcel(Path) {
xlWB := "xl"
	SetTitleMatchMode 2
if(!WinExist(xlWB)) {
    XL := ComObjCreate("Excel.Application")
    ;Path = Path
	XL.Workbooks.Open(Path)
    XL.Visible := 1
	MsgBox %Path% `n is now loaded
} else {
    XL := ComObjActive("Excel.Application")
    WinActivate, %xlWB%
	MsgBox % "Excel already active"
}
}

Return

[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

[DRocks]

I needed to add this condition to make the example OpenExcel() function provided by FanaticGuru:

Code: Select all

if (xlApp) {
...
}

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

[FanaticGuru]

I needed to add this condition to make the example OpenExcel() function provided by FanaticGuru:

Code: Select all

if (xlApp) {
...
}

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

[nitrofenix]

Code: Select all

    WinActivate, Excel

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

[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 ?

[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

[FanaticGuru]

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

[DRocks]

Very nice and clear, thank you FG.