Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro connecting MS Excel and MSProject not working

    Hi

    I have a macro which is in MSExcel and opens MS Project, reading a project plan. This worked using earlier version of Excel and MSproject.

    I am now using Excel 2007 and MSProject 2007.

    The error is when I action the objPJApp.FileOpen (strFile2) command, strFile2 is a variable which contains the location of the MS Project File.

    What happens is the macro sits there for a period of time and the comes up with the error "Microsoft Office Excel is waiting for another application to complete an OLE action"

    Has there been a change in the was Apps work in macros

    Any pointers greatfully recieved

    Mike

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Just a guess

    Open the VBA Editor
    On the VBA Editor Menu Bar Select: Tools Then References
    A dialog box should open, make sure that MS Project 7 (or whtever is listed as an Available Reference for MS Project 7) is checked.
    Without this Box being checked Excel cannot control MS Project 7.

    Hope this helps.

    TD

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi TD,

    I checked that first off and they are all selected.

    Thanks

    Mike

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Assuming there is nothing wrong with the path location of the file (is it a UNC network path?? is it a path which includes an unmapped drive on the PC you are using??) - have you included a statement in the VBA code to turn off alerts in Excel before executing the fileopen command i.e. Application.DisplayAlerts = False

    zeddy

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Try the following VBA code to see if MS Project 7 is being recognized. Note for the example I am assuming that the term "Project" is the name of Project 7
    Sub OpenProj()
    Dim MyApp as Project.Application
    Set MyApp = New Project.Application
    MyApp.Visible = True
    End Sub

    If the code fails I would beleive that VBA is not finding MS Project object library.

    If the code runs, then I would delete the"MyApp.Visible = True" and run this code prior to opening the project file.
    Also remeber to include the VBA code "MyApp.Quit" to terminate MS Porject.
    Hope this helps.

    TD

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    I can remember seeing this message a year or so ago and it puzzled me to.
    I believe this was a performance related issue on a specific PC connected to a network.
    The particular PC was used as a 'hot-desk' with lots of different Users logging on to the machine over a long period.
    The PC kept certain 'profiles' of Users on the machine, and using this PC was like swimming through treacle.

    So, are you using a networked PC?
    If so, have you tried copying the relevant files onto the local drive to test whether this is a network related issue?
    If it all runs OK by switching all the relevant files to a test folder on the local C: drive, then get IT to 'clean up' and remove unneccessary User profiles from the dodgy PC.

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •