Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Microsoft Project from Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Microsoft Project from Excel
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. --- "kms" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Microsoft Project Export to Excel | Excel Discussion (Misc queries) | |||
Microsoft Visual Basic- compile error: can't find project or libra | Excel Programming | |||
MISSING: Microsoft Word 11.0 Project Library | Excel Programming | |||
Creating Status Report like Microsoft Project | Excel Programming | |||
How do I import data from Microsoft Project 2003 to Excel? | Excel Discussion (Misc queries) |