Jump to content
Macro Express Forums
joe

Excel Activate Workbook

Recommended Posts

Having trouble getting Macro Express to pop up an Excel workbook? When using Microsoft Excel with multiple open workbooks, Macro Express can see only the active workbook and not those which lie underneath. In other words, it can only see whichever workbook is currently displayed in the Excel window title. The Macro Express "Activate Window" command is rendered useless in this situation.

 

Here is a solution.

 

Attached to this topic is a zipped help (.chm) file that explains all. Just download, unzip, and read it. NOTE: After unzipping the CHM file, you might have to "Unlock" it. Microsoft has a security feature that prevents viewing a CHM file if it was copied from a network or downloaded. Simply right-click on the file and choose "Properties". Click on the "Unlock" button at the bottom of the General tab, then "Apply". That's it.

 

At some point we will be adding this, and other solutions, to our web site.

Help_Active_Window.zip

Share this post


Link to post
Share on other sites

Attached is a zipped setup.exe style file for this topic.

 

The file is for those who want to use a setup.exe style installer for the Activate Workbook solution in this topic.

 

See the next reply for an alternative.

Setup_Activate_Workbook_1005.zip

Share this post


Link to post
Share on other sites

This works great. Can a wildcard be used for the tail end of the filename? I have many files that need the same macro. All the files start with "Forecast" and then have business units and dates as the rest of the file name before the ".XLS". Only one file will be open at a time. It would be nice not to have to type each file name in.

 

Yes, the microwave is too slow for me...

Share this post


Link to post
Share on other sites
Can a wildcard be used for the tail end of the filename?

No. But I like the idea and can see the benefit. This particular applet is not examining file names, but rather the DOM objXL.Workbooks.Name, so it would require converting a wildcard string passed by the user into a regular expression pattern and then finding the first match.

 

We are currently working on a library of Excel commands to be accessed directly from Macro Express. So, this feature may show up there instead of here ... or both.

Share this post


Link to post
Share on other sites

Hello, I'm a beginner using Macro Express and I am unfamiliar with creating "temporary file name." Could someone briefly explain this to me? Thank you

Share this post


Link to post
Share on other sites

Joe,

 

I'm working extensively with Excel® and having all sorts of grief. Navigation and timing issues for the most part. Will you be doing a section on Excel® on your site?

 

 

FWIW, If you only have two workbooks open, sending ALT+F2 (type "F" then type "2", not the F2 key) to the keyboard is a decent toggle between them.

 

Another game is simply to record a VBA macro Excel® and save it in your person macros so it is available in any workbook. This is triggered by a hot key. So send that hot key to the keyboard with the text type command and you change to the desired window. You need a hotkey defined (ie, a VBA Macro) for each window but very quick to set up. Sample code:

 

-----------------------------------

Sub DEMO_CHNGE_WRKBK()

'

' DEMO_CHNGE_WRKBK Macro

' Macro recorded 4/1/2011 byPITA_Pan

'

' Keyboard Shortcut: Ctrl+Shift+Z

'

Windows("test.xls").Activate

End Sub

-----------------------------------

 

I'm using this sort of game to navigate around the sheets. I may want to add columns later so I go to named cells.

 

 

 

--------------------------------

Sub GO_TO_LOG_DTV()

'

' GO_TO_LOG_DTV macro

' go to log dtv

'

' Keyboard Shortcut: Ctrl+Shift+D

'

Application.Goto Reference:="DTV"

End Sub

-------------------------------

 

 

must be something more elegant.....

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Having trouble getting Macro Express to pop up an Excel workbook? When using Microsoft Excel with multiple open workbooks, Macro Express can see only the active workbook and not those which lie underneath. In other words, it can only see whichever workbook is currently displayed in the Excel window title. The Macro Express "Activate Window" command is rendered useless in this situation.

 

Here is a solution.

 

Attached to this topic is a zipped help (.chm) file that explains all. Just download, unzip, and read it. NOTE: After unzipping the CHM file, you might have to "Unlock" it. Microsoft has a security feature that prevents viewing a CHM file if it was copied from a network or downloaded. Simply right-click on the file and choose "Properties". Click on the "Unlock" button at the bottom of the General tab, then "Apply". That's it.

 

At some point we will be adding this, and other solutions, to our web site.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×