Excel VBA – AppActivate works for me but not for others

[Sunjinsak]

Hi,
I hope someone can help me here as this particular problem is starting to drive me INSANE!!

I’m using MS Office 2013 on Windows 7 Enterprise 64-bit

I have code in an Excel workbook which saves an embedded OLE object (a Word document in this case) out to the user’s personal drive (which is a network drive mapped to K:\)

This is part of a much bigger installation routine but I’m having problems with one specific part.

For context, to save the Word document out I use the following code:

Dim sh1 As Shape
Dim objWord As Object 'Word Document
Dim objOLE1 As OLEObject
 
    'The shape holding the object from "Create from file" (Object1 is the name of the shape)
    Set sh1 = Sheets("Objects").Shapes("Object 1")
 
    'Activate the contents of the object
    sh1.OLEFormat.Activate
 
    'The OLE Object contained
    Set objOLE1 = sh1.OLEFormat.Object
 
    Set objWord = objOLE1.Object  
 
    'Save the document out to the specified location
    objWord.SaveAs Filename:="K:\Personnel_Reports " & strReportingYear & "\Report_Template", FileFormat:=0 '.doc format
    objWord.Close False ‘close Word Doc after saving
       
'Release resources
Set objWord = Nothing
Set objOLE1 = Nothing

I then use…

AppActivate ThisWorkbook.Name

…to switch focus back to Excel so the user can see the progress of the rest of the installation routine. This is necessary so they know when the process has been completed. This is the bit I’m having problems with!

Without using AppActivate Word keeps focus once its part in the installation routine has completed. It is essential that focus is passed back to Excel however.

The title of the Workbook the installation routine runs from should theoretically always be the same but just in case it isn’t I chose to use ThisWorkbook.Name to feed into the AppActivate Title parameter. That way it should always switch focus back to the correct Excel workbook no matter what its title may have been changed to.

This works absolutely fine for me. It switches focus back to the correct Excel workbook every time – even if I have others open and even if I arbitrarily rename the workbook before running the installation routine.

Unfortunately once I send the workbook out by email it throws the following exception for anybody else who tries to run the installer:

“5 Invalid procedure call or argument”

I tried a few variations in my code, such as:

AppActivate ThisWorkbook.FullName
AppActivate ActiveWorkbook.Name
AppActivate ActiveWorkbook.FullName
and…
AppActivate “Title” (where Title is the actual current title of the workbook exactly as displayed in Excel’s title pane – just for testing purposes)

All of these work absolutely fine for me but not for anyone else once I send them the workbook.

This is a corporate network environment and we are all using exactly the same versions of Windows 7 and MS Office with exactly the same updates, patches, plug-ins, hotfixes etc etc. Some user settings may be different though, obviously.

I have made sure each user I have tested this with has no other instances of Excel or Word open before running the installer but it doesn’t seem to make a difference.

Why is it working fine for me and not for anyone else? If it can find a matching workbook title for me by using ThisWorkbook.Name (or any of the other variations I tried above) as the Title parameter why can’t it do so for anybody else? I just don’t get it!!!

MSDN’s AppActivate entry hasn’t really shed any light for me and google has not been much help either so far.

There are a few threads on this forum I found that involve people having problems using AppActivate in various situations but none seem to be quite match the issue I'm facing. Forgive me if I've overlooked something though.

Any ideas?

Thanks.

 

[Sunjinsak]

I guess from the lack of replies that this is as bewildering to you guys as it is to me!

Anyway, by way of an update and for anyone else who might be suffering similar problems using AppActivate I’ve found a different way to achieve the same functionality using the Windows API:

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
   
Public Declare Function ShowWindow& Lib "user32" _
    (ByVal hwnd As Long, ByVal nCmdShow As Long)
 
Public Declare Function SetForegroundWindow Lib "user32" _
    (ByVal hwnd As Long) As Long


Sub ShowXL()
 
Dim h As Long
h = FindWindow("xlMain", vbNullString) 'Grab Excel Handle
If CBool(h) Then
    Call ShowWindow(h, 3) 'Maximize App
    Call SetForegroundWindow(h) 'Focus on App
End If
 
End Sub

Instead of using AppActivate ThisWorkbook.Name in my code I now just call ShowXL and voila - focus switching that works for all and sundry!

I can’t remember where I got the above code from – it may well have been somewhere on this forum – but if you recognise it as yours let me know so I can credit (and thank) you.

I adapted it to work with Word as well:

Sub ShowWD()
 
Dim h As Long
h = FindWindow("OpusApp", vbNullString) 'Grab Word Handle
If CBool(h) Then
    Call ShowWindow(h, 3) 'Maximize App
    Call SetForegroundWindow(h) 'Focus on App
End If
 
End Sub

I hope this helps someone like it did me.

If anyone knows the answer to (or has any theories or information on) my original question though please feel free to respond – I’d still love to know what the problem with using AppActivate is/was and how to fix it if possible.

Thanks.

 

[mcarv]

Whether or not it works depends on if the user has windows setup to show file extensions or not. If they have the default setup of hiding known file extensions it will not work. Thisworkbook.name always returns with the extension included.

I wrote this to sub to call in order to make it compatible with both setups.

Sub ActivateThisWB()


    Dim WBName As String
    
    WBName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5) ' 5 is length of .xlsm if .xls change to 4
    
    On Error Resume Next
        appactivate ThisWorkbook.Name, False
        appactivate WBName, False
    On Error GoTo 0 'return to default error handling


End Sub

Then just "Call ActivateThisWB()" instead of AppActivate ThisWorkbook.name


I just ran into this problem today - thought I would reply with my fix as well!