Script to quickly navigate between multiple Excel windows

[WeThotUWasAToad]

Hello,

Is it possible to create a script which, upon activation with a hotkey, shows a list of all Excel files (workbooks) currently open on your system, and then by clicking one of the filenames, causes that particular workbook to become the active window on your screen?

I am still using Excel 2010* and I always have multiple (6-15) workbooks open. As a result, when I mouse over the Excel icon on my taskbar, I cannot see the filenames of each individual workbook thumbnail. Consequently, I'm constantly having to spend additional time navigating between multiple workbooks to get back to the one I want just then.

I've never been able to figure out how to upload a screenshot here so following is a link to an image which shows what I am referring to:

https://imgur.com/5RzNRu6

I don't know if the script would need to be for a GUI or if some other approach would be more suitable but I sure do appreciate whatever assistance can be offered.

Thanks

*Because it has several features I like which are not found in subsequent versions

[flyingDman]

First, because your using Excel 2010, lets see if this works. Open a few excel files and launch this:

Code: Select all

Xl := ComObjActive("Excel.Application") 				         
for x in xl.Workbooks
	msgbox % x.name

[boiler]

Here's a script to get you started. It creates buttons for each of the Excel windows open when the script is run. If you open or close Excel windows, hit the Refresh button. Other improvements could be made to it such as auto-refresh when the windows change. Tested with the latest version of Office/Excel. I would think it would work with Excel 2010. If not, it would only be minor changes to Excel's window title and/or process name.

Code: Select all

SetTitleMatchMode, 2
ExcelList := {}
WinGet, WinList, List, - Excel ahk_exe EXCEL.EXE
loop, % WinList
{
	WinGetTitle, Title, % "ahk_id" WinList%A_Index%
	ExcelList[StrReplace(Title, " - Excel")] := WinList%A_Index%
}
Gui, +ToolWindow +AlwaysOnTop
for Title, ID in ExcelList
	Gui, Add, Button, xm w200 v%ID% gExcelBtn, % Title
Gui, Add, Button, xm+50 w100, Refresh
Gui, Show,, Excel Windows
return

ButtonRefresh:
reload

ExcelBtn:
	WinActivate, % "ahk_id" A_GuiControl
return

GuiClose:
ExitApp

Edit: Just saw flyingDman's post. Could go down that route or use this approach.

[flyingDman]

Assuming COM works just fine in Excel 2010, this is a wrapper around those few lines above:

Code: Select all

Xl := ComObjActive("Excel.Application") 				         

#\::
gui, margin, 0,0
gui, -caption
gui, add, ListView, x0 y0 w150 r20 grid gLaunch,Active Excel Spreadsheets
for x in xl.Workbooks
	lv_add("",x.name) 
gui, show
return

launch:
if (A_GuiEvent = "DoubleClick")
	{
	LV_GetText(RowText, A_EventInfo)  									
	winactivate, % RowText
	}
esc::
gui, destroy
return

[WeThotUWasAToad]

Thanks for the replies.

First, because your using Excel 2010, lets see if this works. Open a few excel files and launch this:

Code: Select all

Xl := ComObjActive("Excel.Application") 				         
for x in xl.Workbooks
	msgbox % x.name

Yes, that seems to work great. It shows a MsgBox with the filename of the first Excel thumbnail; not the currently active or most recently active file, but the first one originally opened.

Thanks

[WeThotUWasAToad]

Here's a script to get you started. It creates buttons for each of the Excel windows open when the script is run. If you open or close Excel windows, hit the Refresh button. Other improvements could be made to it such as auto-refresh when the windows change. Tested with the latest version of Office/Excel. I would think it would work with Excel 2010. If not, it would only be minor changes to Excel's window title and/or process name.

Code: Select all

SetTitleMatchMode, 2
ExcelList := {}
WinGet, WinList, List, - Excel ahk_exe EXCEL.EXE
loop, % WinList
{
	WinGetTitle, Title, % "ahk_id" WinList%A_Index%
	ExcelList[StrReplace(Title, " - Excel")] := WinList%A_Index%
}
Gui, +ToolWindow +AlwaysOnTop
for Title, ID in ExcelList
	Gui, Add, Button, xm w200 v%ID% gExcelBtn, % Title
Gui, Add, Button, xm+50 w100, Refresh
Gui, Show,, Excel Windows
return

ButtonRefresh:
reload

ExcelBtn:
	WinActivate, % "ahk_id" A_GuiControl
return

GuiClose:
ExitApp

Edit: Just saw flyingDman's post. Could go down that route or use this approach.

I just tried this script and all it did was open a small pop-up displaying a button with the word "Refresh". Pressing the button did nothing nor did rerunning the script after pressing the button.

Thanks

[boiler]

Like I was saying, the Excel 2010 window titles and/or process name could be different. This code worked for me with the latest version of Excel. If you want, you can post the info from the Window Spy tool to see what the differences may be.

[JoeWinograd]

Hi Steve,

Try the attached script. I assume that you want to include it in your AutoHotkey start-up script with all of your AHK hotkeys/hotstrings so I made it such that you can simply copy/paste it in there as is (or running it as its own file works, too). I made the hotkey Alt+Ctrl+F3, but, as always, make it whatever you want (and, of course, it must be unique). Btw, I sorted the list of currently open workbooks alphabetically...I figured that was a good choice.

I tested it with Office 2010 on W10 Pro 64-bit and with Office 365 on W10 Pro 64-bit. Works perfectly here, but let me know if you have any problems. Regards, Joe

[WeThotUWasAToad]

Assuming COM works just fine in Excel 2010, this is a wrapper around those few lines above:

Code: Select all

Xl := ComObjActive("Excel.Application") 				         

#\::
gui, margin, 0,0
gui, -caption
gui, add, ListView, x0 y0 w150 r20 grid gLaunch,Active Excel Spreadsheets
for x in xl.Workbooks
	lv_add("",x.name) 
gui, show
return

launch:
if (A_GuiEvent = "DoubleClick")
	{
	LV_GetText(RowText, A_EventInfo)  									
	winactivate, % RowText
	}
esc::
gui, destroy
return

__________________________________________________

Running this script pops up a box with the title:

"Active Excel Spreadsheets"

Under the title is the file name of the first spreadsheet (but none of the others). Clicking (or double-clicking) the filename does not do anything except select it.

Please do not interpret my comments about what does not happen as complaints. I'm simply trying to give all the feedback I can. I'm actually thrilled with the way this looks. Being able to get a pop-up in the middle of my screen (assuming it will display a list of all currently open Excel files and allow me to click on the one I want to activate) is exactly what I'm after.

By the way, I altered some small things in your script:
• Changed "Active Excel Spreadsheets" to "Currently Running Excel Files"
• Changed "w150" to "w300" (I have some large file names)

Modified script:

Code: Select all

Xl := ComObjActive("Excel.Application") 				         

#\::
gui, margin, 0,0
gui, -caption
gui, add, ListView, x0 y0 w300 r20 grid gLaunch,Currently Running Excel Files
for x in xl.Workbooks
	lv_add("",x.name) 
gui, show
return

launch:
if (A_GuiEvent = "DoubleClick")
	{
	LV_GetText(RowText, A_EventInfo)  									
	winactivate, % RowText
	}
esc::
gui, destroy
return

Also, how do you increase the font size. I didn't see that.

Thanks

[JoeWinograd]

Hi Steve,

Here's a sample of what the selector looks like:

SteveSelectExcelWorkbook example.png (15.05 KiB) Viewed 2418 times

Also, attached is an enhanced version that makes it easier to control the font. I put these variables in the script so that they are easy for you to change:

Code: Select all

; *** begin variables to change
LVcolumnHdr:="Double-click a workbook or single-click and press Enter"
GuiWidth:=600
LVrows:=10
FontName:="Arial"
FontSize:="12"
FontColor:="Blue"
FontWeight:="600"
; *** end variables to change

As you can see, I assigned initial values for the GUI width and the number of rows, but the GUI is also manually sizable...just drag the corners as you would any window. Regards, Joe

[flyingDman]

This code

Code: Select all

Xl := ComObjActive("Excel.Application") 				         
for x in xl.Workbooks
	msgbox % x.name

should show all open spreadsheets, one after the other, not just the first one. Make sure you have a few open before launching the script. Please confirm. If it does not work, @JoeWinograd 's script won't work either as it is basically the same as mine.

[JoeWinograd]

Yep, the heart of flyingDman's script and mine are the same...and both work perfectly with Office 2010 and Office 365 on W10. Regards, Joe

[WeThotUWasAToad]

Hi Steve,

Try the attached script. I assume that you want to include it in your AutoHotkey start-up script with all of your AHK hotkeys/hotstrings so I made it such that you can simply copy/paste it in there as is (or running it as its own file works, too). I made the hotkey Alt+Ctrl+F3, but, as always, make it whatever you want (and, of course, it must be unique). Btw, I sorted the list of currently open workbooks alphabetically...I figured that was a good choice.

I tested it with Office 2010 on W10 Pro 64-bit and with Office 365 on W10 Pro 64-bit. Works perfectly here, but let me know if you have any problems. Regards, Joe

Thanks for the post Joe,

I ran your script but got a "Warning" pop-up which says: "Excel not active".

As a result, I activated one of my Excel files and tried it again. In this case, I got a larger pop-up window which says:
"Double-click a workbook or single-click and press Enter" and then, in blue, the name of the first Excel file opened (the furthest to the left thumbnail when mousing over the taskbar Excel icon) but no other open files. By the way, it doesn't matter which Excel file I activate, the pop-up always shows the first Excel file opened but no others.

Hopefully that info is enough to diagnose the problem since I don't understand much in your script.

By the way, I think it might be preferable if you can post your scripts using the tags in the forum rather than as attachments. That way, the script can be viewed and comments can be made by anyone who is looking at the thread. Inserting it into my large ahk file (as you mentioned) or running it as a standalone script is easy either way — and fewer steps (when inserted between tags).

Thanks a bunch as always Joe

[WeThotUWasAToad]

This code

Code: Select all

Xl := ComObjActive("Excel.Application") 				         
for x in xl.Workbooks
	msgbox % x.name

should show all open spreadsheets, one after the other, not just the first one. Make sure you have a few open before launching the script. Please confirm. If it does not work, @JoeWinograd 's script won't work either as it is basically the same as mine.

Yes, flyingDman, I tried your script with 7 separate Excel files open but still only get the first file name. Also, the 7 files are definitely open before running the script. As just mentioned, the same thing occurs with the script from Joe Winograd.

Thanks

[flyingDman]

OK. Probably outdated Excel version...

[JoeWinograd]

Hi Steve,

> I ran your script but got a "Warning" pop-up which says: "Excel not active".

Yes, I put in that MsgBox if Excel is not the active window when you hit the hotkey.

> As a result, I activated one of my Excel files and tried it again.

I wrote it assuming that you would have many Excel workbooks open (of course, it works with only one open) and that Excel would be the active window.

> In this case, I got a larger pop-up window which says

That's the ListView (GUI) that contains every active workbook. An example is the SteveSelectExcelWorkbook example.png that I posted earlier.

> then, in blue, the name of the first Excel file opened

I hard-coded it as blue in the Gui,Font statement in the first script, then gave you a variable for it in the V2 script (FontColor).

> but no other open files

It shows all the open files here, as you can see with the five files in the ListView that I posted earlier (SteveSelectExcelWorkbook example.png). That screenshot is from a W10 system running Excel 2010.

> By the way, it doesn't matter which Excel file I activate, the pop-up always shows the first Excel file opened but no others.

It doesn't matter which Excel file is active...the ListView (GUI) should show all of them (and does here).

> Hopefully that info is enough to diagnose the problem since I don't understand much in your script.

