Excel and Project seize

[arShort]

I am trying to get excel to open a MS Project file and then run a project macro on it.

What is happen is that when I run my code it works, and then excels VBA window freezes up and the run arrow goes away, and if I try to open project I just get a chiming noise and it won't open.

For now I have to select the MS Project file from a directory, but in the future I would like it to go through the folder and open all of the file in the folder

EXCEL CODE
Sub ImportMSProject()
Dim FileToOpen
Dim mpApp As MSProject.Application
Dim prjmacro As Object
'Identify the File to Open - START
FileToOpen = Application.GetOpenFilename("Microsoft Project Files (*.mpp), *.mpp")
If FileToOpen = False Then
Exit Sub
End If
'Identify the File to Open - END


'Open the MSProject file - START
Set mpApp = New MSProject.Application
mpApp.Visible = True
AppActivate "Microsoft Project"
mpApp.FileOpen FileToOpen
'Open the MSProject file - END


mpApp.Macro "saveasxlsmac"


mpApp.FileClose pjDoNotSave
mpApp.Quit
Set mpApp = Nothing
AppActivate "Microsoft Excel"


End Sub

PROJECT CODE
Sub saveasxlsmac()
' Macro saveasxlsmac
' Macro Recorded Wed 6/12/13
flnm = ActiveProject
FileSaveAs Name:="C:\Users\Adam\Documents\Grant Engineering\Projects\Landing Pad\Excel Raw\" & flnm & ".xls", FormatID:="MSProject.XLS5", map:="Integration Report Draft 5"


FileExit False


End Sub

 

[Andrew Poulsom]

What's prjApp? I strongly recommend that you put Option Explicit at the top of your module. That will force you to declare all variables.

 

[arShort]

Sorry that should say mpApp, I copied the wrong code. I edited my original post to reflect the correct code

 

[Andrew Poulsom]

Why can't you put your save as code in your Excel procedure, suitably qualifying any properties/methods of course.

 

[arShort]

Because all of the settings for how it maps to excel from project are in project and I don't believe can be used outside of project

 

[Andrew Poulsom]

Sorry, I don't understand. You are automating MS Project so I don't see how settings aren't available. I must say that I don't actually use MS project.

 

[arShort]

What I am doing is exporting the Project file to Excel, which allows you to map it (basically use a template). My mapping setting is map:="Integration Report Draft 5"

 

[Andrew Poulsom]

That's just a string, so why wouldn't your automation object understand it?

 

[arShort]

That is a very good point.

 

[arShort]

Thank you, it works