Can this be done?

[michael.beckinsale]

Hi All,

Using FSO l am looping thru approx 30 files in a folder and retrieving
data. Additionally in each of the files there is an error checker so l
thought it would be a good idea to run the error checker and retrieve
the number of errors at the same time.

It all works fine apart from 'ScreenUpdating'. If l run the macro
without this code snippet (which runs the error checker) there is no
screen updating whatsoever as it was set to FALSE and has not been
switched back on again.

If l run the code with the code snippet the 'window' is constantly
switching between the 2 open workbooks as each file in the folder is
opened.

Is there a way round this??

CODE SNIPPET:
Application.Run (myFN & "!Unprotect")
Application.Run (myFN & "!ErrCheck")
SendKeys "{ENTER}", False
ErrorNos = mybook.Sheets("Start Here").Range("D28").Value

Sorry about the SendKeys but it is needed to respond to a MsgBox in
the ErrCheck macro. Unless anybody knows a better way?

Regards

Michael Beckinsale

 

[SeanC UK]

Hi Michael,

Is it only the switching from one window to another that you are trying to
stop? If so you could try playing around with

Application.Visible = True/False

I would test this first though, to see how it works with your SendKeys.

If you are able to send Enter to the message box each time, then I would
look at trying to work around it so that the message box isn't used. In fact,
if you do change the visibility of Excel, then you could write in the
ErrCheck macro:

If Application.Visible = True Then
MsgBox("")
End If
So you don't need the SendKeys anyway.

Just be careful if you use the Application.Visible because if an error
occurs and you click End then Excel will still be running invisibly. If you
click Debug then just type Application.Visible = True in the Immediate window.

I hope this helps,

Sean.

 

[michael.beckinsale]

Hi Sean,

Thanks for your suggestions.

Unfortunately the workbooks to be consolidated have been already been
distributed and it seems a bit of an overkill to either manually
change the code re the MsgBox or change the code re some sort of
automation.

However your Application.Visible has given me some ideas and l will
play around with that. I may just minimise the window while the macro
is running and then re-instate at the end of the code, although the
purpose of using ScreenUpdating was to gain those extra few seconds in
speed.

Once again thank you for your reponse. If you have any other ideas
they will be gratefully received.

Regards

Michael Beckinsale.