Open workbook without activating it

[Kelley]

First: I am not much of a VBA coder. Usually, I record a macro and
edit it slightly.

I would like to create a macro that opens a specific workbook without
making it the active workbook. I'd like it to be transparent to the
user. Since this is going in a template, the user may not have saved
his current workbook, so I can't just switch back to a specific
workbookname.

Any ideas?

 

[Tom Ogilvy]

Dim wkbk as Workbook
set wkbk = ActiveWorkbook
Application.ScreenUpdating = False
workbooks.Open "C:\My documents\Mybook.xls"
wkbk.Activate
Application.ScreenUpdating = True

 

[steve]

Kelley,

Dim wkbk as String

wkbk = Activeworkbook.name

ScreenUpdating = False ' prevents screen from showing changes
Workbooks.Open FileName:="MyDrive\MyFolder\MyBook.xls"
Windows(wkbk).Activate
ScreenUpdating = True

You might want to record a macro to get the Workbook.Open part
of the code.

This should work even if the wkbk hasn't been saved.
(code not tested)

 

[pfsardella]

Sub OpenNoActivate()
Dim wBk As Workbook

Set wBk = ActiveWorkbook
Application.ScreenUpdating = False
Workbooks.Open FileName:="YourFileName"
wBk.Activate
Application.ScreenUpdating = True

End Sub

Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul