Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default With VBA from Excel: Open Project, extract resource list and copy it to a worksheet, close project.

Well, I'm stumped again and in need of serious assistance.

I'm building an Excel add-in for creating project plans, which then are
exported to MS-Project for tracking. But I need to have the resource list
in an Excel worksheet before the plan can be built. And that's where I'm in
trouble. The rest of the add-in actually works, including the piece that
exports the plan to MS-Project (2003). But I'm stuck on the part that
automates the retrieval of the resource list from Project.

The code below shows how far I've been able to get on my own. It actually
works, every second time that it runs, and it fails every second time that
it runs. I get a message that the application server doesn't exist or is
not available. But I can't figure out what's going on, and the available
documentation is, well, excessively challenging for me (read that as I don't
know Project's object model and I can't find readable documentation). Can
somebody here straighten me out? I would be entirely grateful

Tony


Sub GetResourceList()
Dim objMSProject As MSProject.Application
Dim R As Resources
Dim curPath As String
Dim fileToOpen As Variant
Dim thisExcel As Excel.Application
Dim Temp As Long
Dim Names As String
Dim rSheet As Worksheet

curPath = CurDir

fileToOpen = Application.GetOpenFilename("Microsoft Project Files
(*.mpp), *.mpp")

If fileToOpen < False Then

Set thisExcel = Excel.Application

Set objMSProject = New MSProject.Application
objMSProject.Visible = True
objMSProject.FileOpen Name:=fileToOpen
objMSProject.Projects(fileToOpen).Activate

Set R = ActiveProject.Resources

For Temp = 1 To R.Count
Names = R(Temp).Name & ", " & Names
Next Temp
Names = Left$(Names, Len(Names) - Len(ListSeparator & " "))

End If

objMSProject.DisplayAlerts = False
'objMSProject.FileCloseAll pjDoNotSave
'FileExit pjDoNotSave
Quit savechanges:=pjDoNotSave
'objMSProject.Quit pjDoNotSave

Set objMSProject = Nothing
thisExcel.Visible = True
ChDir curPath

MsgBox Names

End Sub


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default With VBA from Excel: Open Project, extract resource list and copy it to a worksheet, close project.

My thanks to Scott Button for a pointer to the fix for this. Just in case
anyone here comes across the problem, here's the pointer.

http://www.highdots.com/forums/micro...d-2893172.html

It seems that when making a reference to anything inside Project, from VBA
code external to Project, the reference needs to be "fully qualified."

So, in my own code below,

Dim objMSProject As MSProject.Application
Set objMSProject = New MSProject.Application
...
Set R = ActiveProject.Resources
should read
Set R = objMSProject.Resources

and
Names = Left$(Names, Len(Names) - Len(ListSeparator & " "))
should read
Names = Left$(Names, Len(Names) - Len(objMSProject.ListSeparator
& " "))

Go figure.

Tony


"Tony" wrote in message
...
Well, I'm stumped again and in need of serious assistance.

I'm building an Excel add-in for creating project plans, which then are
exported to MS-Project for tracking. But I need to have the resource list
in an Excel worksheet before the plan can be built. And that's where I'm
in trouble. The rest of the add-in actually works, including the piece
that exports the plan to MS-Project (2003). But I'm stuck on the part
that automates the retrieval of the resource list from Project.

The code below shows how far I've been able to get on my own. It actually
works, every second time that it runs, and it fails every second time that
it runs. I get a message that the application server doesn't exist or is
not available. But I can't figure out what's going on, and the available
documentation is, well, excessively challenging for me (read that as I
don't know Project's object model and I can't find readable
documentation). Can somebody here straighten me out? I would be entirely
grateful

Tony


Sub GetResourceList()
Dim objMSProject As MSProject.Application
Dim R As Resources
Dim curPath As String
Dim fileToOpen As Variant
Dim thisExcel As Excel.Application
Dim Temp As Long
Dim Names As String
Dim rSheet As Worksheet

curPath = CurDir

fileToOpen = Application.GetOpenFilename("Microsoft Project Files
(*.mpp), *.mpp")

If fileToOpen < False Then

Set thisExcel = Excel.Application

Set objMSProject = New MSProject.Application
objMSProject.Visible = True
objMSProject.FileOpen Name:=fileToOpen
objMSProject.Projects(fileToOpen).Activate

Set R = ActiveProject.Resources

For Temp = 1 To R.Count
Names = R(Temp).Name & ", " & Names
Next Temp
Names = Left$(Names, Len(Names) - Len(ListSeparator & " "))

End If

objMSProject.DisplayAlerts = False
'objMSProject.FileCloseAll pjDoNotSave
'FileExit pjDoNotSave
Quit savechanges:=pjDoNotSave
'objMSProject.Quit pjDoNotSave

Set objMSProject = Nothing
thisExcel.Visible = True
ChDir curPath

MsgBox Names

End Sub



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Question on formula of matching Project Workload vs Resource Capac Morocco Mole Excel Worksheet Functions 2 August 6th 09 07:43 PM
VBA Project - Open/Close with Macro Danny Excel Worksheet Functions 2 September 7th 07 11:02 PM
How to convert MS Project to MS Excel. I don't have MS Project. Jane Excel Discussion (Misc queries) 1 February 20th 06 10:01 PM
VBA project password on close mmm9999[_2_] Excel Programming 5 August 24th 05 07:28 AM
Excel macro to extract information from MS project files zouzou Excel Programming 2 August 13th 04 01:03 PM


All times are GMT +1. The time now is 08:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"