Lesson Objectives
Upon completion of this lesson, the participant will be able to:
Some Topics to be introduced in this lesson include:
FILE INPUT/OUTPUT
This section is a light introduction to file input/output, and emphasizes sequential processing of CSV files.
Visual Basic for Applications has a rich set of file input/output commands. You can read/write data from any offset within a file, or read/write data sequentially. You can easily read/write comma separated files or read/write a byte at a time, a line at a time, or fixed length records.
Sequential processing means that you read from a file or write to a file sequentially, starting from the beginning of the file. Random processing means that you can read data from a file or write data to a file, starting at any position within the file.
Here's a couple of possible uses for file input/output in MSProject macros:
Create custom reports that write to CSV files.
Create (write) or process (read) custom mpx files.
Use a temporary file to "dynamically" exchange data between MSProject and another application or a VB3 program.
The following commands can handle most sequential file input/output needs:
Command |
Common Usage |
| FreeFile | Get a free file handle (needed by other commands) |
| Open | Open the file |
| Input # | Read in fields and assign them to variables |
| Write # | Write out data - automatically comma separated |
| Print # | Write out data - not automatically comma separated |
| EOF | Used to check if all records have been read |
| LOF | Length of file - file must have been opened by Open |
| FileLen | Length of file - must not be open - you specify the filename |
| Close | Close a file opened by Open |
NOTE: If you leave out the Close command at the end of your macros, or if you get a runtime error before the macro executes the Close command, you may get a message telling you the file is already open when you run the macro again. If this happens, use the menu command Run Reset.
If you need random access, then you'll want to look at some of these commands:
| Put | Write data starting at a specified position within the file |
| Get | Read data starting at a specified position within the file |
| Seek | Specify the position for the next read/write |
| Loc | Returns the position for the next read/write |
See Online Help for the detailed syntax and options available with the above commands.
Formatting Concerns
When reading/writing CSV files, you need to deal with the formatting that the CSV file contains. Different applications format different kinds of data in different ways when creating CSV files. About the only thing that is consistent is that the fields are separated by commas. The table below shows how the same MSProject fields would be written out using the Write# command compared to using FileSaveAs with the CSV format. It shows how the Id, Name, Duration, Start, and Flag1 task properties are written out for a single task, with no additional formatting. The task has Id=1. It starts on 1/2/95 8:00 AM, but the current MSProject Date Format shows the date in a task table as 1/2/95. It is assumed that Activeproject.HoursPerDay is 8.
Screen 1 |
MsgBox 2 |
Write# 3 |
FileSaveAs 4 |
| 1 | 1 | 1 | 1 |
| task1 | task1 | "task1" | task1 |
| 1d | 480 | 480 | 1d |
| 1/2/95 | 1/2/95 8:00:00 AM | #1995-01-02 8:00:00# | 1/2/95 |
| Yes | True | #TRUE# | No |
1 The Screen column shows how the data appears in the task table on screen.
2 The MsgBox column was discovered by examining the output from:
Set t = ActiveProject.Tasks(1)
MsgBox t.ID
MsgBox t.Name
MsgBox t.Duration
MsgBox t.Start
MsgBox t.Flag1
3 The Write# column was discovered by writing to a text file and then examining the text file in NotePad. The code looked like:
Dim fnum As Integer, t As Object
fnum = FreeFile
Open "c:\test.csv" For Output As fnum
Set t = ActiveProject.Tasks(1)
Write #fnum, t.ID, t.Name, t.Duration, t.Start, t.Flag1
4 The FileSaveAs column was created manually by choosing File, Save As, and using the CSV format.
Formatting woes: The # signs in the Write# column aren't recognized as date and boolean (true/false, yes/no) qualifiers when the CSV file created by Write# is manually read back in by MSProject or Excel using File Open (error messages result in MSProject). Also, if the pure number 480 is read back into MSProject using File Open, it doesn't interpret it as minutes, and instead would tack on the default duration units, so you could end up with a duration of 480d.
The table below shows some ways to get around these formatting problems. It assumes the same data as in the previous table.
Screen |
Command |
Writes this |
| 1d | Write# fnum, t.Duration | 480 |
| 1d | 1 Write# fnum, t.Duration / 60 / 8 & "d" | "1d" |
| 1d | 2 Write# fnum, t.GetField(pjTaskDuration) | "1d" |
| 1/2/95 | Write# fnum, t.Start | #1995-01-02 8:00:00# |
| 1/2/95 | 2 Write# fnum, CStr(t.Start) | "1/2/95 8:00:00 AM" |
| 1/2/95 | Write# fnum, t.GetField(pjTaskStart) | "1/2/95" |
| Yes | Write# fnum, t.Flag1 | #TRUE# |
| Yes | Write# fnum, CStr(t.Flag1) | "True" |
| Yes | Write# fnum, Format(t.Flag1,"yes/no") | "Yes" |
| Yes | 2 Write# fnum, t.GetField(pjTaskFlag1) | "Yes" |
1 In general, the expression: T.Duration / 60 / 8
should be replaced by: T.Duration / 60 / Activeproject.HoursPerDay
2 Commonly used methods.
|
Try This This MSProject macro writes Id, Name, Duration, Start, and Flag1 values for all tasks in the active project to a CSV file. 1. Open/Create a test file with some tasks. 2. Enter and run the following MSProject macro: Sub write3() Dim fnum As Integer Dim oTask As Object fnum = FreeFile() Open "c:\test.csv" For Output As fnum For Each oTask In ActiveProject.Tasks Write #fnum, _ oTask.ID, _ oTask.Name, _ oTask.GetField(pjTaskDuration), _ CStr(oTask.Start), _ oTask.GetField(pjTaskFlag1) Next Close fnum End Sub 3. After the macro finishes, open up c:\test.csv in NotePad and examine how the data is formatted. Close NotePad. 4. Open c:\test.csv in Excel and examine how the data is formatted. Close Excel. 5. Use the MSProject menu command File Open to open c:\test.csv. Any problems? Did a task sheet view have to be active for this macro to work? Did there have to exist a table that begins with fields Id, Name, Duration, Start, and Flag1? |
|
Try This This MSProject reads in task Text1, Name, and Duration fields from a text file and searches for a task in the active project whose Text1 and Name fields match those read in - the search is case sensitive for Text1, but not for Name. If it finds a match, it sets the duration of that task to the duration read in. If doesn't find a match then if adds a new task with the Text1, Name, and Durations read in. 1. Enter the text below in NotePad. Save it as c:\match.txt and then exit NotePad. "012","task x","10d" "005","task y","20d" "007","task w","30d" 2. Create a project with the following task data: Id Name Duration Text1 1 task a 1d 009 2 task x 1d 011 3 task w 1d 007 4 task x 1d 012 3. Enter the following MSProject sub procedure, but don't run it yet. Sub MergeFile() Dim fnum As Integer, otask As Object Dim Text1Input$, NameInput$, DurationInput$ Dim matchingID As Integer fnum = FreeFile() Open "c:\match.txt" For Input As fnum Do Until EOF(fnum) Input #fnum, Text1Input, NameInput, DurationInput matchingID = GetIdOfMatch(Text1Input, NameInput) If matchingID = 0 Then Set otask = ActiveProject.Tasks.Add Else Set otask = ActiveProject.Tasks(matchingID) End If otask.Text1 = Text1Input otask.Name = NameInput otask.Duration = DurationInput Loop Close fnum End Sub 4. Enter the following MSProject function. It is passed the Text1 and Name read in, and it returns the ID of the first matching task it finds. If there is no match, it returns a 0. Function GetIdOfMatch(TextInput$, NameInput$) As Integer Dim otask As Object For Each otask In ActiveProject.Tasks If Not otask Is Nothing Then If otask.Text1 = TextInput _ And UCase(otask.Name) = UCase(NameInput) Then GetIdOfMatch = otask.ID Exit Function End If End If Next GetIdOfMatch = 0 End Function 5. Run the MergeFile macro and then check a task table to see if it updated the tasks correctly. It should look like this: Id Name Duration Text1 1 task a 1d 009 2 task x 1d 011 3 task w 30d 007 4 task x 10d 012 5 task y 20d 005 |
Binary Examples
Example
This MSProject macro plugs the word "hello" into an existing file Test1, starting at the 3rd byte, without changing the rest of the file.
Sub Bin1()
Dim fnum As Integer
Dim s As String
fnum = FreeFile()
Open "c:\Test1" For Binary As fnum
s = "hello"
Put fnum, 3, s
Close fnum
End Sub
Example
This MSProject macro reads 5 characters, one character at a time, from the file Test1, starting with the 3rd byte in the file. The Ascii code for each byte read is displayed in a MsgBox, along with the character, if it's printable, or a message if it's not.
Sub Bin2()
Dim fnum As Integer
Dim n as integer
Dim c As String * 1 'String can hold 1 char only
Dim s As String
fnum = FreeFile()
Open "c:\Test1" For Binary As fnum
For n = 3 To 5
Get fnum, n, c
If Asc(c) >= 32 Then
s = c
Else
s = "Can't display"
End If
MsgBox "n = " & n & Chr(10) & _
"Ascii code = " & Asc(c) & Chr(10) & _
"Character: " & s
Next
End Sub
7,"10/1/94 8:00:00 AM"
Don't write out records for blank tasks. Assume there's at least one nonblank task in the active project. Don't bother with a custom error handler to handle bad filenames etc. Test this on a project with a couple of tasks and examine the file it creates (open it in NotePad).
3,"5d"
1,"2d"
7,"3d"
Save the file as "C:\M3L6E2.TXT".
Write a macro that reads in this file and assigns the durations to the appropriate tasks (matching the Unique Ids). You can hard code the filename "C:\M3L6E2.TXT", but make no assumptions as to the number of records it contains (i.e. use the Do Until EOF(fnum) loop).
Details: Declare a long variable Uid and a string variable Dur. For each record, the Input# command reads the record into the Uid and Dur variables; then the duration of the task with Unique Id = Uid is set equal to the value of Dur. If there is no task with Unique Id = Uid, then display the message "There is no task with Unique Id = ", followed by the value of the variable Uid. Then continue on to the next record.
This is much easier than it looks - no searching required. For example, if a task has Unique Id = 5, and you wanted to set its duration to 100d, you could use the Unique Id directly:
ActiveProject.Tasks.UniqueId(5).Duration ="100d"
If there is no task with Unique Id = 5, then the above statement would cause a trappable error (On Error Resume Next etc.). Of course, you're working with variables Uid and Dur instead of literals 5 and "100d".
Test the macro: Create a project with at least 7 tasks. Display the Unique Id column. Run the macro. Check that the durations of the tasks with unique ids 1, 3, and 7 have changed correctly. Then try deleting the task with unique id = 3 and run the macro again. It should give the error message about unique id = 3.