Working with Other Applications

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

  1. Open a copy of Excel.

  2. Create a sheet and give it the same name as the project.

  3. Put some identifying information and column headers in place.

  4. Export the tasks one by one and indent them.

  5. When the work is done, return a message to the user , stating that the work is done.

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:

  1. Create an instance of Excel by setting the variable xlApp to a new instance of Excel:

     Set xlApp = New Excel.Application 
  2. Set the application to be visible so that you can see it and activate it, which brings it to the front of your desktop:

     xlApp.Visible = True AppActivate "Microsoft Excel" 
  3. While you are processing the macro, you need to also set the cursor to an hourglass so users will know that something is going on and they should wait:

     xlApp.Cursor = xlWait 
  4. Create a new workbook, similarly to the way you created an instance of Excel, and then add a worksheet to hold the data you are exporting. To make sure that the user can find this sheet easily, you should give it the same name as the project:

     Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets.Add xlSheet.Name = ActiveProject.Name 

With Excel open and active, the procedure can start writing data to Excel. The steps the procedure performs to write are as follows :

  1. Select which cell to write to. By default, the active cell in a new worksheet is the first cell (A1). The procedure takes the predefined range and sets it to that cell:

     Set xlRow = xlApp.ActiveCell 
  2. Set the value of that range to be the value you would like. As in this example, it is set to project name:

     xlRow = "Filename: " & ActiveProject.Name 
  3. After the first cell is written in, move to another cell for the next piece of data. This uses the offset method. The values in the parentheses are the offset in number of rows and columns, respectively. In the new cell write OutlineLevel :

     Set xlRow = xlRow.Offset(1, 0) xlRow = "OutlineLevel" 
  4. Continue to move through the spreadsheet in a similar manner. Because the procedure will be moving down the spreadsheet and across the spreadsheet, two different ranges (or pointers) have been defined. The first, xlRow , moves down the sheet, offsetting row by row. The second, xlCol , moves across the spreadsheet. By using offsets from xlRow , xlCol can be held stationary at any column while xlRow keeps moving down row by row in the first column. Using this method makes it easy to keep track of the position of the cell that is being writing to.

  5. The procedure loops through all the tasks and offsets the range an amount equal to the outline level of the task:

     Set xlCol = xlRow.Offset(0, T.OutlineLevel - 1)         xlCol = T.Name 

    Each time the range moves down a line, the task counter is incremented by 1 and the cells are set to bold if the task is a summary task:

     If T.Summary Then             xlCol.Font.Bold = True 
  6. The procedure resets the cursor to the default, so after it finishes writing, the user is not stuck with an hourglass when he or she goes to the spreadsheet.

  7. Switch back to Project to display a completion message.

  8. Switch back to display the finished spreadsheet.

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:

  1. Get the arguments ready to execute the Open statement. You can use the variable MyFile to hold the name of the file, which we create by concatenating the project name and some other text:

     MyFile = "c:\" & ActiveProject.Name & "_Project_Notes" & ".txt" 
  2. Use the FreeFile() function to find the next available file number, and use it to create a file object that you can use within the macro:

     fnum = FreeFile() 
  3. Open the file for writing:

     Open MyFile For Output As fnum 
  4. Control what is written by using a write # statement, which takes a file number and an expression to write. For example, you can write a string that we created earlier, and then execute a write statement with no expression to write a blank line:

     Write #fnum, MyString Write #fnum, 

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 > 

Special Edition Using Microsoft Office Project 2003
Special Edition Using Microsoft Office Project 2003
ISBN: 0789730723
EAN: 2147483647
Year: 2004
Pages: 283
Authors: Tim Pyron

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: