none
MS Project 2013 VBA Macro - AppActivate "Microsoft Excel" - Runtime Error 5 Invalid Procedural Call or Argument RRS feed

  • Question

  • Hi,

    I have some VBA macros that I have used in MS Proje ct 2003 and 2007 that don't work in MS Project 2013. The macros are getting the Runtime error 5 message at this line:

       AppActivate "Microsoft Excel"

    Excel 2013 attempts to open just before the macro hangs. I verified the string argument for the AppActivate statement is correct by opening Excel 2013 and running the following VBA macro

         MsgBox "The name of the active application is " & ActiveWindow.Application

    I've tried disabbling the "Start Screen" in Excel 2013 using these commands:

    • Click the File tab and choose Options.
    • Choose General in the left pane.
    • In the Start Up Options section, uncheck the Show The Start Screen When This application Starts option.
    • Click OK.

    The start screen no longer shows if I open Excel 2013 manually, but the macros still stop at the same line.

    Any ideas why this statement isn't working for MS Project 2013?

    Thanks



    Monday, January 6, 2014 9:06 PM

Answers

  • I have found a solution. WhenExcel 2007 is launched the title bar opens as "Book1 - Microsoft Excel". When Excel 2013 is launched the title bar opens as "Book 1 - Excel". I changed:

             AppActivate "Microsoft Excel"

    to

               AppActivate "Excel"


    and now the macros work with MS Project 2013.  I hope this helps someone ese.
    Tuesday, January 7, 2014 4:29 PM

All replies

  • I have found a solution. WhenExcel 2007 is launched the title bar opens as "Book1 - Microsoft Excel". When Excel 2013 is launched the title bar opens as "Book 1 - Excel". I changed:

             AppActivate "Microsoft Excel"

    to

               AppActivate "Excel"


    and now the macros work with MS Project 2013.  I hope this helps someone ese.
    Tuesday, January 7, 2014 4:29 PM
  • yep, worked for me as well... thanks very much for posting!
    Wednesday, January 15, 2014 10:04 PM
  • I am using AppActivate in Excel VBA, and have discovered the same workaround.

    It works well when running on my machine using Excel 2013, however if the same workbook is used by another user who has an earlier version of Excel, they will experience the error.

    I've discovered a workaround for that using the following code:

    On Error Resume Next
    AppActivate "Microsoft Excel"
    AppActivate "Excel"

    which works for the most part in my workbooks, excel now I'm facing the following problem:

    If I have to do any other error catching in addition to this, it causes this code to break.  Here is what I mean:

    On Error goto skipSomeCode
    ..
    'some code that might have an error depending on certain conditions in the workbook.
    ..
    'code that I only want to run if there was no error
    skipSomeCode:
    'continue with the code I want run regardless of whether or not there was an error
    On Error Resume Next
    AppActivate "Microsoft Excel"
    AppActivate "Excel"

    When I do that, the original error catching does the job nicely, but when the macro attempts to switch the focus back to Excel, it throws an uncaught error and halts the macro.  It appears as though the error on the one Excel AppActivate call is going back to the 'skipsomecode' label, and not paying attention to resume next.

    Monday, May 26, 2014 2:43 PM
  • wonderful, it worked for me
    Sunday, May 3, 2015 4:31 PM
  • I am using AppActivate in Excel VBA, and have discovered the same workaround.

    It works well when running on my machine using Excel 2013, however if the same workbook is used by another user who has an earlier version of Excel, they will experience the error.

    I've discovered a workaround for that using the following code:

    On Error Resume Next
    AppActivate "Microsoft Excel"
    AppActivate "Excel"

    which works for the most part in my workbooks, excel now I'm facing the following problem:

    If I have to do any other error catching in addition to this, it causes this code to break.  Here is what I mean:

    On Error goto skipSomeCode
    ..
    'some code that might have an error depending on certain conditions in the workbook.
    ..
    'code that I only want to run if there was no error
    skipSomeCode:
    'continue with the code I want run regardless of whether or not there was an error
    On Error Resume Next
    AppActivate "Microsoft Excel"
    AppActivate "Excel"

    When I do that, the original error catching does the job nicely, but when the macro attempts to switch the focus back to Excel, it throws an uncaught error and halts the macro.  It appears as though the error on the one Excel AppActivate call is going back to the 'skipsomecode' label, and not paying attention to resume next.


    Read tons of forum posts on this and didn't find joy until...I realized that the caption on my application was "Project Professional".... Something to keep in mind: Office version matters.... Nothing worked until I used AppActivate "Project Professional"
    Wednesday, August 26, 2015 11:31 PM
  • Thank you so much!!
    Friday, November 20, 2015 11:48 PM
  • If you set up a string variable and capture the caption it will also work

    Dim strAppCap as string

    strAppCap=Application.Caption

    ,..'whatever loses focus

    ..

    AppActivate strAppCap

    All good.  Thanks

    • Proposed as answer by ct2651 Monday, August 28, 2017 6:02 PM
    Monday, September 12, 2016 12:16 AM
  • It also seems to work using this for both Office 2010 and Office 2016:

    Dim appExcel As Excel.Application

    ... open the worksheet with appExcel, etc., then finally

    AppActivate appExcel.Caption

    Friday, December 9, 2016 4:27 PM
  • If you set up a string variable and capture the caption it will also work

    Dim strAppCap as string

    strAppCap=Application.Caption

    ,..'whatever loses focus

    ..

    AppActivate strAppCap

    All good.  Thanks


    This caan work if the first answer wasn't working for some weird reason, (I got a file like that...)

    My alert for questions

    Monday, August 28, 2017 6:03 PM
  • Thank You for the solution. It worked for me with word merge in office 2013.
    Friday, July 13, 2018 3:52 PM