Checking for 2nd instance of Excel

[Molby]

I'm calling a website (not google) from Excel using the below:

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate("[URL unfurl="true"]WWW.google.com")[/URL]

The website automatically generates a spreadsheet in a second instance of Excel. This is all hard coded on the website and cannot be changed. The length of time it takes to generate varies, so I need to check when the second instance has been created so that I can then carry on with my code.
I've tried counting the number of workbooks, but it only checks in the active instance, not in the new instance. Anyone got any ideas?

 

[PHV]

Have you tried to play with the GetObject function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Molby]

Hi PH,

Yeah, I've tried, but I can't get it to count the number of instances. See the code below that doesn't work

bob$ = GetObject(, "Excel.application").Count

 

[Danp129]

This is old code I pulled out of macro I hadn't used in a long time. Should work for you I believe.

'References: Microsoft Internet Controls, Microsoft HTML Object Library
    Dim oWindow As SHDocVw.InternetExplorer
    Dim objShellWins As SHDocVw.ShellWindows
    Dim i%
    Set objShellWins = New SHDocVw.ShellWindows

    For Each oWindow In objShellWins
        With oWindow
                Set objDoc = .document
                If (TypeOf objDoc Is HTMLDocument) Then
                    i = i + 1
                End If
        End With
    Next
    MsgBox i & " IE window(s) are running"

 

[Molby]

Danp129,

It's not the number of IEs I'm after, it's the number of Excels. Can your code be modified to search for them instead?

 

[Danp129]

Check out KB article 288902. It didn't work for me but it may work for you =\. If it doesn't work for you, there may be other options, what versions of Windows will this macro be used on?

 

[Molby]

I'm using Excel 2002 on XP.

 

[Danp129]

Did you try what was suggested in the Microsoft KB article? It didn't work for me but I'm using Excel 2000 / Win2k.

 

[Molby]

I've just checked, but it doen't help. I don't know the name of the file that website creates, so I can't search for it that way. It also says:

If multiple instances of Microsoft Excel are running, GetObject attaches to the instance that is launched first.

I need the 2nd instance.

 

[Danp129]

Yes that's the correct article, did you keep reading?

"You can attach to a specific instance if you know the name of an open document in that instance. For example, if an instance of Excel is running with an open workbook named Book2, the following code attaches successfully to that instance even if it is not the earliest instance that was launched:"

Set xlApp = GetObject("Book2").Application

If you know what the workbook name is gonna be then that is *supposed* to work, just replace "Book2" with the name of the workbook.

 

[Molby]

As I said earlier:

I don't know the name of the file that website creates, so I can't search for it that way.

 

[Danp129]

Ok, I have some code that when filtered to only return windows of the MS-SDIa class returned the following:

class, hWnd, Window Text
MS-SDIa, 223065, maint_log.xls
MS-SDIa, 6228468, Book1
MS-SDIa, 1575264, charmap.xls
MS-SDIa, 2492878, Book2

Now, we could get your count from this, but what do you plan to do after you get your count? We put the "Window Text" into an array and set the count variable before opening the website. Then, we can keep checking on the count of windows and once it has increased, we can loop through them again and figure out what new window was created by comparing the current open windows to the array we made. After it's singled down, I don't know how you are going to reference it unless the code I posted from the KB article works for you. If it does work, let me know and I'll modify/post the code you need.

 

[Molby]

If we can figure the new name of the window, then that would be perfect. What I need to do once the workbook has been created is to save it off. I then carry on running the code in my first instance of Excel.

 

[Danp129]

Let me know if you have problems.. The code for your sub is in a sub called myroutine at the end of the code.

'--------------------------------------------------------------
' Copyright ©1996-2002 VBnet, Randy Birch, All Rights Reserved.
' Terms of use [URL unfurl="true"]http://www.mvps.org/vbnet/terms/pages/terms.htm[/URL]
'--------------------------------------------------------------
'Extremely minor mods by danp129

Public sWinTitles$
Public Const MAX_PATH = 260
Public Const LB_SETTABSTOPS As Long = &H192

Public Declare Function EnumWindows Lib "user32" _
   (ByVal lpEnumFunc As Long, _
    ByVal lParam As Long) As Long

Public Declare Function GetClassName Lib "user32" _
    Alias "GetClassNameA" _
   (ByVal hWnd As Long, _
    ByVal lpClassName As String, _
    ByVal nMaxCount As Long) As Long

Public Declare Function GetWindowText Lib "user32" _
    Alias "GetWindowTextA" _
   (ByVal hWnd As Long, _
    ByVal lpString As String, _
    ByVal cch As Long) As Long

Public Declare Function GetWindowTextLength Lib "user32" _
    Alias "GetWindowTextLengthA" (ByVal hWnd As Long) As Long

Public Declare Function SendMessage Lib "user32" _
    Alias "SendMessageA" _
    (ByVal hWnd As Long, _
    ByVal wMsg As Long, _
    ByVal wParam As Long, _
    lParam As Any) As Long


