Solved

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

Posted on 2010-08-25
24
1,258 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
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
 

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
 

Author Comment

by:rjthomes
ID: 33528101
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to let jenkins recognize where maven is located 2 266
Scan IP address, obtain info 7 82
Modification on userform and column K 47 37
ASP CDO - Limiting Connections 2 36
Welcome, welcome!  If you are new to the series and haven't been following along, please take a brief moment to review the first three installments: Part 1 (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/A_266-VBScri…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

929 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

11 Experts available now in Live!

Get 1:1 Help Now