|< Day Day Up >|
One of the valuable features of VBA is that you can use it in Project to control other applications that have VBA. Often when you're working in Project, you want to be able to export to Excel with a bit more intelligence than is provided by the simple export maps.
For example, you might want to be able to export the tasks and retain the hierarchy that exists in the project file. The code to do this does a fairly simple export of just the task names , but you can easily extend it to export other columns , containing duration, start dates, task percentage complete, or any of the fields available in Project.
All the objects, methods , and properties in Excel can be accessed and controlled by Project. To do this, Project needs to reference the Excel object model, which is contained in the Microsoft Excel Object Library. This library is present on any computer that has Excel installed; however, it must be referenced within the VBE before you can begin working.
To set a reference to the Excel Object Library, select Tools, References. You should see a list of all the libraries that are available on the computer you are working on. Scroll down until you find the Excel library, and place a check mark in the box next to it.
If you were giving instructions to a person, you would ask that he or she follow these steps:
Here is the translation of those steps into Visual Basic statements:
Option Explicit Sub TaskHeirarchy() Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim xlRow As Excel.Range Dim xlCol As Excel.Range Dim Proj As Project Dim T As Task Dim ColumnCount, Columns, Tcount As Integer Tcount = 0 ColumnCount = 0 Set xlApp = New Excel.Application xlApp.Visible = True AppActivate "Microsoft Excel" xlApp.Cursor = xlWait Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets.Add xlSheet.Name = ActiveProject.Name 'Set Range to write to first cell Set xlRow = xlApp.ActiveCell xlRow = "Filename: " & ActiveProject.Name Set xlRow = xlRow.Offset(1, 0) xlRow = Date() Set xlRow = xlRow.Offset(2, 0) 'Write each task and indent to match outline level For Each T In ActiveProject.Tasks If Not T Is Nothing Then Set xlRow = xlRow.Offset(1, 0) Set xlCol = xlRow.Offset(0, T.OutlineLevel - 1) xlCol = T.Name If T.Summary Then xlCol.Font.Bold = True End If Tcount = Tcount + 1 End If Next T 'Switch back to Project and display completion message xlApp.Cursor = xlDefault AppActivate "Microsoft Project" MsgBox ("Macro Complete with " & Tcount & " Tasks Written") AppActivate "Microsoft Excel" End Sub
This code begins with a definition of the variables and sets initial values for some of the variables that you will be using. Because the code is working with Excel as well as Project, the variables include some objects from Excel:
Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim xlRow As Excel.Range Dim xlCol As Excel.Range
You already have Project open if you are running this macro, but it is possible that Excel is not started. The procedure must start Excel before it can export data to it.
To start Excel, follow these steps:
With Excel open and active, the procedure can start writing data to Excel. The steps the procedure performs to write are as follows :
There are many things you could add to this macro. As in Project, if you can do something manually in Excel, there is almost always a way to do it automatically by using Visual Basic, so if you need special formatting or graphing of the data, it is possible to include that in your macro.
Exporting to a Text File
Although Project interacts quite well with Excel and other Microsoft applications, sometimes you might want the data in a different format. The following example reads data from a Project file, modifies it slightly, and writes the results to a text file:
Option Explicit Sub NoteFile() Dim MyString As String Dim MyFile As String Dim fnum As Integer Dim myTask As Task 'set location and name of file to be written MyFile = "c:\" & ActiveProject.Name & "_Project_Notes" & ".txt" 'set and open file for output fnum = FreeFile() Open MyFile For Output As fnum 'Build string with project info MyString = ActiveProject.Name _ & " " _ & ActiveProject.LastSaveDate _ & " " _ & Application.UserName 'write project info and then a blank line Write #fnum, MyString Write #fnum, 'step through tasks and write notes for each then a blank line For Each myTask In ActiveProject.Tasks If myTask.Notes <> "" Then 'edit the following line to include the fields you want 'use " " to include any text or spaces. MyString = myTask.UniqueID & ": " & myTask.Name & ": " & myTask.Notes 'Some other Examples: MyString = MyTask.Text1 & ": " & MyTask.Start Write #fnum, MyString Write #fnum, End If Next myTask Close #fnum End Sub
Opening a File for Writing
To write to a file, you use the Open statement, which either opens a file or creates one. The Open statement requires a number of arguments, including the pathname to the file, the mode (which controls the behavior), and a file number (which it obtains by using the FreeFile() function). The key things to understand here are how to create and write to the file.
To use the Open statement, follow these steps:
The rest of the macro simply steps through all the tasks and writes the task unique ID, the task name, and the task notes. When you are done, close the file. This macro outputs text by using the input mode and Read # statements, but you could bring in the contents of a text file or a .csv file that might be generated by some other application or that contains configuration information.
|< Day Day Up >|