Opening a Spreadsheet with VBA

Posted by TomH on January 23, 2002 7:17 AM

I have about 75 spreadsheet that I need to loop through extracting their data. How can I open a spreadsheet using VBA without actually opening it in it's own window. Currently, I'm doing something like this:
Workbooks.open
&LT;Do my extraction>
Workbooks.close

Thanks in advance,

Tom

Posted by Damon Ostrander on January 23, 2002 9:58 AM

Hi Tom,

Here's what I would suggest. Actually, it does open the workbook in its own window, but you never see it because screen updating is off until the window closes, and time is saved because Excel never has to "paint" the window.

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open FileName:="C:\MyAnalysis.xls"

'code to extract workbook data here

ActiveWindow.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True

Damon