How to open document from Macro without Activating

Question

I've been working on a macro where at one point I need to open a document, copy and edit some data, and then return to the previous document to continue with the Macro. I have a fileDialog that I run to let the user choose the document, but the problem is that this then activates the document, causing the screen to flash even with ScreenUpdating off. Is there an alternative to Workbooks.Open I can use that won't activate the new document? A setting in Workbooks.Open I can change to prevent it from activating? A way to stop the screen from flashing upon the document activating? Here is the code for the fileDialog and a few lines on each side:

Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
Application.ScreenUpdating = False
With fileDialog
    .InitialFileName = "C:\Users\User\Documents"
    .AllowMultiSelect = False
    .Filters.Clear
    .Title = dialogTitle
    If .Show = False Then
        Application.ScreenUpdating = True
        MsgBox "No file chosen. Click Import Contact List to try again."
        Exit Sub
    End If
    strPathFile = .SelectedItems(1)
End With
Set wbSource = Workbooks.Open(Filename:=strPathFile)

Any solution would be greatly appreciated.

Answer 1

My assumption is that you want to let the user open a file, leave it open, but make your workbook active afterwards so the opened file remains "in the background" for the user to navigate to later on. You've noticed some annoying flicker and came here for answers.

The only way I could reproduce the behavior you describe, with code similar to yours, is when I opened a file that was already opened in the same Excel session (see 3rd use case below). Notice that your code doesn't close the just opened workbook, so the first time you run it, you're in use case 2 below, and the second time you run it, you're in use case 3 below.

If, however, you can close the workbook at the end of your process, you'll be in the 1st use case below and all should be fine.

Let's see if anybody can come up with solutions to use cases 2 and 3.

This first use case typically doesn't introduce flicker:

Application.ScreenUpdating = False
Application.EnableEvents = False 'For good measure.
Set myWb = Application.Workbooks.Open("... path of some workbook that's not already open ...")
'... Do stuff ...
myWb.Close
Application.EnableEvents = True
Application.ScreenUpdating = True

I can't make the other 2 use cases below behave as desired.

Second use case is when the workbook must be left opened at the end of the process described above, but not active, all without any flickering. Whatever I've tried, the opened workbook becomes the active one upon leaving the code:

Application.ScreenUpdating = False
Application.EnableEvents = False 'For good measure.
Set myWb = Application.Workbooks.Open("... path of some workbook that's not already open ...")
'... Do stuff ...
'myWb.Close 'Here, the workbook is left opened.
ThisWorkbook.Activate 'Trying...
Application.EnableEvents = True
Application.ScreenUpdating = True
ThisWorkbook.Activate 'Trying harder...
'Be my guest...
'Note: Application.OnTime eventually calling ThisWorkbook.Activate doesn't count!

Third use case is an oddity and probably what happens to OP. Take the second use case above but open a workbook that's already opened in the same Excel instance. After a flicker even though ScreenUpdating = False during the operations (not cool), the code will leave with ThisWorkbook as the active one (cool!) .

I've tried playing with myWb.Windows(1).Visible = False, DoEvents, you name it, to no avail. Your comments are welcome.

EDIT (3 years later)

A dirty workaround is to open the workbook, then immediately set its IsAddin property to True. This will remove it from Excel's UI and leave the workbook with executing code at the front, no matter what. The caveat is you now have to manage the opened workbook's visibility (e.g. setting IsAddin = False when the user wishes to see it) and lifetime (e.g. closing it when exiting your application's workbook). But it's doable.

Answer 2

You can try

Set wbSource = Workbooks.Open(Filename:=strPathFile)
Workbooks(name of users workbook).Activate

or

Set wbSource = Workbooks.Add(trPathFile)

Hope it helps.

Answer 3

On opening a workbook, you can hide the workbook or activate ThisWorkbook in order no to show the just opened workbook.

Sub OpenAndHide() Dim wbSource As Workbook Dim FileDialog As FileDialog Dim dialogTitle As String Dim strPathFile As String

Set FileDialog = Application.FileDialog(msoFileDialogFilePicker)
dialogTitle = "Open And Hide"

With FileDialog
    .InitialFileName = "C:\Users\User\Documents"
    .AllowMultiSelect = False
    .Filters.Clear
    .Title = dialogTitle
    If .Show = False Then
        Application.ScreenUpdating = True
        MsgBox "No file chosen. Click Import Contact List to try again."
        Exit Sub
    End If
    strPathFile = .SelectedItems(1)
End With
Set wbSource = Workbooks.Open(Filename:=strPathFile)
ThisWorkbook.Activate

'// Hide the workbook
'strPathFile = GetFilenameFromPath(strPathFile)
'Windows(strPathFile).Visible = False

End Sub

Function GetFilenameFromPath(ByVal strPath As String) As String ' Returns the rightmost characters of a string upto but not including the rightmost '\' ' e.g. 'c:\winnt\win.ini' returns 'win.ini'

If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
    GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
End If

End Function