Public Function EnumWindowProc(ByVal hWnd As Long, _
                               ByVal lParam As Long) As Long
   
  'working vars
   Dim nSize As Long
   Dim sTitle As String
   Dim sClass As String
   Dim pos As Integer
   
  'set up the strings to receive the class and
  'window text. You could use GetWindowTextLength,
  'but I'll cheat and use MAX_PATH instead.
   sTitle = Space$(MAX_PATH)
   sClass = Space$(MAX_PATH)
   
   Call GetClassName(hWnd, sClass, MAX_PATH)
   Call GetWindowText(hWnd, sTitle, MAX_PATH)

  If (InStr(1, sClass, "XLMAIN", vbTextCompare) <> 0 Or _
    InStr(1, sClass, "MS-SDIa", vbTextCompare) <> 0) And _
    Trim(Replace(TrimNull(sTitle), "Microsoft Excel", "")) <> Empty Then

  'strip the trailing chr$(0)'s from the strings
  'returned above and add the window data to the list
   If sWinTitles <> Empty Then sWinTitles = sWinTitles & ":"
   sWinTitles = sWinTitles & Replace(TrimNull(sTitle), "Microsoft Excel - ", "")

  End If
  
  
  'to continue enumeration, we must return True
  '(in C that's 1).  If we wanted to stop (perhaps
  'using if this as a specialized FindWindow method,
  'comparing a known class and title against the
  'returned values, and a match was found, we'd need
  'to return False (0) to stop enumeration. When 1 is
  'returned, enumeration continues until there are no
  'more windows left.
   EnumWindowProc = 1

End Function


Private Function TrimNull(item As String)

  'remove string before the terminating null(s)
   Dim pos As Integer
   
   pos = InStr(item, Chr$(0))
   
   If pos Then
         TrimNull = Left$(item, pos - 1)
   Else: TrimNull = item
   End If
   
End Function
'''''''''''''''''''''''''' Code for your sub ''''''''''''''''''
Private Sub myroutine()
    Dim arTmp
    Dim arOrigWin
    Dim iCount&
    Dim iNewWinCount&
    Dim boMatch As Boolean
    Dim sNewWins$
    Dim xlApp As Excel.Application
    'enumerate the windows passing the AddressOf the
    'callback function.  This example doesn't use the
    'lParam member.
    Call EnumWindows(AddressOf EnumWindowProc, &H0)
    
    arOrigWin = Split(sWinTitles, ":")
    arTmp = Split(sWinTitles, ":")
    iCount = UBound(arTmp)

    Do Until UBound(arTmp) > iCount
        'put your own wait statement here if needed
        DoEvents
        
        sWinTitles$ = Empty
        Call EnumWindows(AddressOf EnumWindowProc, &H0)
        If sWinTitles <> Empty Then
            arTmp = Split(sWinTitles, ":")
        Else
            MsgBox "There are no instances of Excel detected", vbCritical
            GoTo Quit
        End If
    Loop
    MsgBox "Window count has increased from " & iCount + 1 & " to " & UBound(arTmp) + 1
    Stop

    For i = LBound(arTmp) To UBound(arTmp)
        boMatch = False
        For o = LBound(arOrigWin) To UBound(arOrigWin)
            If arOrigWin(o) = arTmp(i) Then
                boMatch = True
                Exit For 'o
            End If
        Next 'o
        If boMatch = False Then
            'Window in question was not open before
            iNewWinCount = iNewWinCount + 1
            If sNewWins <> Empty Then sNewWins = sNewWins & ":"
            sNewWins = sNewWins & arTmp(i)
            MsgBox "New file found: " & arTmp(i)  'remark after debugging
        End If
nexti:
    Next 'i
    
    Stop
        If iNewWinCount = 1 Then
            'This is where we want to be
            Set xlApp = GetObject(sNewWins).Application
            'If no error, you can now refer to the new instance
            'of excel as xlapp
        ElseIf iNewWinCount > 1 Then
            'Hopefully this doesn't happen but if it does
            'find more then 1 new window since last checked,
            'put your code for handling this situation here.
            'The new window names are available in sNewWins
            'and are colon delimited.
        Else
            'No new windows?
        End If

Quit:
    sWinTitles = Empty
    Erase arTmp
    Erase arOrigWin
    
End Sub

 

[Danp129]

Oh, I forgot to comment where to put what... Put this right before opening the new sheet:

    Call EnumWindows(AddressOf EnumWindowProc, &H0)
    
    arOrigWin = Split(sWinTitles, ":")
    arTmp = Split(sWinTitles, ":")
    iCount = UBound(arTmp)

And this starts after you've started opening the new sheet:

   Do Until UBound(arTmp) > iCount
'etc.. copy to the end

 

[Molby]

I'm out for the next few days, but I will try it next week and get back to you.