CODEWITHSUNDEEP
vbaexcel
Shashank Banerjee
Shashank Banerjee

Reputation: 93

Stay in the active workbook after opening other workbooks

I am trying to open two other Excel workbooks to take data to do my calculation through the macro. I have a working code to open those files.

Dim wbCount As Integer
Application.ScreenUpdating = False
For wbCount = 1 To 2
Workbooks.Open Filename:=ThisWorkbook.Path & "\Installation Sheet " & wbCount & ".xlsm", UpdateLinks:=False
Next wbCount
ThisWorkbook.Activate
Application.ScreenUpdating = True

The other files are named "Installation Sheet 1" and "Installation Sheet 2". After this code runs, I end up with "Installation Sheet 1" displaying instead of my active workbook.

Upvotes: 3

Views: 4224

Answers (4)

Trism11
Trism11

Reputation: 11

Coming in late on this one but I couldn't find an answer that worked for me. I don't know the mechanisms behind it all but no matter what I do it will never reactivate the original wbk after opening a new one unless I completely remove the screenupdating = FALSE. I've tried various ways of re-activating the original, but despite being able to interact via in the code the new wbk won't actually open until the script finishes even after the screenupdating is theoretically disabled. My solution was to force a screen update with a blank userform which instantly deactivates when loaded. Not the most elegant but works 100% of the time for me.

Dim wbCount As Integer
Application.ScreenUpdating = False

For wbCount = 1 To 2
    Workbooks.Open Filename:=ThisWorkbook.Path & "\Installation Sheet " & wbCount & ".xlsm", UpdateLinks:=False
Next wbCount

Load BlankForm 
BlankForm.Show
ThisWorkBook.Activate
Application.ScreenUpdating = TRUE

-------------------------------------------------
Private Sub UserForm_Activate() 'Instantly deactivate userform on Activation
        Unload BlankForm
End Sub

Upvotes: 1

LeonardoX
LeonardoX

Reputation: 1303

The above solutions didn't work for me, because activate method is fired before second workbook is displayed, so I get active this second workbook always. My workaround is wait a couple of seconds before calling Activate method:

Dim t: t = Timer
While Timer < t + 2 'wait for 2 seconds
    DoEvents
Wend
ThisWorkbook.Activate

Upvotes: 1

QHarr
QHarr

Reputation: 84465

Put your ActiveWorkbook into a variable at the appropriate place and then use that to move back. ThisWorkbook is the one with code in. So be sure if you mean ActiveWorkbook or ThisWorkbook, or if ThisWorkbook is the ActiveWorkbook at the time. In that case, you can simply Set wb = ThisWorkbook

Dim wb As Workbook
Set wb = ActiveWorkbook
'Set wb = ThisWorkbook   '<== If you want the workbook containing the code
'Other code
wb.Activate

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37367

You have to put your workbooks references into variables and then using these variables you have two options: close newly opened workbook, so only original stays opened or use Activate method to bring the original workbook to front.

Sample code:

Sub OpeningWb()
    Dim wb As Workbook, currentWb As Workbook
    Set currentWb = ThisWorkbook
    Set wb = Workbooks.Open("your path here")
    ' if you want to close opened workbook
    'wb.Close
    ' if you want to bring to front original workbook
    currentWb.Activate
End Sub

Alternatively, you can do the following:

Dim wbToDisplay As Workbook
Set wbToDisdplay = Workbooks("workbook name here")
wbToDisplay.Activate

Upvotes: 1

Related Questions