Excel OLE Automation Example #4

This example creates a spreadsheet from a Milestones Professional schedule.

The Excel Visual Basic Program:

Here's the Visual Basic Program that generates a spreadsheet from an existing Milestones Professional schedule. This is a simple example which you may use as a starting point for building your own program. The Milestones OLE calls are displayed in blue.

Dim objmilestones As Object
Dim numberofrows As Integer
Dim filename As String

Sub CreateSpreadsheet()
Dim numberofrows As Integer
Dim taskrow As Integer
Dim taskname As String
Dim date1 As String, date2 As Date

'get the last file name used from the registry
filenamex = GetSetting("milestones", "testprogramfilename", "milesfilename")
If filenamex = "" Then filenamex = "c:\test.mla"

filename = InputBox("Enter the name of the Milestones File you want to use", "Test VB Program", filenamex)
If filename = "" Then GoTo quitprogram

'save the new file name to the registry
SaveSetting "milestones", "testprogramfilename", "milesfilename", filename

'open the file, creating the Milestones object
Set objmilestones = GetObject(filename)

'activate milestones
With objmilestones
.Activate

'find out how many task rows there are on the schedule
numberofrows = .GetNumberoflines
If numberofrows < 1 Then
MsgBox "Milestones file doesn't have any information"
GoTo quitprogram
End If

'loop for each row on the schedule
For taskrow = 1 To numberofrows

' how many symbols are there?
numberofsymbolsontaskline = .GetNumberofSymbolsInLine(taskrow)
If numberofsymbolsontaskline < 1 Then GoTo nosymbols

date1 = .getsymbolproperty (taskrow, 1, "Date")
Worksheets("Sheet1").Cells(taskrow + 1, 2).Value = date1

If numberofsymbolsontaskline > 1 Then
date2 = .getsymbolproperty(taskrow, 2, "Date")
Worksheets("Sheet1").Cells(taskrow + 1, 3).Value = date2
Else ' put the startdate in the end date field if there is only one symbol
Worksheets("Sheet1").Cells(taskrow + 1, 3).Value = date1
End If

nosymbols:
taskname = .getcelltext(taskrow, 1)
If taskname <> "" Then Worksheets("Sheet1").Cells(taskrow + 1, 1).Value = taskname

Next taskrow

quitprogram:

.keepscheduleopen
End With

End

End Sub

To try this example:

If you would like to try this example, do this:

1		Download the MilestonesOLEExcelExample4.zip file
2		"Unzip" the file to a folder on your computer.
3		Start Excel and open the MilestonesOLEExcelExample4.xls file.
4		In Excel, choose Macro on the Tools menu, then choose Macros.
5		Click to select the "CreateSpreadsheet" macro.
6		Click the run button.
7		When prompted, enter the name of an existing Milestones Professional schedule.

(This download is recommended for those familiar with Visual Basic and Microsoft Excel).

Click here to visit our main programmer's page.