Solved

script to open an ms project file and run a specific macro and then close

Posted on 2010-08-25
24
1,224 Views
Last Modified: 2013-11-15
Can someone help me with a script (to be run in a .bat file) that will open a specific ms project file and then run a specific macro and then close?
0
Comment
Question by:rjthomes
  • 14
  • 10
24 Comments
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
Hi, I'm not too sure about Project, but this approach works with Excel, so fingers crossed!

Rob.
strProjectFile = "C:\MyDocs\MyProject.mpp"

strMacroName = "MyMacro"

Set objProject = CreateObject("MSProject.Project")

objProject.Application.FileOpen "My Project.mpp"

objProject.Application.ActiveProject.Visible = True

objProject.Application.Run strMacroName

objProject.Application.ActiveProject.Close False

objProject.Quit

Open in new window

0
 

Author Comment

by:rjthomes
Comment Utility
Rob,

Should this work in a .bat file?

I noticed that you worked on both of both of my questions.  

Thank you,

Chad
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
No, this is a VBS file, but you could call the VBS from a batch file by using
wscript.exe C:\MyScript.vbs

Regards,

Rob.
0
 

Author Comment

by:rjthomes
Comment Utility
ok.

First try.

see below.

Thanks,
 
Chad
strProjectFile = "C:\MyDocs\CITRUSMASTERSCHEDULE.mpp"

strMacroName = "PrintFilteredResources"

Set objProject = CreateObject("MSProject.Project")

objProject.Application.FileOpen "CITRUSMASTERSCHEDULE.mpp"

objProject.Application.ActiveProject.Visible = True

objProject.Application.Run strMacroName

objProject.Application.ActiveProject.Close False

objProject.Quit

Open in new window

Untitled-10.jpg
0
 

Author Comment

by:rjthomes
Comment Utility
ok.

First try.

see below.

Thanks,
 
Chad
strProjectFile = "C:\MyDocs\CITRUSMASTERSCHEDULE.mpp"

strMacroName = "PrintFilteredResources"

Set objProject = CreateObject("MSProject.Project")

objProject.Application.FileOpen "CITRUSMASTERSCHEDULE.mpp"

objProject.Application.ActiveProject.Visible = True

objProject.Application.Run strMacroName

objProject.Application.ActiveProject.Close False

objProject.Quit

Open in new window

Untitled-10.jpg
0
 

Author Comment

by:rjthomes
Comment Utility
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
Whoops...change this line:
objProject.Application.FileOpen "CITRUSMASTERSCHEDULE.mpp"

to this
objProject.Application.FileOpen strProjectFile

Rob.
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
Unfortunately I don't use Exchange...so I can't help you with any Outlook automation....it's not my thing... :-)

Rob.
0
 

Author Comment

by:rjthomes
Comment Utility
Rob,

No problem on the Exchange thing.  Certainly over my head.

Here is what is happening now.  Take a look at my email settings.  I think its right.

Because we use exchange from behind a vpn this is the only server setting I can find.

