How to capture MsgBox result and paste into Excel

[Marty]

I have generated a number of VBA modules that count various types of tasks in
a project and display the count total in a Message box. But there doesn't
appear to be a way to copy or cut the number out of the message box. How do
I get the total number out of the message box and pasted into Excel? Or how
do I get the total count some other way in project so I can paste it into
Excel? A sample of my VBA code is below.

Sub Total_Completed_Number_of_Tasks()
Dim T As Task
Dim C As Long

For Each T In ActiveProject.Tasks
If Not (T Is Nothing) Then
If T.Summary = False Then
If T.PercentComplete = 100 Then
C = C + 1
End If
End If
End If
Next T

MsgBox Prompt:="The Project Contains " _
& C & " Completed non-Summary tasks"

End Sub

 

[John]

I have generated a number of VBA modules that count various types of tasks in
a project and display the count total in a Message box. But there doesn't
appear to be a way to copy or cut the number out of the message box. How do
I get the total number out of the message box and pasted into Excel? Or how
do I get the total count some other way in project so I can paste it into
Excel? A sample of my VBA code is below.

Sub Total_Completed_Number_of_Tasks()
Dim T As Task
Dim C As Long

For Each T In ActiveProject.Tasks
If Not (T Is Nothing) Then
If T.Summary = False Then
If T.PercentComplete = 100 Then
C = C + 1
End If
End If
End If
Next T

MsgBox Prompt:="The Project Contains " _
& C & " Completed non-Summary tasks"

End Sub

Marty,
There are basically two choices. Why write the value out in a message
box? Instead, put it into a spare field (e.g. Number1) and then export
the field to Excel using an export map or via a simple copy and paste.
Or, expand the VBA code to write the data directly into Excel.

Hope this helps.
John
Project MVP

 

[Marty]

Thanks John,
Your reply was very helpful. Part of my problem is that I don't know how to
write the data directly into excel. This is exactly what I want to do.
Could you provide me a code sample that I could include in my existing VBA
code?

 

[John]

Thanks John,
Your reply was very helpful. Part of my problem is that I don't know how to
write the data directly into excel. This is exactly what I want to do.
Could you provide me a code sample that I could include in my existing VBA
code?

Marty,
I'll give you two leads.

First for an excellent tutorial on VBA for Project, go to the MVP
website at:
http://project.mvps.org/project/links.htm
At the bottom of the page there is a link to, "Project 98 Visual Basic
Environment Training Materials". Although it says it is for Project 98
it is equally applicable to all current versions of Project. As I recall
(it's been a while since I read it), it has a section on interfacing
with other applications.

Second, for some good VBA code examples, go to fellow MVP, Jack
Dahlgren's website at:
http://masamiki.com/project/macros.htm
Take a look at his macro "Export hierarchy to Excel". It will give you
the basics of how to open Excel from Project and write data.

At some point in the past year I posted some sample code on how to
export Project data to Excel, but in doing a quick search I couldn't
find it. Nonetheless, between the tutorial and Jack's examples, you
should be well on your way to getting what you need. If you have further
questions, we are here to help.

John
Project MVP

 

[Marty]

Thanks John,
I will follow up on the leads you provided. I really appreciate your
patience and assistance. On a related topic, I think it was prior to the
release of Project 98, I was evaluating a beta release of Project for
Microsoft and as part of the process they sent me an internal Microsoft
document on writing macros and/or VBA code. It was roughly 8.5 x 11 with
white or grey heavy stock paper for front and back cover and was bound with
what looked like black electricians tape. This book was packed with not only
tutorials but had tons of sample macros to do lots of different things in
project. I have looked everywhere in my stuff and on the web, but can't find
it. Have you heard of it?

 

[John]

Thanks John,
I will follow up on the leads you provided. I really appreciate your
patience and assistance. On a related topic, I think it was prior to the
release of Project 98, I was evaluating a beta release of Project for
Microsoft and as part of the process they sent me an internal Microsoft
document on writing macros and/or VBA code. It was roughly 8.5 x 11 with
white or grey heavy stock paper for front and back cover and was bound with
what looked like black electricians tape. This book was packed with not only
tutorials but had tons of sample macros to do lots of different things in
project. I have looked everywhere in my stuff and on the web, but can't find
it. Have you heard of it? Project MVP

Marty,
No I've never heard of that document but then I've never been involved
with any beta testing. Maybe some of my cohorts were and will read and
respond to this message, but in order to increase the chances of a
response, I suggest you post a new message specifically about the
document. Nonetheless, the "document" sounds like a relic. If you find
it, I'd think seriously about putting it up for auction on eBay. You'll
probably make a killing

Just for reference there was a major change in VBA between Project 4.x
and Project 98. A massive update was performed on VBA with the release
of Project 98 so if the document is a tutorial on an earlier [VBA]
version, it may have very limited usefulness in today's environment.

John
Project MVP

 

[MaryR]

Just an FYI -
The link for the Project 98 training is http://project.mvps.org/links.htm.

Thanks for the great info!

Thanks John,
I will follow up on the leads you provided. I really appreciate your
patience and assistance. On a related topic, I think it was prior to the
release of Project 98, I was evaluating a beta release of Project for
Microsoft and as part of the process they sent me an internal Microsoft
document on writing macros and/or VBA code. It was roughly 8.5 x 11 with
white or grey heavy stock paper for front and back cover and was bound with
what looked like black electricians tape. This book was packed with not only
tutorials but had tons of sample macros to do lots of different things in
project. I have looked everywhere in my stuff and on the web, but can't find
it. Have you heard of it? Project MVP

Marty,
No I've never heard of that document but then I've never been involved
with any beta testing. Maybe some of my cohorts were and will read and
respond to this message, but in order to increase the chances of a
response, I suggest you post a new message specifically about the
document. Nonetheless, the "document" sounds like a relic. If you find
it, I'd think seriously about putting it up for auction on eBay. You'll
probably make a killing

Just for reference there was a major change in VBA between Project 4.x
and Project 98. A massive update was performed on VBA with the release
of Project 98 so if the document is a tutorial on an earlier [VBA]
version, it may have very limited usefulness in today's environment.

John
Project MVP

 

[Marty]

Hellow again John,
Obviously, I'm not a natural programmer. Your solution is what I want to
do, but I can't figure out how to get the calculation results into the text1
field. What code would I use to replace the message box display with placing
the total number of the calculation into the text1 field?