Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-08-25
24
Medium Priority
?
1,617 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 10
24 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 33527571
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
ID: 33528038
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
ID: 33528045
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rjthomes
ID: 33528085
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
ID: 33528090
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 33528143
Whoops...change this line:
objProject.Application.FileOpen "CITRUSMASTERSCHEDULE.mpp"

to this
objProject.Application.FileOpen strProjectFile

Rob.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 33528147
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
ID: 33528196
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
ID: 33528211
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
ID: 33528220
Gosh I am tired.  both of the above comments were for my other question.

0
 

Author Comment

by:rjthomes
ID: 33528239
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 33528268
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
ID: 33528301
Ok.  This time it opened with the following error.

Chad
Untitled-15.jpg
0
 

Author Comment

by:rjthomes
ID: 33528310
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
ID: 33528313
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
ID: 33528320
Sure, no worries....I have work to do as well....probably should get to it.... :-)
0
 

Author Comment

by:rjthomes
ID: 33545821
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
ID: 33546887
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
ID: 33548308
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
ID: 33548414
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
ID: 33549410
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 2000 total points
ID: 33556014
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
ID: 33558431
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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Re-planning is just as important as planning. MS Project files need to be updated regularly to reflect the current status of the project and to streamline the upcoming tasks. We have seen a lot of issues where project managers have not updated the p…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

609 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