Issues in the Linked Expense Application Project Management Help

That covers the minimum steps that you need to implement this Project application. However, you can add other features as well. It would be nice, for example, to be able to open up the Excel workbook from Project. Adding this feature requires one additional macro, so we’ll include it in the application

Before we begin coding, we need to make some other decisions that affect how the application functions. For example, we must decide whether we want this application to be globally available or part of a template. The application as designed has one crystallization: because it holds a reference to the Excel workbook in a public object variable, the macro work for only one project at a time. If this application is placed in he user will have to close the budget of one project before opening the budget of another project document. This can be corrected by adding one line at the end of each macro:

Creating the Custom Field

You will need to have one custom field to store link information. for A .detailed information on creating and using custom fields.) Rename the custom Task field Text 30 “Budget Unit Source”. You could use any field you but our macros are written for the Text 30. This field was chosen because it is that it has been. customized for other use. To rename a custom field in Project 2000, folIow these steps

1. In Project, choose Tools> Customize> Fields to open th~ Customize Fields dialog box ..
2. Use the option buttons to choose Task or Resource, then select the Type of field you want to customize from the drop-down list.
3. Select a field from the field list and click the Rename button.
4. In the Rename dialog box, type a new name for the field and click
5. Click OK to close the Customize Fields’ dialog box.

Renaming a custom field in the Customize Fields dialog box has no effect on the object model. For example; you renamed the custom Task field Finish as Submit To accounting. To.access your custom field from VB, you would still use the Finish property, not the Submit To Accounting property

Creating the Application Code

Open the Visual Basic Editor, select the project you want to create the application in, and create a new Module. Name the module Excel_Budget. The following paragraphs describe the forms and procedures that handle the application requirements listed at the beginning of this section. The descriptions are followed by the code for the application. You can download this code, and a sample Excel workbook, from the Mastering  Project 2000 page on the Sybex Web site but you’ll learn more about the objects used in this application if you create one or more of the procedures in the VB Editor.

Updating the Links

When an item is initially linked, the current value stored in Excel will be copied to the fixed cost. However, like linked fields in Word or Excel, the links in this application will not automatically be updated. Your application will need a method to refresh the links. This is.accomplished by the macro Excel_Budget Update_Links.

dosing Excel After You Finish

We decided .that the individual macros for managing links would open Excel ;f it were not already opened, but would not close Excel. The user wouldn’t appreciate this application closing an Excel session they were using. The Object Variable that refers to Excel is placed in a standard module in the Global template. After Excel is opened, the reference will be held until project closes. Though Excel is opened invisibly, it is still loaded into memory. Once the user is done with Excel there is no . reason to continue using system resources to run it.

Showing Excel from Projed

Unlike many applications, Excel opens invisibly when it is called through automation. To open Excel so the user can edit the linked workbook, you merely have to make Excel visible. The macro Excel_Budget Show handles the settings to make the workbook visible to the user.

Completed Code for VBA Module Excel_Budget

Way of refering to the Excel workbook (for macro procedures)
Public xl WorkBookAs Object
‘String to hold error ~essage for Connection to Excel Error.
Const strConnectErrMsg – ‘Error Connecting to Linked Worksheet. ‘ &
‘Check Budget Settings. to verify’ &_
‘workbook location is correct.’

