Open workbook without activating it

  • Thread starter Thread starter Kelley
  • Start date Start date
K

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?
 
Dim wkbk as Workbook
set wkbk = ActiveWorkbook
Application.ScreenUpdating = False
workbooks.Open "C:\My documents\Mybook.xls"
wkbk.Activate
Application.ScreenUpdating = True
 
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)
 
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
 
Back
Top