vba: Refocus on original workbook

Damo71

Board Regular
Joined
Aug 17, 2010
Messages
88
Hi

I'm putting a macro in personal.xls so that I can run it on workbooks that are sent to me from external sources.

I need to get it to open a workbook "OMSREPORT.xls" and then make the original workbook the active workbook again so that I can continue working on it.

How do i either:
a) Open OMSREPORT.xls without making it the active workbook, or
b) get the macro to switch focus to the original workbook once OMSREPORT.xls is opened.

And, seeing as i'm here, is it possible to get the code to look at what workbook was the active one when the macro was executed, and if it is OMSREPORT.xls then stop it from running (it will damage the format/layout of OMSREPORT.xls if it is run on it)

Thx in advance
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Try like this

Code:
Dim wb As Workbook
Set wb = ActiveWorkbook
'
'code to open other file
'
wb.Activate
 
Upvote 0
Your main question and your bonus question both have the same answer. Create a workbook variable to store the current book, open the new one, then active the variable one again:

Code:
Dim wb as New Workbook

Set wb = activeworkbook

'run code to open new file and do some stuff in it, then:

wb.activate
'
'
set wb = nothing
 
Upvote 0
To open a workbook without making it active, you create a workbook 'object' which represents your workbook, open the workbook via that object, and then use the object to manipulate it.
Code:
Dim wkbk As Workbook
Set wkbk = Workbooks.Open("c:\folder\OMSREPORT.xls")
wkbk.Sheets(1).Range("a1") = "fred"
 
Upvote 0

Forum statistics

Threads
1,224,527
Messages
6,179,348
Members
452,907
Latest member
Roland Deschain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top