Public Sub Excel_Budget_Add~Link()
If ActivateExcel Then
Else . .’Report error if can not connect
MsgBox strConnectErrMsg~ vbCritical
End If
End Sub
Publ +c Sub Excel_BudgeCRemove_L inkO
‘Clear link information
ActiveCell.Task.Text30 –
End Sub
Public Sub Excel_Budget_Show()
If ActivateExcel Then
‘Excel Sta~ts invisible show it now
xlWorkBook.Applicati~n.Visible – True
‘Report error if can not connect
Msg80x strConnectErrMsg, vbCritical
End If
End Sub
Public Sub Excel_Budget_Update_Links()
Dim objTasks As Tasks
Dim objTask As Task
Dim strField As String
Dim strMsg As String
On Error Resume Next
‘Check that we can access Workbook before update
If ActivateExcel Then
Set objTasks • ActiveProject.Tasks
‘Iterate through list and
For fad ol:7J”TaskIn objTasks
strf’ eld • ~bjTask.Text30
If Len(~trFi eld) > 0 Then
objTast.Fi:xedCost • xlWorkBook.Names(strField).RefersToRange.Value
If Err.Numlber <> O’Then
strMsl· ‘An Error occured while attempting to update Task:’ &_
o~1fask.IP & ‘)’& objTask.Name & ‘From Excel Cell:’ &
strField &vbCrLf & ‘Error’ & Err.Number & ‘:’&_
‘Report error
MsgBox strMsg, vbCritical
End If
End If
Next objTask
‘Report ‘Update done
~sgBox ‘Update Complete’, vbInformation
‘Report error if can not connect
MsgBox strConnectErrMsg, vbCritical
End If
.A End Sub
Set objTask • Nothing
Set objTasks • Nothing
Public’Sub Excel_Budget_Workbook_Link()
Dim strPath As String
strPath • GetXlsBudgetPath
strPath • InputBox(‘Enter Full Path to Excel Workbook:’, strPath)
‘Input box Returns” if user clicks .cancel.
li ~Q~ ts~r?a~n- ••) inen
‘If user did not update path
SetXlsBudgetPath strPath
‘If the user.was connected then we need to disconnect from old workbook and
connect to new workbook.
If Cx1WorkBook ~s Nothing) Then
‘Disconnect from old workbook
Set xlWorkBook – Nothing
‘Connect to new workbook.,
If Not ActivateExcel Then
‘Report error if can not connect
MsgBox strConnectErrMsg, vbCritical
End If
End If
End If
End Silb
‘Errors will occur if att~pt to set values in
‘Summary tasks-this just ignores these errors.
On Error ResUlle Next
Dim int~esult As Integer
Dim objTasks As Tasks
Dim objTask As ,Task ,A
‘Display confirmation message
intResult – MsgBoxC’Are you sure you want to unlink the Ex~el Workbook and
all linked fields?’, vbYesNo)
If intResult – vbYes Then
Set objTasks – ActiveProject.Tasks·
‘Remove any links to Excel cells
For Each objT~sk In objTasks
objTask.Text30 –
Next objTask
‘Clear ~ath
End If
Set objTask – Nothing
Set objTasks – Nothing’
End Sub

Public Sub Excel_Budget_Deactivate()
‘Disconnect from workbook
Set xlWorkBook – Nothing
End Sub
Private Function GetXlsBudgetPath() As. String
Dim objDocProps As DocumcntProperties
Dim objDocProp As DocumentProp.erty
On Error Resume Next
Set objDocProps – ActiveProject.CustomDocumentProperties
‘If Doc Prop not found will return error
Set objDocProp – objDocProps.Item(‘ExcelBudgetWorkbook’)
If Err.Number – 0 Then ‘error 0 is no error
GetXlsBudgetP,ath – objDocProp.Va1ue
‘Return no path
GetXlsBudgetPath – ”
End If
.Set objDocProps – Nothing
‘~Set objDocProp – Nothing
End Function
Private Sub SetXlsBudgetPath(Path As String)
Dim objDocProps As DocumentPropert;es
Dim objPropLocation as DocumentProperties
Set objDocProps – ActiveProject.CustomDocumentProperties
If Len(Path) > 0 Then
Set objPropLocaion – objDocPropsC’ExcelBudgetWorkbook’)
If objPropLocation Is Nothing Then ..
objDocProps.Add ‘ExcelBudgetWorkbook’, False, _
msoPropertyTypeString, Path

Set objPropLocation – objOocPropsC’ExcelBudgetWorkbook’)
If Not (objPropLocation Is Nothing) Then
End If
End If
Set objOocProps – Nothing
End Sub
Private F~nction ActivateExcel() As Boolean
Dim strPath As String
On Error GoTo Err_Hnd
‘If The Object Variable points to nothing attempt to Get workbook object
If Not (xlWorkBook Is Nothing) Then
‘Lookup stored Path
strPath – GetX’isBudgetPath
‘Check to see we have a pa~h, before· opening wo~book
If strPath <> II Then
‘Attempt to get object
Set xlWorkBook – GetObject(strPath)
End If
‘Else: Already Connected to workbook
End If
ActivateExtel – True
Exit Function
Set xlWorkBook – Nothing
ActivateExcel – False
End Function

Creating the UserForm

The next step is to create a new User Form in the same VBA project that contains the module Excel_Budget: Change the name of the form to dlgLinkExcelCell This form is very similar to the form you built in the introduction to User Forms earlier in this chapter. The form will have three controls. You will need to add a Command- Button. Label and a ComboBox

Creating the UserForm

Creating the UserForm

Creating the UserForm

Creating the UserForm


Posted on November 26, 2015 in Advance Programming In Project 2000

Share the Story

Back to Top