Nope, not enough info. It works perfectly here (as does flyingDman's) and I don't know why it doesn't for you.

> By the way, I think it might be preferable if you can post your scripts using the tags in the forum rather than as attachments.

I nearly always do (as you know from our many threads together on EE), but I thought it would be easier for you as an attachment.

> That way, the script can be viewed and comments can be made by anyone who is looking at the thread.

True both ways, imo.

> Inserting it into my large ahk file (as you mentioned) or running it as a standalone script is easy either way — and fewer steps (when inserted between tags).

Fair enough. Here it is in code tags:

Code: Select all

!^F3:: ; hotkey is Alt+Ctrl+F3 - make it whatever you want
SplitPath,A_ScriptName,,,,ScriptNameNoExt ; get script name without extension to use in window title
If ((WinExist(A_ScriptName) or WinExist(ScriptNameNoExt)) and WinExist("ahk_class AutoHotkeyGUI"))
{
  Gui,+OwnDialogs
  MsgBox,4144,Warning,Excel workbook selector is already running
  Return
}
If (!WinActive("ahk_exe excel.exe"))
{
  MsgBox,4144,Warning,Excel not active
  Return
}
Try oExcel:=ComObjActive("Excel.Application")
Catch
{
  MsgBox,4144,Error,Unable to create Excel object - should never happen
  Return
}

; *** begin variables to change
LVcolumnHdr:="Double-click a workbook or single-click and press Enter"
GuiWidth:=600
LVrows:=10
FontName:="Arial"
FontSize:="12"
FontColor:="Blue"
FontWeight:="600"
; *** end variables to change

LVwidth:=GuiWidth-30 ; leave room for LV to fit in gui
Gui,+Resize
Gui,Font,s%FontSize% w%FontWeight% c%FontColor%,%FontName%
Gui,Add,ListView,Sort xm w%LVwidth% r%LVrows% gWorkbookSelect vWorkbookLV,%LVcolumnHdr%
Gui,Add,Button,Hidden Default,Default ; so Enter key can be used in LV to select
GuiControl,-Redraw,WorkbookLV  ; improve performance by disabling redrawing during load
For oWorkbook in oExcel.Workbooks
{
  RowNumber:=LV_Add("",oWorkbook.Name)
  LV_Modify(RowNumber,"Vis") ; scroll LV so newly added last row is visible
}
GuiControl,+Redraw,WorkbookLV  ; re-enable redrawing
LV_ModifyCol(1,"AutoHdr") ; auto-size each column to fit contents and include header in case it is wider than workbook name
Gui,Show,w%GuiWidth%,%ScriptNameNoExt%
Return

WorkbookSelect:
If (A_GuiEvent="DoubleClick")
{
  LV_GetText(WorkbookName,A_EventInfo) ; get workbook name from column 1
  WinActivate,%WorkbookName%
}
Goto,GuiClose

ButtonDefault:
GuiControlGet,FocusControl,Focus
If (FocusControl!="SysListView321")
  Return
SelectedRow:=LV_GetNext(0,"Focused")
LV_GetText(WorkbookName,SelectedRow,1) ; get workbook name from column 1
WinActivate,%WorkbookName%
Goto,GuiClose

GuiSize: ; expand or shrink LV if window resized
If (A_EventInfo=1) ; window minimized, no action needed
  Return
; window has been resized or maximized, resize LV to match
LVwidth:=A_GuiWidth-30 ; leave room for LV to fit in gui
LVheight:=A_GuiHeight-30 ; leave room for LV to fit in gui
GuiControl,Move,WorkbookLV,w%LVwidth% h%LVheight%
Return

GuiClose:
GuiEscape:
Gui,Destroy
ObjRelease(oExcel)
Return

Regards, Joe

[JoeWinograd]

OK. Probably outdated Excel version...

I don't think that's the problem, since it works fine here with Excel 2010, but I don't know why it's not working for him. Regards, Joe

[JoeWinograd]

I ran your script but got a "Warning" pop-up which says: "Excel not active".
As a result, I activated one of my Excel files and tried it again.

Here's a version of the script (V3) that doesn't require Excel to be the active window when the hotkey fires:

Code: Select all

!^F3:: ; hotkey is Alt+Ctrl+F3 - make it whatever you want
SplitPath,A_ScriptName,,,,ScriptNameNoExt ; get script name without extension to use in window title
If ((WinExist(A_ScriptName) or WinExist(ScriptNameNoExt)) and WinExist("ahk_class AutoHotkeyGUI"))
{
  Gui,+OwnDialogs
  MsgBox,4144,Warning,Excel workbook selector is already running
  Return
}
If (!WinExist("ahk_exe excel.exe"))
{
  MsgBox,4144,Warning,No Excel workbooks are open
  Return
}
If (!WinActive("ahk_exe excel.exe"))
  WinActivate
Try oExcel:=ComObjActive("Excel.Application")
Catch
{
  MsgBox,4144,Error,Unable to create Excel object - should never happen
  Return
}

; *** begin variables to change
LVcolumnHdr:="Double-click a workbook or single-click and press Enter"
GuiWidth:=600
LVrows:=10
FontName:="Arial"
FontSize:="12"
FontColor:="Blue"
FontWeight:="600"
; *** end variables to change

LVwidth:=GuiWidth-30 ; leave room for LV to fit in gui
Gui,+Resize
Gui,Font,s%FontSize% w%FontWeight% c%FontColor%,%FontName%
Gui,Add,ListView,Sort xm w%LVwidth% r%LVrows% gWorkbookSelect vWorkbookLV,%LVcolumnHdr%
Gui,Add,Button,Hidden Default,Default ; so Enter key can be used in LV to select
GuiControl,-Redraw,WorkbookLV  ; improve performance by disabling redrawing during load
For oWorkbook in oExcel.Workbooks
{
  RowNumber:=LV_Add("",oWorkbook.Name)
  LV_Modify(RowNumber,"Vis") ; scroll LV so newly added last row is visible
}
GuiControl,+Redraw,WorkbookLV  ; re-enable redrawing
LV_ModifyCol(1,"AutoHdr") ; auto-size each column to fit contents and include header in case it is wider than workbook name
Gui,Show,w%GuiWidth%,%ScriptNameNoExt%
Return

WorkbookSelect:
If (A_GuiEvent="DoubleClick")
{
  LV_GetText(WorkbookName,A_EventInfo) ; get workbook name from column 1
  WinActivate,%WorkbookName%
}
Goto,GuiClose

ButtonDefault:
GuiControlGet,FocusControl,Focus
If (FocusControl!="SysListView321")
  Return
SelectedRow:=LV_GetNext(0,"Focused")
LV_GetText(WorkbookName,SelectedRow,1) ; get workbook name from column 1
WinActivate,%WorkbookName%
Goto,GuiClose

GuiSize: ; expand or shrink LV if window resized
If (A_EventInfo=1) ; window minimized, no action needed
  Return
; window has been resized or maximized, resize LV to match
LVwidth:=A_GuiWidth-30 ; leave room for LV to fit in gui
LVheight:=A_GuiHeight-30 ; leave room for LV to fit in gui
GuiControl,Move,WorkbookLV,w%LVwidth% h%LVheight%
Return

GuiClose:
GuiEscape:
Gui,Destroy
ObjRelease(oExcel)
Return

If there are no Excel workbooks open, you'll get a MsgBox that says, "No Excel workbooks are open". But if any Excel workbooks are open, even if one of them is not the active window, it will work (by activating one of them).

This does not address the problem that you're having of getting only one workbook in the ListView (GUI)...it addresses only the issue where you hit the hotkey when an Excel workbook is not the active window. Regards, Joe

[boiler]

In case this helps, on my Win10 laptop, scripts connecting to Excel via COM do not work even though they do on my Win10 desktop. They both are running Excel 365. It’s like it doesn’t recognize the Excel application when doing the ComObjConnect on my laptop. I thought there was a thread on here somewhere that talks about it and how to fix it.

If you want to try to get my version of the script working which doesn’t rely on COM, please post the Excel WinTitle and process name info for Excel 2010 which is found using the Window Spy tool so we can modify my script accordingly.

[WeThotUWasAToad]

Thanks everyone for your contributions.

OK. Probably outdated Excel version...

Yes, as mentioned in the OP, I'm using Excel 2010 in Windows 10.*
__________________________________________________

…it works fine here with Excel 2010, but I don't know why it's not working for him. Regards, Joe

Precisely.

I don't know if the following is contributing to the problem on my end but I've got 2 versions of MS Office installed because I use MS Office 2010 Excel, Word, & PowerPoint and MS Office 2013 OneNote. See screenshot image from my start menu here: https://imgur.com/bbhIQMJ

Also, Excel 365 has been mentioned a couple of times. I don't know what that is but I don't use it.

__________________________________________________

In case this helps, on my Win10 laptop, scripts connecting to Excel via COM do not work even though they do on my Win10 desktop. They both are running Excel 365. It’s like it doesn’t recognize the Excel application when doing the ComObjConnect on my laptop. I thought there was a thread on here somewhere that talks about it and how to fix it.

If you want to try to get my version of the script working which doesn’t rely on COM, please post the Excel WinTitle and process name info for Excel 2010 which is found using the Window Spy tool so we can modify my script accordingly.

I've got 8 Excel files running right now. I activated one of them; then, while keeping the mouse over it, I captured a screenshot of Window Spy. The result can be seen here: https://imgur.com/oPlJ40R.

I hope that provides enough and the correct information.

Thanks again.

*Correction: in the OP I did mention Excel 2010 but did not mention Windows 10.