VBA Code - Need Help

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
 
R

Rod Gill

HI,

Please post VBA questions in the Programming ng from now on as that is the
place to go for all things programming re Project.

I would code the solution as:

Option Explicit

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:\Temp\DeleteMe.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 R As Resource
Dim A As Assignment
Dim TheAssignment As Assignment
Dim TheTask As Task
Dim TSVS As TimeScaleValues

' Check that the resource exists in the project
Set R = Nothing
Set R = ActiveProject.Resources(ResourceName)
If R Is Nothing Then
MsgBox ("Resource Name " + ResourceName + " does not exist in the
project")
End
End If

' Search active project for the specified task.
Set TheTask = Nothing
Set TheTask = ActiveProject.Tasks.UniqueId(UniqueId)

' If task doesn't exist, display error and quit Sub procedure.
If TheTask Is Nothing Then
MsgBox ("Task Unique Id " & UniqueId & " not found.")
End
End If

' Search to see if the resource has been assigned to this task
Set TheAssignment = Nothing
For Each A In TheTask.Assignments
If A.ResourceName = ResourceName Then
Set TheAssignment = A
End If
Next A

' If the resource doesn't exist in the assignment, then assign the Resource
If TheAssignment Is Nothing Then
Set TheAssignment = TheTask.Assignments.Add(ResourceID:=R.ID,
Units:=0)
End If

' Set the actual work field of the assignment
Set TSVS = TheAssignment.TimeScaleData(SDate, SDate + 7,
pjAssignmentTimescaledActualWork, pjTimescaleDays, 1)

TSVS(1).Value = Mon * 60
TSVS(2).Value = Tue * 60
TSVS(3).Value = Wed * 60
TSVS(4).Value = Thu * 60
TSVS(5).Value = Fri * 60
TSVS(6).Value = Sat * 60
TSVS(7).Value = Sun * 60
End Sub


You need to use a collection with TimescaleData, you used tsv instead of
tsvs. I've reduced the code by simplifying the find task, resource and
assignment code. Also your description said the text files had data starting
with Monday, but the first tsv() had Sunday data.

See how you go with this code.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com
 
F

freeup86

HI,

Please post VBA questions in the Programming ng from now on as that is the
place to go for all things programming re Project.

I would code the solution as:

Option Explicit

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:\Temp\DeleteMe.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 R As Resource
    Dim A As Assignment
    Dim TheAssignment As Assignment
    Dim TheTask As Task
    Dim TSVS As TimeScaleValues

' Check that the resource exists in the project
    Set R = Nothing
    Set R = ActiveProject.Resources(ResourceName)
    If R Is Nothing Then
        MsgBox ("Resource Name " + ResourceName + " does not exist in the
project")
        End
    End If

    ' Search active project for the specified task.
    Set TheTask = Nothing
    Set TheTask = ActiveProject.Tasks.UniqueId(UniqueId)

    ' If task doesn't exist, display error and quit Sub procedure.
    If TheTask Is Nothing Then
        MsgBox ("Task Unique Id " & UniqueId & " not found.")
        End
    End If

' Search to see if the resource has been assigned to this task
    Set TheAssignment = Nothing
    For Each A In TheTask.Assignments
        If A.ResourceName = ResourceName Then
            Set TheAssignment = A
        End If
    Next A

' If the resource doesn't exist in the assignment, then assign the Resource
    If TheAssignment Is Nothing Then
        Set TheAssignment = TheTask.Assignments.Add(ResourceID:=R.ID,
Units:=0)
    End If

' Set the actual work field of the assignment
    Set TSVS = TheAssignment.TimeScaleData(SDate, SDate + 7,
pjAssignmentTimescaledActualWork, pjTimescaleDays, 1)

    TSVS(1).Value = Mon * 60
    TSVS(2).Value = Tue * 60
    TSVS(3).Value = Wed * 60
    TSVS(4).Value = Thu * 60
    TSVS(5).Value = Fri * 60
    TSVS(6).Value = Sat * 60
    TSVS(7).Value = Sun * 60
End Sub

You need to use a collection with TimescaleData, you used tsv instead of
tsvs. I've reduced the code by simplifying the find task, resource and
assignment code. Also your description said the text files had data starting
with Monday, but the first tsv() had Sunday data.

See how you go with this code.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:http://www.projectvbabook.com































- Show quoted text -
Click to expand...

Mr. Gill,

Thank you for replying and doing so very promptly and I apologize for
posting in the wrong place- I have never posted on any board before.
I took your suggestions for the code, but I am still getting the same
error: "Run Time Error 1101: The Argument is not valid" When it
reaches the first TSVS(I).Value.
 
R

Rod Gill

It worked fine for me with a one line text file. Are you certain that you
are inputting a valid date? Single step thru the code and make sure there
are no errors after running the Set tsvs line. Hover your mouse over each
variable as you single step, there must be a data problem somewhere.

Once the tsvs variable has been set, in the immediate window what do you get
if you type:

?tsvs.count

it should be 7

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com



HI,

Please post VBA questions in the Programming ng from now on as that is the
place to go for all things programming re Project.

I would code the solution as:

Option Explicit

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:\Temp\DeleteMe.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 R As Resource
Dim A As Assignment
Dim TheAssignment As Assignment
Dim TheTask As Task
Dim TSVS As TimeScaleValues

' Check that the resource exists in the project
Set R = Nothing
Set R = ActiveProject.Resources(ResourceName)
If R Is Nothing Then
MsgBox ("Resource Name " + ResourceName + " does not exist in the
project")
End
End If

' Search active project for the specified task.
Set TheTask = Nothing
Set TheTask = ActiveProject.Tasks.UniqueId(UniqueId)

' If task doesn't exist, display error and quit Sub procedure.
If TheTask Is Nothing Then
MsgBox ("Task Unique Id " & UniqueId & " not found.")
End
End If

' Search to see if the resource has been assigned to this task
Set TheAssignment = Nothing
For Each A In TheTask.Assignments
If A.ResourceName = ResourceName Then
Set TheAssignment = A
End If
Next A

' If the resource doesn't exist in the assignment, then assign the
Resource
If TheAssignment Is Nothing Then
Set TheAssignment = TheTask.Assignments.Add(ResourceID:=R.ID,
Units:=0)
End If

' Set the actual work field of the assignment
Set TSVS = TheAssignment.TimeScaleData(SDate, SDate + 7,
pjAssignmentTimescaledActualWork, pjTimescaleDays, 1)

TSVS(1).Value = Mon * 60
TSVS(2).Value = Tue * 60
TSVS(3).Value = Wed * 60
TSVS(4).Value = Thu * 60
TSVS(5).Value = Fri * 60
TSVS(6).Value = Sat * 60
TSVS(7).Value = Sun * 60
End Sub

You need to use a collection with TimescaleData, you used tsv instead of
tsvs. I've reduced the code by simplifying the find task, resource and
assignment code. Also your description said the text files had data
starting
with Monday, but the first tsv() had Sunday data.

See how you go with this code.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:http://www.projectvbabook.com































- Show quoted text -
Click to expand...

Mr. Gill,

Thank you for replying and doing so very promptly and I apologize for
posting in the wrong place- I have never posted on any board before.
I took your suggestions for the code, but I am still getting the same
error: "Run Time Error 1101: The Argument is not valid" When it
reaches the first TSVS(I).Value.
 
F

freeup86

It worked fine for me with a one line text file. Are you certain that you
are inputting a valid date? Single step thru the code and make sure there
are no errors after running the Set tsvs line. Hover your mouse over each
variable as you single step, there must be a data problem somewhere.

Once the tsvs variable has been set, in the immediate window what do you get
if you type:

?tsvs.count

it should be 7

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:http://www.projectvbabook.com





























Mr. Gill,

Thank you for replying and doing so very promptly and I apologize for
posting in the wrong place- I have never posted on any board before.
I took your suggestions for the code, but I am still getting the same
error: "Run Time Error 1101: The Argument is not valid" When it
reaches the first TSVS(I).Value.
Click to expand...

Mr Gill,

Thank you for all of your responses. When I use "?TSVS.Count" I do
get 7 in the immediate window. Unfortunately I still get the same
error when i step to TSVS(1).Value

Could you show me what you type into your text file - DeleteMe.txt?

Thank you for all you help so far. You are the best resource I have
found around here. I just bought your VBA Programming for Microsoft
Project book earlier today- from skimming through it looks really good.
 
R

Rod Gill

How can I refuse a judge of good books!!

My test data is:

1,"Res",#23/6/2008#,1,2,3,4,5,0,0

My local date format is d/m/yy

The fact that you have a count of 7 suggest two dates a week apart have been
accepted.

Finally I assume you have TSVS(1).Value rather than TSVS(I).Value (numeric
one rather than capital I)?

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com
 
M

Mike Glen

Hi ,

Just for the record, the project programming group is called
microsoft.public.project.developer newsgroup. Please see FAQ Item: 24.
Project Newsgroups. FAQs, companion products and other useful Project
information can be seen at this web address:
http://project.mvps.org/faqs.htm

Mike Glen
Project MVP
 
F

freeup86

How can I refuse a judge of good books!!

My test data is:

1,"Res",#23/6/2008#,1,2,3,4,5,0,0

My local date format is d/m/yy

The fact that you have a count of 7 suggest two dates a week apart have been
accepted.

Finally I assume you have TSVS(1).Value rather than TSVS(I).Value (numeric
one rather than capital I)?

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:http://www.projectvbabook.com






...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...

Mr Gill.

It finally worked. I was up all night yesterday and couldn't make it
work. Then this morning I just came in to the office and somehow it
worked. I changed the date format first and I thought that was why it
worked, but then I changed it back and it still works.

Thanks again for all your help.

-Larry
 
R

Rod Gill

No problem, happy coding!

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com



How can I refuse a judge of good books!!

My test data is:

1,"Res",#23/6/2008#,1,2,3,4,5,0,0

My local date format is d/m/yy

The fact that you have a count of 7 suggest two dates a week apart have
been
accepted.

Finally I assume you have TSVS(1).Value rather than TSVS(I).Value (numeric
one rather than capital I)?

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:http://www.projectvbabook.com






...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...

Mr Gill.

It finally worked. I was up all night yesterday and couldn't make it
work. Then this morning I just came in to the office and somehow it
worked. I changed the date format first and I thought that was why it
worked, but then I changed it back and it still works.

Thanks again for all your help.

-Larry
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Timescalevalue update gives error 1101 0
Working outlook via vba code with xlsx - error 0
VBA - exporting serial letters to single documents 0
Need help modifying code 0
Macro Expert's 1
Simple Copy to and From Cells With IF statement. 3
TimescaleData 5
About Lag Time 2

Top