Controlling Microsoft Project from Excel

[kms]

I am trying to get Microsoft Project to import from Excel into a blank
project and then have it save as a Project File with the same name as the
excel file.

I recorded a macro within project so the mapping information would be
accurate, but I am having two problems.

First, the line beginning with "FileOpenEx" is erroring out with "runtime
error 1101 arguement value is not valid" when this code is copied into an
excel module to be integrated with the rest of the code. I'm thinking it's
the "fileopenex" portion that is possibly different than in excel. I have
the object library selected within tools. This works when it is run within
Microsoft Project and the excel file is open. I just don't know how to
reverse it so Excel is telling Project to do all of this.

The second problem I am having is that the line beginning with
..activedocument.saveas" is erroring that is it an invalid or unqualified
reference. This line is supposed to save the project file with the same name
and path as the excel file that is being copied.

I can't find documentation on this anywhere and it was my stab at it. Any
help would be greatly appreciated. Below is the code:

Public Sub Import()
Dim ProjectApp As Object
Dim SaveAsName As String
'This is declared at the top... added for the sake of this example and is
successfully
'referenced elsewhere...
Global wbmain As Workbook

SaveAsName = ThisWorkbook.Path & ThisWorkbook.Name

Application.ActivateMicrosoftApp (xlMicrosoftProject)
Set ProjectApp = MSProject.Application

ProjectApp.Visible = True

With ProjectApp

MapEdit Name:="Map 1", Create:=True, OverwriteExisting:=True,
DataCategory:=0, CategoryEnabled:=True, TableName:="Task_Table",
FieldName:="Name", ExternalFieldName:="Name", ExportFilter:="All Tasks",
ImportMethod:=0, HeaderRow:=True, AssignmentData:=False,
TextDelimiter:=Chr$(9), TextFileOrigin:=0, UseHtmlTemplate:=False,
IncludeImage:=False
MapEdit Name:="Map 1", DataCategory:=0, FieldName:="Duration",
ExternalFieldName:="Duration"
MapEdit Name:="Map 1", DataCategory:=0, FieldName:="Outline Level",
ExternalFieldName:="Outline Level"
MapEdit Name:="Map 1", DataCategory:=0, FieldName:="Notes",
ExternalFieldName:="Notes"
MapEdit Name:="Map 1", DataCategory:=1, CategoryEnabled:=True,
TableName:="Resource_Table", FieldName:="ID", ExternalFieldName:="ID",
ExportFilter:="All Resources", ImportMethod:=0
MapEdit Name:="Map 1", DataCategory:=1, FieldName:="Name",
ExternalFieldName:="Name"
MapEdit Name:="Map 1", DataCategory:=1, FieldName:="Initials",
ExternalFieldName:="Initials"
MapEdit Name:="Map 1", DataCategory:=1, FieldName:="Type",
ExternalFieldName:="Type"
MapEdit Name:="Map 1", DataCategory:=1, FieldName:="Max Units",
ExternalFieldName:="Max Units"
MapEdit Name:="Map 1", DataCategory:=1, FieldName:="Standard Rate",
ExternalFieldName:="Standard Rate"
MapEdit Name:="Map 1", DataCategory:=1, FieldName:="Cost Per Use",
ExternalFieldName:="Cost Per Use"
MapEdit Name:="Map 1", DataCategory:=1, FieldName:="Notes",
ExternalFieldName:="Notes"
MapEdit Name:="Map 1", DataCategory:=2, CategoryEnabled:=True,
TableName:="Assignment_Table", FieldName:="Task Name",
ExternalFieldName:="Task Name", ImportMethod:=0
MapEdit Name:="Map 1", DataCategory:=2, FieldName:="Resource Name",
ExternalFieldName:="Resource Name"
MapEdit Name:="Map 1", DataCategory:=2, FieldName:="% Work Complete",
ExternalFieldName:="% Work Complete"
MapEdit Name:="Map 1", DataCategory:=2, FieldName:="Work",
ExternalFieldName:="Work"
MapEdit Name:="Map 1", DataCategory:=2, FieldName:="Units",
ExternalFieldName:="Units"
FileOpenEx Name:=wbmain, ReadOnly:=True, Merge:=0,
FormatID:="MSProject.XLS5", Map:="Map 1"


End With
.ActiveDocument.SaveAs Filename:=SaveAsName

End Sub

 

[Bob Bridges]

I think I see the second problem. You're setting SaveAsName =
ThisWorkbook.Path & ThisWorkbook.Name, but you need a "\" between the two:
Path doesn't include the final backslash and of course Name doesn't start
with one.

I don't work with VBA/Project so I don't know what MapEdit or FileOpenEx do,
but I am puzzled about one thing: Within a With block, don't you have to
start references to child objects with a period? Shouldn't all those
statements read ".MapEdit...", ".FileOpenEx..." etc? If not, I don't think I
can help you with the first problem.