Solved: Scheduled xls not starting properly

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

balloon_tom

Thread Starter
Joined
Jan 4, 2006
Messages
107
I thought we had all the bugs out yesterday ..but not to be.

I have an Excel workbook that opens a UserForm immediately upon opening (using 'Sub Workbook_open' in the 'ThisWorkbook" code). The UserForm begins a 30 second timer (via the sub UserForm_Activate command). After the 30 second timeout, code passes back to the ThisWorkbook code which runs a macro. I have a desktop icon for this workbook. Every time I run the program it runs perfectly!!

Now the problem. I have scheduled this workbook to run by the 'Scheduled Tasks' program in Control Panel. Excel opens right on time and the workbook is loaded after 'virus scan' (according to the status bar...I don't know if that is Norton or Excel that does that). Then the blue border of the worksheet begins to flash deep blue to pale blue, the taskbar display for this workbook flashes blue to orange, and the UserForm is NOT displayed. If I click anywhere on the worksheet the UserForm opens and the program proceeds properly.

Does the workbook not have focus when opened by the scheduler? Any thoughts?
TIA,
Tom
 

OBP

Joined
Mar 8, 2005
Messages
19,896
Do you have to give the user form check button or command button focus after "showing" the form? or make the sheet it is on the "Active" sheet?
PS well done for getting this far!
 

balloon_tom

Thread Starter
Joined
Jan 4, 2006
Messages
107
I tried placing 'ThisWorkBook.Activate' as the first line in 'WorkBook_Open'. This did not help. However, I now have the scheduler wndow open and have found that I can right click on the workbook name in the scheduler and I am given an option to 'Run'. This saves me from having to reset the schedule time a few minutes out and then waiting. The problem also occurs using this method.
BUT...I now notice that when the Excel sheet opens and starts blinking, the scheduler window is still deep blue and active. How do I get focus (or whatever) away from the scheduler?
 
Joined
Jul 25, 2004
Messages
5,458
It's flashing because the Application doesn't have focus, not the workbook. You can activate all the workbooks you want (and it will) but if the app isn't showing, then it still won't matter really. You may be able to get the window to activate by manipulating the Application object or using API. Haven't tried it and I don't really have time today to. If you still need more help by tonight I'll see what I can do.
 

balloon_tom

Thread Starter
Joined
Jan 4, 2006
Messages
107
Zack,
I've tried every command I could think of, but no joy. I even had one open up the calculator instead! Any help would be appreciated.
Tom
 
Joined
Jul 25, 2004
Messages
5,458
I don't think there is anything you can do for this with VBA, tell you the truth. If there is, I don't know how. You may be able to get away with it by creating a batch or executable file, or even a VBScript file which opens the file that way and can control the windows environment. That would mean some kind of core code (VB, C, etc.). Though without the user interaction, I'm not sure how this can be done. Sorry. May be a good question for the Development forum.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
Tom, if you just want to be able to open this workbook at other times without running the macro, can you do as I originally suggested and just have a Time check in the macro itself or a module that it calls that stops the macro running at any time other than the one you want?
 

balloon_tom

Thread Starter
Joined
Jan 4, 2006
Messages
107
I have tried a batch file with moderately positive results:

ECHO ON
Excel/RUN C:\TOM\ab\xcel\PAtstock.xls
EXIT

The .xls opens and runs great! Except, it opens in 'read-only' mode so I can't save it automatically. VB might be better but I'm only now starting to play with it.

Also, I haven't written a batch file since all file names were 8 characters long. Therefore, I stored the batch file in 'Office11' as I don't know how to code 'chdir "c:\Program Files\Microsoft Office\Office11' such that dos will recognize it.

Any suggestions are more than welcome.

Tom
 
Joined
Jul 25, 2004
Messages
5,458
I'm not positive (haven't tested) but you should be able to code that in VBS, and you could just use the Shell command to do that as well. (Read up on the Shell command on microsoft support site.)
 

balloon_tom

Thread Starter
Joined
Jan 4, 2006
Messages
107
This is now working satisfactorily using a dos batch file, called by the scheduler, to invoke the excel workbook. My next phase wil be to write a VB to replace the batch but that will be another issue a few days out.
Thanks to the entire group for your help and support!!!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top