Please find below a transcription of the audio portion of Ira Brown’s session, Exploring VBA: Microsoft Project’s Macro Language Part III, being provided by MPUG for the convenience of our members. You may wish to use this transcript for the purposes of self-paced learning, searching for specific information, and/or performing a quick review of webinar content. There may be exclusions, such as those steps included in product demonstrations. You may watch the recording of this webinar at your convenience.
Melanie: Welcome, everyone. We are going to start our session today. It’s Melanie here with team MPUG. Welcome again to VBA. Today we are doing part three, Integration with Other Applications, which is something all of our MPUGers are always looking for. I’d like to welcome back our VBA widget and solution expert, Ira Brown, to continue this excellent series. If you missed part one or part two, I’ll chat out the link to the recordings, and I’ll also send it after in an email. You can also find these sessions and 600 other hours of training and courses on demand on mpug.com anytime. If you have questions today, please type these into the questions chat window, and we will hold those to the end to present to Ira. We’ve gotten some great questions from last two sessions, so I’m looking forward to that. Again, today’s session is part three of the three part series. If you missed one and two, they’re available on demand on mpug.com, just go to our homepage and click on on demand videos and I will send those out to you as well. I am now going to hand the presentation over to Ira.
Ira Brown: Thank you very much, Melanie. I’d like to welcome everyone to part three of our series called Exploring VBA: The Microsoft Project Macro Language. So thank you all for attending today’s session and hopefully many of you attended the previous two sessions as well. As Melanie mentioned, they are available on the MPUG website, if you want to get a refresher or if you didn’t have a chance to attend. This will be the final part of the series and this is where it all comes together and we have probably the most fun. That is, we’re going to take a look at how we can integrate Microsoft Project with other office applications. What if you wanted to create some solution to create an automated status report in Microsoft Word by pulling in tasks from your project, or what if you wanted to create some PowerPoint presentation with the timeline in your project? So all kinds of interesting examples we’ll look at together, and I think that you’ll find these to be really useful.
Ira Brown: Just a quick reminder about Project widgets. We are a Microsoft Project gold partner specializing in project and portfolio management. In fact, that’s really all we do. Everything we do is based on Microsoft Project. We specialize in creating Microsoft Project custom solutions, which frequently turn into our widgets. As you probably learned from the previous two sessions, that even something like recording a macro can serve as the foundation for a new widget or something much more sophisticated than just what that macro recording provided. So with that, let’s get into today’s session.
Ira Brown: Okay. So in today’s session where we’re going to be integrating Microsoft Project with other office applications, the first thing we’re going to take a look at is how do we go about exporting our project to Microsoft Excel? Then we’ll take a look at how we can integrate Project with Microsoft Word by creating an automated status report. And then finally, we’ll see how we’re able to integrate Microsoft Project with PowerPoint where we could take our timeline that we have in Microsoft Project and actually create a PowerPoint presentation that includes our project timeline.
Ira Brown: So just as a run reminder, we’re going to be giving away two free copies of our Driving Path Widget at the end of today’s session. Just like we’ve been doing in all of these other sessions, today we’re going to give away two more copies. That’s absolutely free. All you have to do is send me an email at the end of the session, and then you’ll be entered into that drawing. Then also, all of the macros that we’re going to be creating during today’s session, I will make available to you, including the presentation. If you just shoot me an email, I’ll give you that information at the end of the presentation and I’ll be happy to send you everything that we did today.
Ira Brown: Okay. So just as a bit of a refresher, we talked about this during the first session about why would you want to create a macro? What’s the benefit? The idea would be that it’s really helpful if you have activities you perform in Microsoft Project that tend to be time consuming, error prone and repetitive, those are perfect candidates for creating a macro. You create the macro, very often you can just record the macro and maybe with a little bit of editing, you can get exactly what you want. You can even assign that macro to a button in the ribbon in Microsoft Project so that when you want to run it, you just click the button. You can share your macros with other people within your organization. Especially if you’re running Project Online, you have the ability to put your macros in what’s referred to as the enterprise global, and then they become available for all users of Project Online.
Ira Brown: Today we’re going to be focused on standalone Microsoft Project. That’s what I’ll be using, but everything that we’re talking about today can also be done in Project Server, as well as Project Online and basically any version that you’re running. So the first example, we’re going to build ourselves a widget, and the first example is exporting a project to Microsoft Excel. I guess the question I always like to ask is, well, what’s the business reason why you might want to do this? Well, frequently, I hear from a lot of my clients that not everybody has Microsoft Project on their computer or not everyone has access to the Project web app or maybe people just don’t, or they’re not as comfortable using Project. Maybe you want to be able to share your schedule, but you want to give someone an Excel copy of it just to be able to view the schedule. That’s why you might want to be able to do something like this.
Ira Brown: You remember how we said that, well, the first thing is in order to build any macro or automation, you’d have to say, how would I accomplish this manually if I had to do it manually? You need to understand that first and then you can automate it. The way we would go about doing that is, and I’m going to demonstrate it for you right now, I’m going to go ahead and start off by opening up one of the templates that comes with Microsoft Project, this software development plan. So we’ll just create that. I’ll just close that up a little bit there, and then we’ll just expand out all the tasks. So we go to View, Outline, show all sub-tasks.
Ira Brown: Suppose I wanted to be able to provide someone with a copy of what we’re looking at right now, basically this schedule, but I want to be able to give it to them in an Excel file? Now, the Excel file is not going to display the Gantt bars. Although I should say that I have come up with some techniques to emulate a Gantt chart in Microsoft Excel. Because if you think of maybe a time scale as a bunch of columns and then maybe you can shade some of the cells. So you could emulate what a Gantt chart looks like, but that’s not what we’re going to try to do today.
Ira Brown: I just want to keep it pretty basic. We want to be able to take this project and we want to be able to export it to Excel. I’m going to walk you through the steps to do, but as I walk you through the steps, I am going to first turn on the macro recorder so that everything that I’m doing is being captured by the macro recorder. Remember how we do that? We click on the View tab here, then we go to Macros, drop that down and we say record macro. I’m just going to call this, ExportToExcelDemo. So notice how I’m just putting all those words together, no spaces. I’m capitalizing the first letter of each word there just to make it more readable. Now I click Okay. Our macro recorder is now running.
Ira Brown: So how do we go about what’s the first step in exporting this to Excel? Well, it turns out there is a built-in feature in Microsoft Project that lets you export your project to Excel. We’re going to take a look at that right now. We’re going to click on File, Save as, I’m going to go ahead and browse and then I’ll go to the folder where I’d like to be able to save this. I’m just going to go to my C drive and I have this data folder here. Notice where it says Save as type. Well, by default, it wants to save this as an MPP file. However, if I drop that down, notice there’s lots of other options here. Some of these you may not have seen before, and I think it might be worth your while to check out some of the other formats that are available. But we’re going to pick this one right here called Excel Workbook. I’m going to go ahead and give this a name, I’ll just go ahead and call this Export Project MPUG, and then we’ll click Save.
Ira Brown: When I do that, it knows I want to save this as an Excel file. So it brings up what’s called the export wizard. This is a built-in feature in Microsoft Project. So I’m going to go ahead and click the next button, and this is going to step me through a series of steps for exporting my project to Excel. It says, what is the format of the data? I’m just going to go ahead and say, I just want to export the selected data, meaning the data that’s in my project, click Next. Then it says, do you want to create a new map or use an existing map? A map is something we can create in Microsoft Project that if I want to be able to do this on an ongoing basis, I can utilize this map over and over again. Essentially the map app says, which fields would you like to be able to export from Microsoft Project to? In this case Excel.
Ira Brown: All right. We’re just going to create a brand new map, so the default here is fine. I’ll click Next. Then it says, what type of data do you want to export? In our case, we just want to export tasks. So I’ll check that off. And then it goes on to say, if you’re going to Excel, do you want to also include the headers? In other words, the names of the columns, the names of the field. Yes I do. So we’ll keep that checked, and I’ll just go ahead and click Next again. All right. So this screen here says, okay, well tell me a little bit more about which fields you’d like to be able to export. This is where I could go through and basically just pick the fields one by one. Just by hitting my dropdown here, I see a whole list of fields and I can pick the ones I want. But there’s actually an easier way to do this.
Ira Brown: Remember, when you’re in Microsoft Project and when you’re looking at the Gantt chart view as we were a moment ago, well, that structure that you see on the left side, that tabular structure is actually referred to as a table in Microsoft Project. In this case, it happens to be called the entry table. So when you’re creating a map, instead of having to specify each individual field one at a time, I can just say base on table. I’ll click that button, and then it says, which table would you like to base the map on? So I’m going to pick the table called Entry. That’s just a list of all the tables. I’ll pick the one I want and then click Okay. Notice what happens, it brings in all of the columns that are defined as part of that table. That really will save you a bunch of time when you’re doing this. Because that’s exactly what I want.
Ira Brown: Now, there are a couple of additional columns that I want to add to this table and there’s also a couple that I actually don’t need. I’m not going to bring over the indicators column since that doesn’t do me any good in Excel. That’s only, it’s that graphical indicator that you have in Project. That won’t come over the way we want it to. So I’m going to just hit my delete key here and that’ll get rid of that one. Also, task mode that tells me if a task is auto scheduled or manually scheduled. I don’t really need that one. So I’m going to delete that one. But I do want to bring over name, duration, start, finish, predecessors, resource names. I’m going to add two more fields here at the end. I want to bring over whether or not this is a summary task.
Ira Brown: There’s a field in Microsoft Project called Summary and it’s really just a yes/no field that’s set automatically. If a task is a summary task, then Summary gets set to yes. And if it’s not a summary task, then the field called Summary gets set to no. So I want to bring that over and I’ll show you why in just a minute. I want to bring over one more field and that field is going to be called Outline Level. Notice if I just type in the first letter and then drop it down, it takes me to the Os and there’s that field called Outline Level that I want. Here’s a little preview of what the data will look like. Shows you the first couple of rows just to make sure that, yeah, that looks pretty much like what I’d expect. Now I’m going to go ahead and click Next, and it asks me, do I want to save my map for future use? In this particular case I don’t, so I won’t save the map. Now I’ll just go ahead and click Finish.
Ira Brown: All right. Hopefully if that worked correctly and did what it was supposed to do, I would’ve just created an Excel file. Now I need to just go to the location where I told it to create the Excel file. So I’m just going to go to my data folder here on the C drive, and there’s the file I just created called Export Project MPUG. I’m going to go ahead and open that up, and there it is. That’s exactly what I asked for almost. All the data’s there, but it doesn’t look all that great. Does it? What I would like to be able to do is I’d like to make this look a little bit more presentable. I want it to look a little bit closer to the way Microsoft Project looks. I want to be able to preserve some of that formatting that we have in Microsoft Project.
Ira Brown: Looking at this, well, what are the things that are wrong with this? Well, for one, the column widths are not what they need to be. We need to increase some of the column widths. How would we do that in Excel? Well, I have a pretty good idea of how to do that, but before I do any of these things, I’m going turn on the macro recorder in Excel. Remember how we recorded macros in Project? Well, we can record macros in Excel and it’s works the same way. What I’m going to do is I’m going to click the View tab. Sure enough, there’s that Macros button again. We’ll drop it down and we’ll say record macro, and we’ll give it a name. So how about if we call this something like Format Excel MPUG. Click Okay, and now the macro recorder is going.
Ira Brown: The first thing I want to do is I want to adjust the column widths here so that everything is as big as it needs to be. So how would you do that manually in Excel? Remember the macro’s recording. So anything I do manually, it should pick up. I’m going to do that by clicking on this little corner button right here to select the entire worksheet. Then I’ll just double click right on top of one of the borders of a column. When I do that, it does what’s called a best fit. Notice how that every column now has been adjusted based upon the data that’s in that column. So it’s already looking much better. What else might I want to do? Maybe I want to bold the column headers. I can do that by selecting row one and then just clicking the Bold button. Right now. We’ve made that look a little bit better.
Ira Brown: Notice there’s not really any hierarchy here. Everything is just this flat list. I’d like to try to deal with that in some way. Also, my summary tasks are not bold. But remember I said how we’re going to bring over the field called Summary and we’re going to bring over the field called Outline Level? Well, we brought those over because they are going to help us right now, do what we want to be able to do with our macro. But again, let’s see how we would do it manually. Essentially, any place where summary equals yes, I want to make the task bold. So let’s just do that manually for now. Let’s just click on Software Development. I’ll just focus on the task name column, and then I’ll just click the Bold button again. That looks pretty good.
Ira Brown: Now, this scope, this is also a summary task. So I’ll make that bold. However, in Microsoft Project, that is actually indented underneath the Software Development, which is the project summary test, that top level, row zero. I’d like to indent that. Well, again, Excel has the ability to indent. Notice right there, there’s a little indent button. I’m just going to go ahead and click that, and notice that it just indented. Now the next task here is a sub-task that doesn’t need to be bold. It’s not going to be a summary, because summary equals no here, but you see how the outline level says two. Well, that outline level two tells me how many times do I need to indent the task to give it that appearance that we want? Well, in this case, it’s twice. So I’m going to hit the indent button twice. One, two.
Ira Brown: You see how we’re now starting to have more of that outline look that we want. So I would imagine that if I were to go through every single task in Excel, and if my logic said something like, well, if I’m on a summary task, let’s make it bold. Then based upon this outline level, whatever that number is there, that’s how many times I want to indent the task name. That’s the approach that I think ought to accomplish this. So, I’m not going to take it any farther in terms of the macro recording, I’m going to stop the recorder and let’s take a look at the code, that macro code that was generated in Excel. Basically, everything I’ve done so far has been captured, hopefully. So let’s click on View and then go back to Macros and then say stop record.
Ira Brown: Now we want to take a look at the macro code. Remember how we do that? We click on Macros again. It’s a little bit different in Excel than it was in Project. I don’t have the ability to go directly to the Visual Basic Editor here, but if I choose View Macros, here I can see the macro that I just recorded. And now if I click Edit, that will take me to the Visual Basic Editor. Here it is. I’ll maximize that screen. You can see right here, this is the macro that was just created. Now, let’s see if we can analyze some of this a little bit and see what it’s doing. Some of these lines here, we probably don’t really need. So it looks to me like this cells select, I may not need that one. So I’m probably just going to go ahead and delete it. Now, how do I know that?
Ira Brown: Well, you’ll get more familiar with what can be deleted as you work with this over time. But remember, I was clicking around in Excel and every time I clicked in the worksheet somewhere, it generated that line of code that said cell select. So in this case, I’m going to get rid of it. This line right here that says cells.entirecolumn.autofit, well, what that is going to do, that is the line of code that actually did that best fit that made everything fit the way it was supposed to. In other words, adjusted all the column widths. That looks like it’s pretty important. Then notice, it says rows one, column one select. Remember I selected the first row. And then once I selected the first row, it says, now take that selection and let’s make the font bold. That’s what this line of code here does. Selection.font.bold equals true. Make that line bold.
Ira Brown: Remember how we found that helpful last time when we put some comments in? Let me do that here also. Remember, I’ll just put an apostrophe character in and I’ll just say, best fit all columns. Notice it makes that green text. That’s just the way you can comment this to make it easier to read, to make it more understandable, especially when you’re looking at it down the road. This is going to say something like, make the first row bold. Now the next line here says, select cell B2. So B2 was that name column. Let me just drag this over a little bit so you can see it. When I clicked on Software Development, that was me selecting B2. And then what happened after I selected that cell? Well, again, selection.font.bold equals true. That made that cell bold. Then didn’t I do that again for B3? So it looks like that is the technique, where I can basically just select what I want. Then if I want to make it bold, this line here makes it bold. Okay.
Ira Brown: Now let’s take a look at this next line. Selection.insertindent with a one. Well, remember how I indented this scope and I hit the indent button one time? Well, that’s the line of code that got generated. Then I went down to B4. Remember how I indented that twice? Well, it’s interesting what the macro recorder gave us. It actually gave us indent once, indent once again. But it turns out, once you dig a little deeper into the macro language, that this one here is actually what is referred to as an argument. What that means in this case, then I’ll just backspace over, hit my space key. Well, actually it doesn’t… Nevermind. But once you get into it, if you actually look at the documentation, you’ll discover that that one there actually represents the number of times that you want to indent. So instead of having to repeat this line twice, I could just change that to a two. That tells it to indent twice.
Ira Brown: Well, isn’t that two really, can I get that number from the field called Outline Level? Because that’s tells me how many times that I want to press the Indent button. So let me just go back to VBA. It’s just here at the bottom again. All right. So I think I have… Oops, sorry. I’m in Microsoft Project. I need to be in Excel. I think I have a pretty good framework for what I want to be able to do here. So now what I would like to do is, using the code that I recorded as the framework, let’s see if we can make that a little more sophisticated to get the automation to work exactly the way we need to. Just in the interest of time here, I’ve actually already created that routine. I’m just going to copy it, and I am going to paste it on in here, getting rid of the one we did before. This is actually what we want. I’ve put some other logic in here.
Ira Brown: I’m going to walk you through this right now, just to help you understand exactly how this works. Actually, before I do that, I guess it would make sense, because I’ve already made a lot of changes here. I’m wondering if it’s going to let me undo these. Let’s see. I might be able to get away with, say undo, basically everything that we’ve done. There we go. Hey, I got lucky. All right, so this is how it’s started. When we first opened Excel, this is what we saw. Let’s take a look at this routine here now and let’s see if we can figure out what each line is going to do here. I think it will seem quite logical to you as we go through it. So remember, whenever you create one of these macros, remember it’s also referred to as a subroutine, it starts off with the word sub and then you give it the name that you want.
Ira Brown: In this case, we can call it whatever we want. I’m going to call this Format Excel. Now, these here, these are referred to as our variables. I have a few variables I had to create. In fact, all three of them are integer variables. Just whole numbers and [inaudible 00:27:48] just as a way of declaring those variables. Then this is the name of the variable, Int Row. So since it’s an integer, I started off with that prefix, INT. Int Indent Levels is another one I created, and then Int Last Row. Okay. So let’s step through the logic here. The first line here where I have a comment, it says, get the last row in Excel. So why do I want to get the last row? Well, I want to be able to essentially loop through this Excel file here row by row.
Ira Brown: How do I know how many rows there are? Well, I can scroll down and I can see in this case, there’s 88 rows altogether. But I need to be able to figure that out within the macro language. So it turns out this is the line that will tell us that. Active workbook, that’s referring to the Excel workbook, .worksheet one. Well, worksheet one is this particular worksheet. That’s the one we’re referring to. And then there’s this object referred to as used range. In other words, like in Excel have thousands of rows, but most of them are blank. What is the used range? What are the used rows? Well, that’s what used range tells us, .rows.count. This is basically going to tell me how many rows are actually being used in Excel. When I get that number, I’m going to assign it to that Int Last Row variable. So now I know how many rows I have.
Ira Brown: This section right here selects the first row where we have our column headers. So rows one, one select. While it’s selected, let’s make it bold. We already saw that one. I added one more line here, not only do I want to bold it, but I’d like to actually increase the font size a little bit just on that first row. So selection.font.size equals 12. This is where we can do a best fit for the entire worksheet. It looks like I actually did need that line here, cells.select. So that probably wasn’t a good idea that I deleted that before, but it’s back again. That selects the entire worksheet and then does that best fit. So it makes all the columns as wide as they need to be.
Ira Brown: You can see how these comments are really helpful. Now, notice the next thing we’re going to say. Loop through each row in order to bold the summary tasks and to indent the sub-tasks. So this is what is referred to as a loop, specifically a four next loop. It is a way of iterating through each row within the Excel spreadsheet, one row at a time. And while you’re processing that row, let’s do some things to it. Well, for example, starting with row two, it’s going to say, let’s determine the number of times we’re going to need to actually click that Indent button. Well, notice what this says, int indent levels, that’s that variable is going to be equal to the cells, meaning looking at particular cell.
Ira Brown: Here’s the particular cell to look at. Well, row says, remember, that’s the row, that’s the particular row that I’m processing. In this case, it would be equal to two when we’re processing row two. And then this nine here represents the column that I want. So what is in row two column nine? Well, let’s go take a look. There’s row two, and let’s just count our way over one, two, three, four, five, six, seven, eight, nine. Notice it’s the outline level. That’ll tell us how many times we need to indent. Then the next line says that if that number is greater than zero, because remember, the first row here which has a zero in it, we can’t indent zero times. That doesn’t make any sense. So we have to have this little extra check here.
Ira Brown: I’ll put a comment in that if the int indent levels is greater than zero, then indent the name of the task. That’s what this is doing here. If that is greater than zero, what does it say to do? Well, again, in this case it would be row two. And then in column two, well, that’s where we have the name. Perform that indent command, and then how many times should it do it? Well, int indent levels tells us how many times the indent button should be pressed essentially, just like when we do this manually. Then this section right here says that if the column called summary, remember we brought over summary, if that equals yes, then make the name column bold. We basically repeat that for every single row in Excel. Then when we’re done, it just says, select the beginning cells, one, one, that’s basically the first row, first column, select it and then we’re done.
Ira Brown: What I’m going to do here is I am going to do what’s referred to as stepping the code. Meaning that I can run this one line at a time. Just you can see it through its thing. So I do that by pressing the F8 function key, and notice it starts working its way through. I’m going to run that line of code right there. And if I hold my mouse over int last row, see how it shows me 88. That’s how many rows there are. Let’s keep going. We’re going to select the first row. Notice we can see on the left, exactly what’s happening. Let’s make it bold. There we go. Let’s increase the font size, made it a little bit bigger.
Ira Brown: Let’s select the entire worksheet and let’s do that best fit. There we go. Now we’re going to loop through each row in Excel. We get the indent level. In this case, it’s zero. So if it’s zero, remember we don’t want to actually do any indent. That worked the way it was supposed to. And now if that summary is equal to yes, let’s make it bold. So watch what happens now in row two in that task name column, see how we just made it bold. Now, go back and do the next row. Work our way through. Now, watch it indent scope. See what just happened? Should we be bolding that also? I think, yes, let’s bold it. So it’s really doing exactly what we want.
Ira Brown: This is pretty cool. Now we don’t have to do it one line at a time. When you actually are ready to run the whole thing, we can just press the F5 function key or press this button right here, which is the same thing. That’s what I’m going to do and watch what happens. We’re done. So notice it just made the formatting in Excel exactly the way we wanted it to look. So doesn’t this look a whole lot closer to what your Microsoft Project schedule look like? If someone wanted to look at your schedule in Excel, well, this is a pretty good thing to hand to them. It has that nice format to it. So you can see that we have this macro language in Excel, and it works very much the same way, except instead of working with tasks and resources and things like that, you’re working with rows and columns when you’re in Excel, but the basic techniques are the same.
Ira Brown: All right, let’s take this one step further. So I’m just going to go ahead and close this. Let’s go back to Microsoft Project and it gets even better. In Microsoft Project, I have created a macro and I’m going to share this with you. Let’s go to the macro, the Visual Basic Editor. Remember we click on macros visual basic, and I will show you here in this MPUG part three. I created this routine called Export to Excel, but notice that everything is now running from within Microsoft Project. What I’m able to do is I can take that exact Excel macro that I created in Excel. I can copy and paste that Excel code, that VBA code from Excel into Microsoft Project. I can actually do all of that Excel formatting in Microsoft Project. Essentially Microsoft Project is controlling, is performing those commands on Microsoft Excel. And it all could be in one consolidated macro.
Ira Brown: Let’s take a look at it real quick here. Notice this says export to Excel. Well, remember when we created that map. Remember, this part of the code was created in Microsoft Project, where we told Microsoft Project which fields do we want to be able to send over to Excel. Here’s the line of code here where it says file save as. I’ll just put a comment there, save Excel file. So all of that happens on the project side. Now this is where the Excel portion of it kicks in. This line of code right here that says create object, “Excel.application,” that is the technique for programmatically starting the Excel application just as if you opened it manually. This is how we can programmatically start Microsoft Excel. Notice that we have this line that says set OBJ Excel equal to that.
Ira Brown: Well, OBJ Excel is what’s referred to as an object. In this particular case, the object is the Excel application and I’ve named it OBJ Excel. That’s how I can programmatically do these things in Excel from Microsoft Project. So let’s just take a look at what some of these things are doing. Well, this is how we can make Excel visible, because it is possible to do everything that we just did, but Excel doesn’t even have to be visible to the user. But in this case, I would like it to be visible. So we say .visible equals true. This is where we open the Excel file. Now, once we have an open, this code here should look very familiar to you because this is the exact code that we just reviewed over on the Excel side. The only difference is that every line had to be proceeded with OBJ Excel.
Ira Brown: Remember how we selected the row and we made the font bold and we made it 12 points? Well, over on the Excel side, it just said, selection.font.bold. I had to put here OBJ Excel.selection, OBJ Excel. Every line of that VBA code has to have the prefix OBJ Excel. When you do that, it knows that that command runs against Excel rather than Microsoft Project. But other than that, it’s the exact logic that we just reviewed. Notice how this is called Export to Excel? Let’s remember that name. But what I did previously was I assigned that to a ribbon button. So let me go ahead and close this. There’s our ribbon that we created last time, MPUG, and you know what, maybe I did not assign it to the ribbon button.
Ira Brown: Well, that’s okay. Let me do it right now. I’m going to right click, customize the ribbon. Remember, this is just a review from last time. This is exactly what we did last time. I’m going to choose Macros here, Export to Excel. That’s the one that I want. I want to add that to my list of widgets here. Remember that widgets group. So I’m going to click Add, and let me right click on Export to Excel. I’ll rename it, give it a little bit of a friendlier name. Maybe I’ll call it Export Project to Excel. And I give it a nice button. Like maybe that guy right there, click Okay, click Okay. And there it is, Export Project to Excel. Just want to make sure Excel is not running. It does not appear to be. Let’s give this a try.
Ira Brown: I’m going to click that button right here and let’s see what happens. So what would we expect to happen? Well, the first part of the code is it’s going to take the project, save it to Excel, then start up Excel and then do all that really cool formatting. So in one click of a button, I’m hoping that we get everything that we need. Let’s give it a try. Let’s click the button. Here we go. Starts up Excel, does all my formatting. Done. How about that? That was pretty easy. That’s a perfect use for [inaudible 00:41:45]. Anytime somebody ever says to you, “Hey, can you give me a copy of your project in Excel?” You can just say, “Oh, sure, no problem.” You can click your button here and you send them the Excel file. It’s easy as that. I really like this example because I think you’d be able to put it to good use.
Ira Brown: So, we have some time left here. Let’s take a look at a couple of other examples. This time we’re going to see how we can do something with Microsoft Word and how we can also do some automation with PowerPoint. The first Microsoft Word example we’re going to take a look at is we are going to create an automated status report. I’m going to go ahead and open up this project here called Status Report Widget Demo. By the way, again, I’m going to be sending this to you. All you have to do is just send me an email and I’ll send you everything that we’re doing here today. Let me go to VBA for a minute, click on View, Macros, Visual Basic, and on top here, I’m going to just select the macro called Show Status Report Widget.
Ira Brown: When I run the line Show Status Report Widget, it actually says, okay, show me the form. So this is actually something that we do within a form. Just like we did last time, this is a little bit more sophisticated than the one we did last time. So we’re going to be able to run this form and the user will be able to supply a date range for the report. So we want to start our report on this date and the report on this date. I can put in an overall status, red, green, or yellow. I can specify who the project manager is. And then I click Okay, and that actually runs all this code right here. So we’re not going to go through every single line here, but just generally, what we’re doing is we’re going to launch Microsoft Word programmatically.
Ira Brown: This is how we do it. One of the things I just want to point out is that another technique that you can use to make it even easier to create this VBA code is if you go to the Tools menu, once you’re in VBA, and choose References. Notice how I can specify a reference to Excel or Word or PowerPoint. They’re all listed here alphabetically until I check them. Then it moves them to the top. Well, when you create that reference, it makes it even easier to write the code because it’ll give you suggestions as you’re creating your code. I just want to mention that. In this case it says, start Microsoft Word, make it visible, open up, we’re going to start off with a Word template that I’m going to show you in a minute here. That’s going to be the template that we’ll use to build the status report.
Ira Brown: We’re going to utilize this technique in Word. If you’re familiar with bookmarks in Word, bookmarks are really handy to go to a particular location in a Word document. Let me go ahead, before I keep going here in the code, let me show you what that template looks like. So I’m going to go to my data folder here, and I’m going to open up this project, WidgetsStatusReport.doc. This is essentially what the format of the status report is going to look like. It’s going to have several different sections in it, like who is the project manager? Sorry, who’s the project manager? What’s the name of the project? What’s the reporting period? What is the overall status of our project? And then we have a section for key tasks and milestones, tasks completed this tracking period, tasks that were scheduled to be completed but did not.
Ira Brown: We could programmatically produce this status report by taking information from Microsoft Project and actually just writing it to this format that we have right here. One of the techniques that we use, how does it know where to write the data to? Well, in Microsoft Word, we have something called a bookmark. If I click on Insert and then bookmark, notice I have several bookmarks already listed here. So for example, see where it says project manager, if I were to click, Go To, watch what happens to my cursor in the Word document. I’ll click Go To and let me just click Close here. My cursor is now positioned right next to project manager. I use these different bookmarks strategically. So I say, before you write the key tasks and milestones to this table in Word, position yourself right there, and then write in the name, write the duration, write the start, write the finish, and write the percent complete.
Ira Brown: Then to go to the next row, I have to send another tab command that will add an additional row here, and then I write the next task and the next milestone, et cetera. I do that for each section. So we go back to project. That’s what all of this code is doing, and it’s all documented for you. It goes to the bookmark that we specify, and then it does this thing called OBJWord.selection.typetext. And it’s as if you typed the text, but in this case, it’s the project name or it is the duration or start date or finish date or percent complete. Get the idea? So this will produce our status report for us. Let’s give it a try. Let me close this code. Let me close the Microsoft Word template. I am now going to click the Status Report Widget button, and there’s our form.
Ira Brown: Let’s put in the start date. In this particular case, I want my start date to be the beginning of this week. That would be 9/27. Let’s put that in there, 9/27/2021. The report finish date, I’ll use 10/3 for that, 10/3/2021. Overall status, I’ll select, let’s give ourselves a green status here. And then project manager, let’s see, project manager, I’ll make myself the project manager. I wrote Brown. Okay. Now, I’m going to click Okay, and let’s see what happens here. I click Okay, it will programmatically launch Microsoft Word, and right before your eyes, it builds the status report. So Project is now controlling Word, and it’s writing this information to the key tasks, to the tasks that were completed this tracking period and the tasks that were supposed to be completed but did not. So notice that here, percent complete says 100% they were completed.
Ira Brown: Notice the date range falls within our tracking period. Here are some more tasks that we’re supposed to finish, but they’re not 100%. So there you go. We just build ourselves a status report. You can see there’s the name of the project, there’s the project manager, there’s the reporting period, and there’s our green. We shaded that so green for our overall status. This is pretty cool. This is doing exactly what we want it to do. Let’s take a look at one more example, and then we’re going to open it up for some questions. So, the last example is going to be, I’m just going to close PowerPoint here, because I don’t want PowerPoint open in this case, we are going to programmatically control PowerPoint. What I want to do is I want to create a presentation in PowerPoint to take this timeline that I have here at the top of this view.
Ira Brown: I want to be able to bring that timeline over into PowerPoint. So let’s take a look at VBA for a moment. View, Macros, Visual Basic. And then I’m going to show you right here, this routine called add timeline to PowerPoint. So just like we started Excel, programmatically and Word programmatically, this is how we start PowerPoint programmatically, pretty much the same concept. But before we start it, these lines of code here actually copy the timeline. So it basically puts that timeline in the Windows clipboard. Starts PowerPoint, makes PowerPoint visible. It opens up my presentation file. So I’m basically using a presentation as a template. Let me just go to that folder. I’ll show you that real quick. This is what the template looks like. It’s just the project widgets logo at the top and essentially a blank slide. And then once we open that template, we then do a paste special.
Ira Brown: Essentially, we’re doing a paste of the timeline as a bitmap image. That’s what this does right here. Then we position it where we want it. We’re sending the left and the top so that it’s centered on the slide. And then finally, we actually run the slideshow. Shall we give this a try? Let’s go back to our ribbon. Let’s click Export Timeline to PowerPoint. Here we go. Click the button, fire up PowerPoint, bring over my timeline, put it in the slide, go into presentation mode. And there it is. So all that was done programmatically. I realized this is a very simple example or fairly simple example, but basically anything that you can do manually in PowerPoint for the most part, you can do programmatically. That’s the good news. The bad news about PowerPoint, it does not have a macro recorder. So you actually have to refer to some online documentation.
Ira Brown: When I click on Macro, see there’s no recorder here. I can create my macros if I know what I’m doing. But the good news is once you get good at this with Project, once you get good at this with Excel, you can apply those same skills to PowerPoint. Instead of working with worksheets or tasks in PowerPoint, you work with things like presentations and slides and fonts and things like that. But again, no recorder, but you can still automate pretty much anything that you want in PowerPoint, just takes a little bit more work to do it. So with that, folks, I think we have completed the material. Let me just remind you that we’re going to open up for questions in a second. But let me just remind you that if you, I’ll just put this slide up here real quick, if you send me an email, I’m going to put my email address up right now.
Ira Brown: You will be entered into our… Oh, looking at it. Somehow the quantity just went from two to three. Three free copies of the Driving Path of Widget. And then all you have to do is send me an email. My email address is ira.brown@projectwidgets.com. I encourage you to go out to our website, you can learn a lot about our services and all the different widgets that we have available. There’s even some free ones you can download as well. If you’re interested in seeing a demo of any of our other widgets that are on our website, please reach out to me. I’d be happy to arrange a time to meet with you. If there’s any that you’re interested in, I’d be happy to give you a demo. So again, thank you so much for joining the session today. Melanie, do we have any questions?
Melanie: Ira, we do.
Ira Brown: Okay.
Melanie: Steve, “Would it be easier or harder to create a table in Excel to refer to column names rather than numbers?”
Ira Brown: Yeah, I think that’s a good technique. Because oftentimes, if you’re just talking about column one, column two, you can easily say, well, A is one, B is two, et cetera. But there is a little technique that I find pretty useful in Excel, that if you go into your File options in Excel and then click on Formulas, you can see where this is option that’s unchecked, R1, C1 reference style. If you check that, notice what will happen is instead of seeing ABCDE at the top there, you actually see the column number. So I find that pretty useful if you’re trying to figure out what column to use. But yes, if you create a table and you can refer to that column by a name rather than the number or even the letter, then that could make this even easier. I agree.
Melanie: Super thank you. Thank you, Ira. Thank you, Steve. So from Teresa, can you change the date from text to an actual date format?
Ira Brown: Yes. That’s a great question. Well, the answer is yes. However, it’s maybe not quite as easy as you’d like it to be. Let me open up this Excel file for a second. This is the one we used. Notice that when we do our export, this is how it brings it over. Now, there is that technique in Excel where you can say, let’s apply a formatting to this. But the problem is that even if we format this as a date, I’ll pick date here and then I’ll say, let’s show it like that for example, it doesn’t do it because it thinks this is text. So what I’ve had to do is I’ve had to strip out the time element here. Once I get rid of the time, like I get rid of where it says 8:00 AM, then all of a sudden Excel sees it as an actual date.
Ira Brown: What I’ve been able to do is, there’s lots of ways of doing it, but an easy technique might be to do a find and replace. Because most of what we’re looking to replace you can see is either eight o’clock, one o’clock or 12 o’clock. Well, there’s a three o’clock there too. So this is not the perfect solution. There’s other techniques as well. Well, you can look at maybe the number of spaces and basically you just have to do some string manipulation here. But if I search for space eight o’clock, space am, and replace it with nothing and then do a replace all, you see how I’ve just fixed the problem for 20 of them, and then I’d have to do it for the other time. So there are other techniques as well, but essentially once you converted from text to a date, then you can apply whatever format you want.
Melanie: That was a great question. Okay. So from Mike, he wants to know, can you automate project data into Visio and then Gantt chart template?
Ira Brown: Yes. Maybe in a future session, we’ll have one that actually explores how VBA from Project can integrate with Visio. The bottom line is, I don’t know if I have Visio installed on my computer or not. Let’s find out if I do. It looks like I probably do not, but Visio also has VBA built into it. I don’t remember if it has a recorder or not, but it has VBA. So you can programmatically manipulate and integrate with Visio as well.
Melanie: On that note, Ira, I have had people asking as we’re in the session, can we do another session, a full course with you even deeper on this subject? So I will be harassing you about that.
Ira Brown: Want to do a part four and a part five, you think?
Melanie: We’re just going to keep going.
Ira Brown: All right.
Melanie: Another question from Dan, in the PowerPoint code, you declared OBJ PowerPoint as the PowerPoint application rather than as an object as you did in the Excel code. What is the difference between the two approaches?
Ira Brown: That’s a great question. Well, remember how I talked for a moment about setting a reference when you go to Tools references, and then I picked PowerPoint? If I set a reference to PowerPoint, then I can declare, I can start the application differently. Instead of saying, create object. I can actually say equals new PowerPoint.application. It basically does the same thing. But because I’ve declared an object, sorry, because I declared a reference to PowerPoint, now as I am typing my code, like if I say OBJ PowerPoint, I hit a period here, see how it gives me these choices. By having the reference, it gives me all the possible commands that I can choose from. So if you just declared it as an object by doing create object, you don’t get that added benefit. So this would be the preferred technique. Once you get a little better at it, once you get more experience, I would say, this would be the preferred technique to make the code writing even easier.
Melanie: Excellent. Excellent. That looks like it for the questions.
Ira Brown: All right. Very good. Well, I know we’re at the top of the hour here. So again, I just want to thank everybody for attending the session. Again, feel free to reach out, ira.brown@projectwidgets.com. I’ll send you the presentation. I’ll send you all the VBA code we generated. Again, I want to thank Melanie for her hospitality. This was a lot of fun. Hopefully we’ll do it again sometime.
Melanie: Thank you. A big, big thank you to Ira. This is an excellent series, and we will try to get him back to go even farther. A big thank you to the MPUG community. Thank you for choosing MPUG to grow your skills with today. Again, the PDU code, I have up on the screen here. Following today’s event, I will send a link to the recording. I’ve chatted those out as well. And we’re going to send a quiz, covering the entire series. The quiz is solely to test your knowledge and for your records, you can take it as many times as you want. So please save a copy of your results. If you’d like to keep it along with your transcripts, we will not save that information. So that is all up to you if you keep that.
Melanie: I will also send out a super short survey shortly. Please let us know what you think. We invite you as well to join us back here for some other sessions. We’ve asked you what your frustrations are. Some of these sessions that are coming up specifically answer those frustrations. So our first session in October, that’s coming up, is about modifying custom fields and project for the web. You can do that, and we’re going to answer your frustrations in a session covering all you need to know about task types. That is pretty exciting. You can sign up for any of these today and save your seat MPUG.com/events. Again, thank you for joining us today. I will leave the PDU code up on the screen so you don’t miss that. We’ll look forward to hearing from you in our surveys and seeing you at a future event. Thank you again.
