Activate Open Workbook

My code currently opens any workbook, document, video, etc. from 1 button. When it comes to opening a file that is already open, it gives the following message.

"This File Is Already Open"

I want to convert the code to activate (Display) the non-displayed workbook. In my sample code, I have 2 rows of rem'd X's to show where the problem is, I think.

'xxxxxxxxxxxxxxxxxxxxxxxxxxx

Questionable Code

'xxxxxxxxxxxxxxxxxxxxxxxxxxx

Sub NewExcelWithWorkbook()

    Dim oXL    As Object    'This is needed to open a new instance of Excel.
    'Without it, the file is only opened as a new Window
    Dim OpenFileName    '<-this isn't used
    Dim testFileFind As String
    Dim oWB    As Workbook
    Dim Cl     As Range
    
    'This reads the cell 1 column to the Left so the path & file name can be read
    Set Cl = ActiveCell.Offset(0, -1)

    'The following tests for a blank cell and ends processing
    'It is needed because dir() function will not work with a blank.
    If Len(Trim(Cl)) = 0 Then
        MsgBox "You have not entered a Path and File name."
        End
    End If

    'The following tests for the existance of the file
    testFileFind = Dir(Cl)
    
    'If the file is not found there will be nothing in the variable and processing ends.
    If Len(testFileFind) = 0 Then
        MsgBox "Invalid selection." & Chr(13) & _
               "Filename " & Cl.Value & " not found"
        End
    End If

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

        'THIS TESTS TO SEE IF THE DOCUMENT IS ALREADY OPEN AND IT WORKS
    If FileAlreadyOpen(Cl.Value) = True Then

    
'THE FOLLOWING 2 LINES OF CODE ALSO WORK, GIVING THE MESSAGE BUT DOES NOT DISPLAY THE FILE
        'ActivateWorkbook (FSS)
                  'MsgBox "File is already open"
        

'THE FOLLOWING LINE OF CODE IS WHAT I AM TRYING TO MAKE WORK.  ActivateWorkbook does work.  

    ActivateWorkbook (testFileFind)
'(testFileFind) has the file name that I want displayed.  Example:  mytestfile.xls , BUT DOES NOT DISPLAY FILE
        
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    End
    Else
    End If

    'THIS LINE OF CODE OPENS THE NEW INSTANCE OF EXCEL.
    Set oXL = CreateObject("Excel.Application")

    'THIS LINE OF CODE MAKES THE NEW INSTANCE OF EXCEL VISIBLE.
    oXL.Visible = True

    Set oWB = oXL.Workbooks.Open(Cl)

End Sub

Leith are you around?

Hello Matt,

My guess is the file you are trying to activate has been opened in separate instance of Excel. ActiveWorkbook will only display workbooks open in the current instance of Excel. If memory serves me correctly, I wrote a macro for you to do this. almost a year ago. Here it is again...

'Written: April 18, 2009
'Author:  Leith Ross
'Summary: Searchs all open Windows for the name of the workbook passed to the macro.
'         If found and the workbook is a separate instance of Excel, the macro will
'         activate it.
'         Two sets of variables hold the window handle, process identifier, and
'         thread identifier for the instance of Excel running the macro and for the
'         workbook to be activated.
'
'NOTE:  ALL THIS CODE MUST REMIAN IN THE SAME MODULE.