Chad
            ' generate and save seperate .pdf files for each resource thanks to RobSampson

                strFullName = ActiveProject.FullName

                strBaseName = Mid(strFullName, InStrRev(strFullName, "\") + 1)

                strBaseName = Left(strBaseName, InStrRev(strBaseName, ".") - 1)

                Set objShell = CreateObject("WScript.Shell")

                strMyDocs = objShell.RegRead("HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders\Personal")

                If Right(strMyDocs, 1) <> "\" Then strMyDocs = strMyDocs & "\"

                strSource = strMyDocs & strBaseName & ".pdf"

                strTarget = "C:\MyDocs\" & strBaseName & "_" & Resource.Name & ".pdf"

                FilePrintSetup "Adobe PDF"

                FilePrint FromDate:=Start, ToDate:=Finish

                Sleep 3000

            ' this is where I would like to email the pdf to each resource

                FileCopy strSource, strTarget

                ' Email variables:

                strServer = "rjtexchange1.RJTHomes.local"

                strTo = "chad@rjthomes.com"

                strFrom = "chad@rjthomes.com"

                strSubject = "Subject Here"

                strBody = "Please find attached a copy of your resource schedule:" & vbCrLf



                SendEmail strServer, strTo, strFrom, strSubject, strBody, strTarget



      End If

NextResource:

    Next Resource

    FilterApply Name:="All Tasks"       ' apply the filter

End Sub



Public Sub SendEmail(strServer, strTo, strFrom, strSubject, strBody, strAttachment)

        Dim objMessage

        

        Set objMessage = CreateObject("CDO.Message")

        objMessage.To = strTo

        objMessage.From = strFrom

        objMessage.Subject = strSubject

        objMessage.TextBody = strBody

        If strAttachment <> "" Then objMessage.AddAttachment strAttachment

        '==This section provides the configuration information for the remote SMTP server.

        objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

        'Name or IP of Remote SMTP Server

        objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strServer

        'Server port (typically 25)

        objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

        objMessage.Configuration.Fields.Update

        '==End remote SMTP server configuration section==

 

        objMessage.Send

        Set objMessage = Nothing

End Sub

Open in new window

Untitled-11.jpg
0
 

Author Comment

by:rjthomes
Comment Utility
Oops.  The above error was when I used mail.rjthomes.com as the server.  That was stupid.

Here is the correct error for the above code.

Thanks

Chad
Untitled-12.jpg
0
 

Author Comment

by:rjthomes
Comment Utility
Gosh I am tired.  both of the above comments were for my other question.

0
 

Author Comment

by:rjthomes
Comment Utility
Ok.  Back to business

here is the error with this code.

Thanks

Chad
strProjectFile = "C:\MyDocs\CITRUSMASTERSCHEDULE.mpp"

strMacroName = "PrintFilteredResources"

Set objProject = CreateObject("MSProject.Project")

objProject.Application.FileOpen strProjectFile

objProject.Application.ActiveProject.Visible = True

objProject.Application.Run strMacroName

objProject.Application.ActiveProject.Close False

objProject.Quit

Open in new window

Untitled-13.jpg
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
OK, sorry again, change this line:
objProject.Application.ActiveProject.Visible = True

to this
objProject.Application.Visible = True

If Project did actually open the file, but didn't make Project visible, you might have the msproj.exe (I think that's what it is) stuck in the Task Manager process list.  Kill that first, then try again.

Regards,

Rob.
0
 

Author Comment

by:rjthomes
Comment Utility
Ok.  This time it opened with the following error.

Chad
Untitled-15.jpg
0
 

Author Comment

by:rjthomes
Comment Utility
Rob,

As much as I am enjoying the attention I have to get some rest.  Going on a short trip tomorrow.

Will be back Friday to bug you again.

Chad
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
OK, that means the macro would have run if it got to that stage....good...progress....

Now, we just need to get Project to close....If you remove this line
objProject.Application.ActiveProject.Close False

does it close gracefully?

Rob.
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
Sure, no worries....I have work to do as well....probably should get to it.... :-)
0
 

Author Comment

by:rjthomes
Comment Utility
Rob,

Ok.  I am running the script below.  It does open the correct file but does not run the macro.  Frankly it just sits there.

I think it is a naming issue.  Take a look at the images and tell me if I am getting it right.

Chad
strProjectFile = "C:\MyDocs\CITRUSMASTERSCHEDULE.mpp"

strMacroName = "PrintFilteredResources"

Set objProject = CreateObject("MSProject.Project")

objProject.Application.FileOpen strProjectFile

objProject.Application.Visible = True

objProject.Application.Run strMacroName

Open in new window

Untitled-1.jpg
Untitled-2.jpg
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
OK, so try setting
strMacroName = "CITRUSMASTERSCHEDULE.mpp!PrintFilteredResources"

and / or try making the sub Public by adding Public infront of Sub PrintFilteredResources()

Regards,

Rob.
0
 

Author Comment

by:rjthomes
Comment Utility
Rob,

No luck.  I have tried the following.

strMacroName = "CITRUSMASTERSCHEDULE.mpp!PrintFilteredResources"
strMacroName = "PrintFilteredResources"
strMacroName = "CITRUSMASTERSCHEDULE.mpp!Module5.PrintFilteredResources"
strMacroName = "PrintFilteredResources()"
Public / Private / Neither

It acts like it isn't even trying.  Wouldn't I get an error if it didn't find the right name?
Once it opens the file, it just sits there.

Bummer.

Chad
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
Hmmm, I'll have to look into the documentation when I get a chance....maybe also try
objProject.Run strMacroName

instead....without the .Application

Rob.
0
 

Author Comment

by:rjthomes
Comment Utility
Rob,

Here it is.

I wish I  could give some input.

Chad
strProjectFile = "C:\MyDocs\CITRUSMASTERSCHEDULE.mpp"

strMacroName = "CITRUSMASTERSCHEDULE.mpp!PrintFilteredResources"

Set objProject = CreateObject("MSProject.Project")

objProject.Application.FileOpen strProjectFile

objProject.Application.Visible = True

objProject.Run strMacroName

Open in new window

Untitled-16.jpg
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
Comment Utility
OK, I got somewhere!  I installed Project 2000, and tested it out.  It really is different to any other Office automation object....which sucks!  But anyway....this should work:

strProjectFile = "C:\MyDocs\CITRUSMASTERSCHEDULE.mpp"
strMacroName = "PrintFilteredResources"
Set objProject = CreateObject("MSProject.Project")
objProject.Application.FileOpen strProjectFile
objProject.Application.Visible = True
objProject.Application.Macro strMacroName
objProject.Application.Quit 0

Regards,

Rob.
0
 

Author Comment

by:rjthomes
Comment Utility
Rob,

That was it!!!!!!!  Seems like ms project is some sort of step child for Microsoft.

Anyway.  I really appreaciate it.  No way I could have got that done.

Chad
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now