SetFocus Problem with comment by Microsoft Tech Support

[evangelmike]

Note: Comment by Microsoft Tech Support Engineer is at the end of this post in a PPS.

Greetings! The operating system on my computer is Windows Vista, and I am using Macro Scheduler Version 11. Under certain circumstances, a macro which uses the following code to change focus from one Excel 2007 workbook to another leaves the 2nd workbook in a somewhat unstable (or confused) state:

Code: Select all

SetFocus>BookDates.xlsm
waitready>0
Wait>0.3

The above instructions do indeed appear to change the focus to Workbook BookDates, but following the execution of the above code, the macro then attempts to select a range on which to perform a find operation as follows:

Code: Select all

Let>Tbl300End=IssuerListNDX+10
// IssuerListNDX points to the last cell of Table 300. The last 10 cells of
// the range are blank.
// Select C300:C%Tbl300End%
Press F5
Wait>0.3
WaitWindowOpen>Go To
Wait>0.3
Let>Param300={"c300"+":c%Tbl300End%"}
Send>%Param300%%CR%

Unfortunately, this code fails to select anything, and the worsheet appears to "freeze". The state of the workbook is strange. It will not respond to mouseclicks (although it will respond to the left, right, up, and down arrows), and one can't even exit Excel (Alt-f-x). In this state, Ctrl-F6 fails to change the focus. Via trial and error, I managed to unfreeze the sheet somewhat by doing a worksheet Format Cell operation (Select Home on the Ribbon-Format-Format Cells-OK, which I don't intend to format anything--just to unfreeze the worksheet enough so that I can exit Excel). When I replaced the first set of code above (which changes focus) with the below code to change focus, everything now works perfectly. I ran this macro about 6 times after changing the above code to the below code without any problems.

Code: Select all

// Try CTL-F6 to switch to next workbook; Set Focus>BookDates.xlsm seems not to be working 100%.
  Press CTRL
  Wait>0.2
  Press F6
  WaitReady>0
  Wait>0.2
  Release CTRL
  Wait>3

I worked on this problem from about 6 PM until midnight, and came close to reinstalling both Macro Scheduler and Excel 2007. I never suspected that the SetFocus was the cause of this problem. This is old code and worked perfectly when I was using Macro Scheduler Version 10. Has anyone else encounterd a problem with the SetFocus instruction in Version 11? I can email the code leading up to the above instructions to anyone who wishes to study it.

PS: It seems as if the worksheet has the focus for some operations but not for others after the macro performs the SetFocus operation.

PPS: Even after replacing the SetFocus with a CTL-F6, this workbook "Freeze" problem still occurs occasionally. It is now an intermittent problem and occurred this morning. Since no one on this forum seems to know how to approach this problem, I called Microsoft Tech Support (for which there would be a $49 charge when they resolve this problem.) I was routed to Gaurav Kumar, a Microsoft Tech Support Officer-Consumer Support for Excel 2007. I gave Gaurav permission to remotely access my Desktop. He was unable to select any cells on the worksheet of the frozen workbook. He noted that Ctrl-Home put A1 in the Address box, but the screen remained frozen. Likewise with Ctrl-End to go to the last data cell. With the frozen worksheet still open and having the focus, he suggested opening and closing a new blank workbook. This unfroze the screen of the "frozen" worksheet. He does not think that the problem is in his area of expertise. I should also add that yesterday, an expert in Excel and Excel VBA stopped over to my house and read the above description of this problem in this forum. He was shocked that no one on this forum had any comments or suggestions regarding this problem.

[Marcus Tettmar]

Michael,

I'm sorry I missed your forum post. It should be pointed out that the forums are largely peer-peer and while I try my best to monitor every new post that arrives I can only guarantee swift responses to our helpdesk. If you want a guaranteed response to a technical query please issue a ticket to our helpdesk (or send an email to support) as the helpdesk is monitored 24x7.

There are no open tickets from you in our help desk and I don't see any closed ones on this subject either. Our support is free and we would not have charged you $49. Also, unlike that of your Microsoft support engineer, our expertise is with Macro Scheduler and Windows Automation

I see the problem:

Your code is assuming a window title which is called entirely "BookDates.xlsm". If you look at Excel, you will see the Excel window is actually titled:

BookDates - Microsoft Excel

Have a look at the top title bar of your Excel window. THAT is what you need in your SetFocus line. BookDates.xlsm is not the window that you want.

Since your SetFocus line is not complaining there must be a hidden (or child) window called precislely "BookDates.xlsm" but since it is hidden or child there is no knowing what might happen if you try to focus it. Apps all behave differently and only THEY know what they will do. This probably explains why things are acting weird (focusing child windows directly may circumvent internal code that an application processes when IT decides to focus its window, because it was developed on the assumption that only IT would do the focusing, and if this code is important the app may be left in an unstable state).

You might also want to tell SetFocus to only look at visible windows using:

Let>WF_TYPE=2

This will help prevent such situations in future.

If this does not help please let me know and I will personally hook up to your desktop to debug it and fix it for you.

SetFocus has not been changed or even touched since v10 - so v11 is using the exact same code - which uses Win32 API functions. I have not seen any issues using V11 with Excel 2007. I will be happy to fix it for you.

I'm sorry your Microsoft friend was so shocked that there was no answer to your forum post. Perhaps you could ask him to look at a message I sent to a Microsoft support forum a couple of years ago which still hasn't received an answer (seriously)

This forum is great as it helps everyone rather than just the original poster and as such I try my best to answer every post unless another user has already answered it. But it is easy to miss stuff as it is not designed as a help desk. For a guaranteed response please send a ticket via our support system (http://www.mjtnet.com/contact.htm) which is monitored constantly.

If you need to get in touch with me between now and the 29th December would you please email me direct or send a ticket to the support desk as I myself will be visiting friends and family over the Christmas period and will not be able to check into the forum every day. I will however receive emails and support ticket alerts to my phone.

[Bob Hansen]

Another superb example of the unnique support provided by Marcus at MJT!

No other support team can match what is received here.

Keep up the great work Marcus.