F
freeup86
I am using VB Code generously donated by another member when i run the
code, I get the following error "The argument is not valid." error
1101 when it reaches the TSV(I).Value line.
' Timesheet data importer V1.0
' Michael Rosenberg 25/7/2001 (e-mail address removed)
' This VBA will load timephased data from a file and store the values
' against the actual work of the appropriate task.
' It expects an entire week worth of data on one line, week start date
is the SUNDAY of
' that week, going through to the Saturday.
' The file format is: <Task Unique Id>,<Resource Name>,#<Week Start
Date>#,<Monday hours>,<Tuesday hours>,<Wednesday hours>,
' <Thursday hours>,<Friday hours>,<Saturday
hours>,<Sunday hours>
' V1.0 MJR 25/7/2001 Initial Release
' V1.1 MJR 25/7/2001 Fixed "can't find task" error message
Public Pathname As String
Sub TimesheetImport()
Dim TSV As TimeScaleValues
Dim MyChar As String
Dim UniqueId As Integer
Dim TaskName As String
Dim ResourceName As String
Dim SDate As Date
Dim Mon As Single, Tue As Single, Wed As Single, Thu As Single, Fri As
Single, Sat As Single, Sun As Single
' Get the path name of the input file - sets Pathname
Pathname = "C:\Documents and Settings\Z06948\Desktop\actualwork.txt"
If Dir(Pathname) = "" Then
MsgBox "File does not exist"
End
End If
Open Pathname For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
Input #1, UniqueId, ResourceName, SDate, Mon, Tue, Wed, Thu, Fri,
Sat, Sun
' Ignore the line if the task unique id wasn't specified
If UniqueId > 0 Then
Call UpdateTheTasks(UniqueId, ResourceName, SDate, Mon,
Tue, Wed, Thu, Fri, Sat, Sun)
End If
Loop
Close #1 ' Close file
End Sub
Sub UpdateTheTasks(UniqueId As Integer, ResourceName As String, SDate
As Date, _
Mon As Single, Tue As Single, Wed As Single, Thu As Single,
Fri As Single, Sat As Single, Sun As Single)
' Search through tasks for the Unique Id
Dim T As Task ' Task object used in For Each loop
Dim I As Long ' Used in For loop
Dim R As resource
Dim A As Assignment
Dim Exists As Boolean ' Whether or not the task exists
Dim TheTaskId As Integer
Dim TheAssignmentId As Integer
Dim TheResourceId As Integer
Dim TheAssignment As Assignment
Dim TheTask As Task
Dim TSVS As TimeScaleValues
Dim EndDate As Date
Dim TSV As TimeScaleValue
Dim I As Long
' Check that the resource exists in the project
If Not ResourceExists(ResourceName, TheResourceId) Then
MsgBox ("Resource Name " + ResourceName + " does not exist in
the project")
End
End If
Exists = False ' Assume task doesn't exist.
' Search active project for the specified task.
For Each T In ActiveProject.Tasks
If T.UniqueId = UniqueId Then
Exists = True
TheTaskId = T.ID
Set TheTask = T
End If
Next T
' If task doesn't exist, display error and quit Sub procedure.
If Not Exists Then
MsgBox ("Task Unique Id " & UniqueId & " not found.")
End
End If
' Search to see if the resource has been assigned to this task
Exists = False
For Each A In ActiveProject.Tasks(TheTaskId).Assignments
If A.ResourceName = ResourceName Then
Exists = True
TheResourceId = A.ResourceID
Set TheAssignment = A
End If
Next A
' If the resource doesn't exist in the assignment, then assign the
resource
If Not Exists Then
Set TheAssignment =
ActiveProject.Tasks(TheTaskId).Assignments.Add(TheTaskId,
TheResourceId, 0)
End If
' Set the actual work field of the assignment
EndDate = DateAdd("d", 7, SDate)
Set TSV = TheAssignment.TimeScaleData(SDate, EndDate,
pjAssignmentTimescaledActualWork, pjTimescaleDays, 1)
TSV(I).Value = Sun * 60
TSV(2).Value = Mon * 60
TSV(3).Value = Tue * 60
TSV(4).Value = Wed * 60
TSV(5).Value = Thu * 60
TSV(6).Value = Fri * 60
TSV(7).Value = Sat * 60
End Sub
Function ResourceExists(ResourceName As String, RId As Integer)
'Check that the resource is defined within the project
Dim R As Long, Names As String, Exists As Boolean
Exists = False
For R = 1 To ActiveProject.Resources.Count
If ActiveProject.Resources(R).Name = ResourceName Then
Exists = True
RId = ActiveProject.Resources(R).ID
End If
Next R
ResourceExists = Exists
End Function
code, I get the following error "The argument is not valid." error
1101 when it reaches the TSV(I).Value line.
' Timesheet data importer V1.0
' Michael Rosenberg 25/7/2001 (e-mail address removed)
' This VBA will load timephased data from a file and store the values
' against the actual work of the appropriate task.
' It expects an entire week worth of data on one line, week start date
is the SUNDAY of
' that week, going through to the Saturday.
' The file format is: <Task Unique Id>,<Resource Name>,#<Week Start
Date>#,<Monday hours>,<Tuesday hours>,<Wednesday hours>,
' <Thursday hours>,<Friday hours>,<Saturday
hours>,<Sunday hours>
' V1.0 MJR 25/7/2001 Initial Release
' V1.1 MJR 25/7/2001 Fixed "can't find task" error message
Public Pathname As String
Sub TimesheetImport()
Dim TSV As TimeScaleValues
Dim MyChar As String
Dim UniqueId As Integer
Dim TaskName As String
Dim ResourceName As String
Dim SDate As Date
Dim Mon As Single, Tue As Single, Wed As Single, Thu As Single, Fri As
Single, Sat As Single, Sun As Single
' Get the path name of the input file - sets Pathname
Pathname = "C:\Documents and Settings\Z06948\Desktop\actualwork.txt"
If Dir(Pathname) = "" Then
MsgBox "File does not exist"
End
End If
Open Pathname For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
Input #1, UniqueId, ResourceName, SDate, Mon, Tue, Wed, Thu, Fri,
Sat, Sun
' Ignore the line if the task unique id wasn't specified
If UniqueId > 0 Then
Call UpdateTheTasks(UniqueId, ResourceName, SDate, Mon,
Tue, Wed, Thu, Fri, Sat, Sun)
End If
Loop
Close #1 ' Close file
End Sub
Sub UpdateTheTasks(UniqueId As Integer, ResourceName As String, SDate
As Date, _
Mon As Single, Tue As Single, Wed As Single, Thu As Single,
Fri As Single, Sat As Single, Sun As Single)
' Search through tasks for the Unique Id
Dim T As Task ' Task object used in For Each loop
Dim I As Long ' Used in For loop
Dim R As resource
Dim A As Assignment
Dim Exists As Boolean ' Whether or not the task exists
Dim TheTaskId As Integer
Dim TheAssignmentId As Integer
Dim TheResourceId As Integer
Dim TheAssignment As Assignment
Dim TheTask As Task
Dim TSVS As TimeScaleValues
Dim EndDate As Date
Dim TSV As TimeScaleValue
Dim I As Long
' Check that the resource exists in the project
If Not ResourceExists(ResourceName, TheResourceId) Then
MsgBox ("Resource Name " + ResourceName + " does not exist in
the project")
End
End If
Exists = False ' Assume task doesn't exist.
' Search active project for the specified task.
For Each T In ActiveProject.Tasks
If T.UniqueId = UniqueId Then
Exists = True
TheTaskId = T.ID
Set TheTask = T
End If
Next T
' If task doesn't exist, display error and quit Sub procedure.
If Not Exists Then
MsgBox ("Task Unique Id " & UniqueId & " not found.")
End
End If
' Search to see if the resource has been assigned to this task
Exists = False
For Each A In ActiveProject.Tasks(TheTaskId).Assignments
If A.ResourceName = ResourceName Then
Exists = True
TheResourceId = A.ResourceID
Set TheAssignment = A
End If
Next A
' If the resource doesn't exist in the assignment, then assign the
resource
If Not Exists Then
Set TheAssignment =
ActiveProject.Tasks(TheTaskId).Assignments.Add(TheTaskId,
TheResourceId, 0)
End If
' Set the actual work field of the assignment
EndDate = DateAdd("d", 7, SDate)
Set TSV = TheAssignment.TimeScaleData(SDate, EndDate,
pjAssignmentTimescaledActualWork, pjTimescaleDays, 1)
TSV(I).Value = Sun * 60
TSV(2).Value = Mon * 60
TSV(3).Value = Tue * 60
TSV(4).Value = Wed * 60
TSV(5).Value = Thu * 60
TSV(6).Value = Fri * 60
TSV(7).Value = Sat * 60
End Sub
Function ResourceExists(ResourceName As String, RId As Integer)
'Check that the resource is defined within the project
Dim R As Long, Names As String, Exists As Boolean
Exists = False
For R = 1 To ActiveProject.Resources.Count
If ActiveProject.Resources(R).Name = ResourceName Then
Exists = True
RId = ActiveProject.Resources(R).ID
End If
Next R
ResourceExists = Exists
End Function