Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this be done?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this be done?
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. -- (please remember to click yes if replies you receive are helpful to you) "michael.beckinsale" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this be done?
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|