'API call Declarations
 Private Declare Function EnumWindows _
   Lib "user32.dll" _
     (ByVal lpEnumFunc As Long, _
      ByVal lparam As Long) As Long
     
 Private Declare Function AttachThreadInput _
   Lib "user32.dll" _
     (ByVal idAttach As Long, _
      ByVal idAttachTo As Long, _
      ByVal fAttach As Long) As Long
     
 Public Declare Function SetForegroundWindow _
   Lib "user32.dll" _
    (ByVal hWnd As Long) As Long
   
 Private Declare Function ShowWindow _
   Lib "user32.dll" _
     (ByVal hWnd As Long, _
      ByVal nCmdShow As Long) As Long
    
 Private Declare Function GetWindowThreadProcessId _
   Lib "user32.dll" _
     (ByVal hWnd As Long, _
      ByRef lpdwProcessId As Long) As Long
     
 Private Declare Function GetClassName _
   Lib "user32.dll" _
     Alias "GetClassNameA" _
       (ByVal hWnd As Long, _
        ByVal lpClassName As String, _
        ByVal nMaxCount As Long) As Long

 Private Declare Function GetWindowText _
   Lib "user32.dll" _
     Alias "GetWindowTextA" _
       (ByVal hWnd As Long, _
        ByVal lpString As String, _
        ByVal nMaxCount As Long) As Long

'API Variables for use in this Module Only
 Private hWnd1 As Long, hWnd2 As Long
 Private pid As Long, pid1 As Long, pid2 As Long
 Private tid As Long, tid1 As Long, tid2 As Long
 Private Workbook_Name As String

'VBA call to Activate the Workbook
 Public Sub ActivateWorkbook(ByVal Wkb_Name As String)

   Dim RetVal As Long
  
     Workbook_Name = Wkb_Name
     hWnd = 0: hWnd1 = 0: hWnd2 = 0
     pid = 0: pid1 = 0: pid2 = 0
     tid = 0: tid1 = 0: tid2 = 0
    
       RetVal = EnumWindows(AddressOf EnumWindowProc, 0)
      
         RetVal = AttachThreadInput(tid1, tid2, True)
           RetVal = SetForegroundWindow(hWnd2)
           RetVal = ShowWindow(hWnd2, 3)
         RetVal = AttachThreadInput(tid1, tid2, False)
    
 End Sub

'This function is used by ActivateWorkbook
 Private Function EnumWindowProc(ByVal hWnd As Long, ByVal lparam As Long) As Long

   Dim ClassName As String
   Dim L As Long
   Dim RetVal As Long
   Dim WindowTitle As String
    
         'Get the Window Class name
          ClassName = String(512, Chr$(0))
          L = GetClassName(hWnd, ClassName, 512)
          ClassName = IIf(L > 0, Left(ClassName, L), "")
           'Is this window an Excel Application window?
            If ClassName = "XLMAIN" Then
              'The Process Id is unique to each instance
               tid = GetWindowThreadProcessId(hWnd, pid)
              'Get the window title
               WindowTitle = String(512, Chr$(0))
               L = GetWindowText(hWnd, WindowTitle, 512)
               WindowTitle = IIf(L > 0, Left(WindowTitle, L), "")
                'Is the window title part of ThisWorkbook's title?
                 If InStr(1, WindowTitle, ThisWorkbook.Name, vbTextCompare) > 0 Then
                   hWnd1 = hWnd
                   tid1 = tid
                   pid1 = pid
                 End If
                 If InStr(1, WindowTitle, Workbook_Name, vbTextCompare) > 0 Then
                  'Save the window handle and its process id
                   hWnd2 = hWnd
                   tid2 = tid
                   pid2 = pid
                   EnumWindowProc = False
                 End If
            End If
           
          EnumWindowProc = True
   
 End Function

Macro Example

Sub ActivateTest()
  
    ActivateWorkbook "Book3.xls"
    
End Sub

Thanks Leith . . . Your memory is correct, I am opening separage instances of Excel. I am using the code you gave me for opening all files where I directly open a workbook using the file name. Love it and it works great.

I have this one button though, that you helped me with to open any file, Excel or Word as examples, where I have selected the name of the file in a cell, with the path & file name one cell to the left..

I was hoping that I could modify the existing macro so it didn't have to be totally re-written. I don't think that the code you referenced will work for this application.

Do you need any other information to evaluate this?

P.S. I'll be sending you my eMail program this coming week. Just doing cleanup and decor. My beta test people love it.

Please Bump Up