Macro connecting MS Excel and MSProject not working

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

Hi TD,

I checked that first off and they are all selected.

Thanks

Mike

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote