wait while excel loads file

[rroach1991]

Hi,

The following script loads all files in a folder sequentially and runs an excel macro on the files. Problem is that I don't seem to have control on how long it waits while loading the file. Some files get the CTRL-W command inserted in a random field, while some CTRL-W causes the macro to execute. I may be wrong, but my guess is that where I have bolded it below is where I need to exert better control over waitwindow....something? In other words, HELP! TIAk=0
Repeat>k
Let>k=k+1
//Message>file_names_%k%
Let>filename=file_names_%k%
// set focus to Excel
SetFocus>Microsoft Excel*
WaitWindowOpen>Microsoft Excel*

// file open dialog box in Excel, filled with file name from list
Press ALT
Send>fo
Release ALT
WaitWindowOpen>Open
Send>file_names_%k%
Wait 0.5
Press ALT
Send>o
Release ALT
//End file open dialog

//run trim columns macro
WaitWindowOpen>Microsoft Excel*
SetFocus>Microsoft Excel*
CapsOff
Press CTRL
Wait>.5
Send>w
Wait>.5
Release CTRL

[Bob Hansen]

If this is running an Excel macro, how about adding "DONE" to a particular cell.

Then use Macro Scheduler to check contents of that cell. If not DONE, then loop every few seconds until it is DONE. When DONE, first step in Macro Scheduler will be to clear that cell before Macro Scheduler continues.

[rroach1991]

Problem occurs in macro scheduler before I get to run the macro. Another way to say it is that the macro sometimes fires off too soon--gotta stop macro scheduler from sending the Excel CTRL-w command until the file is loaded.

[JRL]

Rob,

In my Excel the file name is in the title bar with Excel. It looks something like "Microsoft Excel - Filename.xls". What would happen if you changed the last part of the script to:

//run trim columns macro
//Don't know if this step is necessary but it might be
Let>openfile=file_names_%k%
WaitWindowOpen>%openfile%*
SetFocus>Microsoft Excel*
//Add a short wait just for good measure, length to be determined.
Wait>1
CapsOff
Press CTRL
Wait>.5
Send>w
Wait>.5
Release CTRL

Just a thought,
Dick

[rroach1991]

Dick,

I like the diea, but at least in the following implementation it locks up Excel.

// file open dialog box in Excel, filled with file name from list
Press ALT
Send>fo
Release ALT
WaitWindowOpen>Open
Send>file_names_%k%
Wait 0.5
Press ALT
Send>o
Release ALT
Let>openfile=file_names_%k%
WaitWindowOpen>%openfile%
SetFocus>Microsoft Excel*
Wait>1
//End file open dialog

Any suggestions anyone on how to wait until that file is open?

Rob

[Bob Hansen]

Don't have access to Excel at this moment, but based on input from JRL you could try this:

Code: Select all

Let>openfile=file_names_%k%
WaitWindowOpen>Microsoft Excel - %openfile%*
SetFocus>Microsoft Excel - %openfile%*

[JRL]

Rob,

Just set it up and tried it and it works fine for me. But in the testing I came accross a couple of things that might be problems.

1) You have a macro you're executing so obviously your file has a macro, how is your Excel security set up for opening files with macros? Do you need to account for this in the script? For example, I have to send an "e" to enable macros .

2) Is the path found in the filename variable? If it is you'll need to strip off the path and use only the file name in the WaitWindowOpen>%openfile%* line. I think you could do this using separate with "\" as the delimiter.

Hope this helps,
Dick

[rroach1991]

Dick,

I bet you're right that the path is in the filename string. I'll get rid of it and try it. Thanks again for the help. BTW, macro security is not a problem (actually the way it works in excel IS a problem, just not mine at the moment!).

Thanks!

Rob

[JRS]

All,

Again from firsthand experience Bob's suggestion of using
Done in a cell as a status flag for Macro Scheduler.

Coincidentally the exact string he sugggests using: Done
I used as the flag in a cell especially being monitored by
Macro Scheduler.

(No I'm not getting paid or being otherwise compensated for
giving props to Bob - I just like giving credit where credit
is due)

Joel S.

[Methuselah]

The default actions for the Microsoft Office suite defines CTRL+W or CTRL+F4 as "Close the active workbook window."

You mention that Ctrl+W runs a "Trim Columns" macro ... so I assume that this custom macro (and shortcut) exists in each of the files you are loading ?

Your other option (one that I use often) is to do almost everything externally to the Macro Scheduler ... in Excel VBA

... change the logic to open a "master" Excel workbook,
... one which perhaps runs an Auto_Exec macro.
... Have this macro open all the required files itself (sequentially)
... you can then even issue the trim code from that workbook
... without having to ensure the macro lives in each workbook.

Not quite an MSched solution, but you have much more control as the
next line on the code (trim columns) will not be executed until the
workbook has opened ... and you remove the timing vaguaries.

Of course, if you are not comfortable writing VB code, this won't help
Pop me an email if you need example code.

robinm at nedbank-dot-co-dot-za

[Methuselah]

In case others are watching ... here's the code example
Option Explicit

Dim sDir As String
Dim sFileName As String
Dim sController As String

'== Rename subroutine to "Auto_Open" for immediate execution.
Private Sub Junk()

'== Don't flash updates at the user
Application.ScreenUpdating = False

'== Who are we ?
sController = Application.ActiveWorkbook.Name

'== Run through Excel files in the directory.
'== Display range B3 contents

'== Assuming files to scan are in same folder as Controller.
sDir = Application.ActiveWorkbook.Path


sFileName = Dir(sDir & "\*.xls", vbNormal) '== Want Excel files only.
Do While sFileName "" '== While we have some.

'== We don't want to re-open ourselves
If sFileName sController Then

'== Open the file found
'== Excel will auto-switch focus to the new file
Application.Workbooks.Open sDir & "\" & sFileName, False, False

'== Do stuff on active file here
Debug.Print sFileName & "(B3)contains - " & Application.Range("B3").Value

'== Close (with save ?)
' Application.ActiveWorkbook.Save
Application.ActiveWorkbook.Close (False)
DoEvents

End If

sFileName = Dir '== Any more ?
DoEvents

Loop

'== Don't forget to turn updates back on again
Application.ScreenUpdating = True

MsgBox "All Done"


End Sub





HTH ... Meths

[JRS]

Quoting Methuselah:

Your other option (one that I use often) is to do almost everything externally to the Macro Scheduler ... in Excel VBA

Exactly Methuselah! ... Exactly!

Joel S.