Macro won't execute on first run, must do it twice

[Lucas Soler]

Excel won't open on my first attempt (nothing appears to happen). On my
second attempt, opens OK. Code to follow. Any ideas why Excel won't
initiate at first attempt?

Sub AAASMI_Time_Collection_v1()

Set Proj = ActiveProject

ViewApply Name:="SMI - WBS Structure"
'ViewApply Name:="Resource Usage"

Set xlApp = GetObject(, "Excel.Application") 'Look for running copy

If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application") 'Start new instance
If xlApp Is Nothing Then
MsgBox "Can't Find Excel, please try again.", vbCritical
End 'Stop, can't proceed without Excel
End If
xlApp.Visible = True
Else
Set xlR = Nothing
Set xlApp = Nothing
Set xlBook = Nothing
Set xlApp = CreateObject("Excel.Application") ' Start New Instance
If xlApp Is Nothing Then
MsgBox "Can't Find Excel, please try again.", vbCritical
End 'Stop, can't proceed without Excel
End If
xlApp.Visible = True
End If

Application.ActivateMicrosoftApp pjMicrosoftExcel

On Error Resume Next

 

[JackD]

It appears to be failing on the GetObject call and exiting.
I'm not sure why it works the second time.

The following article discusses this issue and recommends AGAINST using
GetObject.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q288902

Here is a snippet: "In general, Microsoft recommends that you use a new
instance of an Office application instead of attaching to an instance that
the user may be using. It is best create an instance by using the
Application ProgID, and then open or create new objects from there."

This document discusses how to handle looking for an open instance of excel.

http://support.microsoft.com/kb/258511/EN-US/

Personally I don't bother doing this and just create a new instance.

-----------------------------------------------

The following code appears to work for me. You might want to start with it.


Sub AAASMI_Time_Collection_v1()
Dim xlApp As Excel.Application
Set Proj = ActiveProject

If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application") 'Start new instance
If xlApp Is Nothing Then
MsgBox "Can't Find Excel, please try again.", vbCritical
End 'Stop, can't proceed without Excel
End If

Else
Set xlR = Nothing
Set xlApp = Nothing
Set xlBook = Nothing
Set xlApp = CreateObject("Excel.Application") ' Start New Instance
If xlApp Is Nothing Then
MsgBox "Can't Find Excel, please try again.", vbCritical
End 'Stop, can't proceed without Excel
End If

End If
xlApp.Visible = True
xlApp.Workbooks.Add
AppActivate ("Microsoft Excel")

End Sub