Import rates from excel via macro

J

John4bank

I am trying to import employee rates from Excel into Project. The Excel file
has a column of rates and next to it a column of employee IDs. I want to
compare the EmpID fields in Project to the EmpID in fields in Excel and then
copy over the rate from Excel that corresponds to the EmpID.

I am not sure how to pull the data over from Excel though. I have included
the code that I have written so far. My problem is where the "If x = SEARCH
EXCEL LIST OF EMPLOYEE NUMBERS Then" appears in the code below. Can someone
please let me know of how I can pull this information into Project via a
macro?

Thanks,

John


Sub ImportRates()

'First enter date for rates to take affect
'Call emp# in project scan for same emp# in Excel if it exists update Std.
Rate
'If it doesn't appear in Excel highlight Red
'if it is a Material set cost to $1.06


Dim Rsr As Resource
Dim MRsr As Resources
Dim iDate As Date
Dim y As String
Dim x As String
Dim z As Integer
Dim ExcelRate As Integer
Dim BRate As Integer 'burdened base rate
Dim NRate As Integer 'no burden base rate
Dim Location As String

Set MRsr = ActiveProject.Resources

'input box to ask the user to enter the date rates should take affect
iDate = InputBox("Please enter the date the base rate should take affect.",
"Date Input", "mm/dd/yyyy")
'input box to ask the user to enter the rates for ODCs and Travel
BRate = InputBox("Please enter the rate for ODCs and Travel.", "Rate Input",
"i.e. 1.06")
'input box to ask the user to enter the rates for Materials, Subks, etc
NRate = InputBox("Please enter the rate to be used for Materials, Subks,
Consultants, and Temps.", "Rate Input", "i.e. 1.00")

'Use open Excel file
Dim xlApp As Excel.Application
Dim xlrange As Excel.Range
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then
MsgBox "Failed to connect to Excel, macro ended"
Else
MsgBox "Connected to exisitng Excel Application"
End If
xlApp.Visible = True
'Tidy up
xlApp.UserControl = True
Set xlApp = Nothing

'remove any filters on the resource sheet
If ActiveProject.AutoFilter Then
ActiveProject.AutoFilter = False ' removes any autofilters. If the list
is filtered it won't run on the undisplayed items.
End If

'go through each resource updating the Std. Rate
For Each Rsr In MRsr
y = 0 ' setting y to zero to clear it out
x = 0 ' setting x to zero to clear it out
p = Rsr.ID ' pulling in the resource id and setting it to p
SelectRow p, rowrelative:=False
SelectResourceField Row:=p, rowrelative:=False, Column:="ResourceType"
y = ActiveCell.Text 'setting y to whatever text appears in the
"ResourceType" field on the resource sheet for each individual resource
If "SAIC" = y Then ' only search for EmployeeNum of ResourceType "SAIC"
SelectRow p, rowrelative:=False
SelectResourceField Row:=p, rowrelative:=False, Column:="EmployeeNum"
x = ActiveCell.Text ' set x to employee number

If x = SEARCH EXCEL LIST OF EMPLOYEE NUMBERS Then
Rsr.CostRateTables("A").PayRates.Add iDate, ExcelRate,
ExcelRate, "0" ' changes "Std. Rate" of resources on the date entered in the
first inputbox and sets it to the rate in Excel that matches with the
employeeNum
End If
End If
If ("Travel" = y Or "ODCs" = y) Then
Rsr.CostRateTables("A").PayRates.Add iDate, BRate, BRate, "0" '
changes "Std. Rate" of resources on the date entered in the first inputbox
and sets it to the rate entered for Travel and ODCs
End If
If ("Subk" = y Or "Consultant" = y Or "Temp" = y Or "Material") Then
Rsr.CostRateTables("A").PayRates.Add iDate, NRate, NRate, "0" '
changes "Std. Rate" of resources on the date entered in the first inputbox
and sets it to the rate entered for Subk, Material, Consultant, Temp
End If
Next Rsr

End Sub
 
J

John

John4bank said:
I am trying to import employee rates from Excel into Project. The Excel file
has a column of rates and next to it a column of employee IDs. I want to
compare the EmpID fields in Project to the EmpID in fields in Excel and then
copy over the rate from Excel that corresponds to the EmpID.

I am not sure how to pull the data over from Excel though. I have included
the code that I have written so far. My problem is where the "If x = SEARCH
EXCEL LIST OF EMPLOYEE NUMBERS Then" appears in the code below. Can someone
please let me know of how I can pull this information into Project via a
macro?

Thanks,

John


Sub ImportRates()

'First enter date for rates to take affect
'Call emp# in project scan for same emp# in Excel if it exists update Std.
Rate
'If it doesn't appear in Excel highlight Red
'if it is a Material set cost to $1.06


Dim Rsr As Resource
Dim MRsr As Resources
Dim iDate As Date
Dim y As String
Dim x As String
Dim z As Integer
Dim ExcelRate As Integer
Dim BRate As Integer 'burdened base rate
Dim NRate As Integer 'no burden base rate
Dim Location As String

Set MRsr = ActiveProject.Resources

'input box to ask the user to enter the date rates should take affect
iDate = InputBox("Please enter the date the base rate should take affect.",
"Date Input", "mm/dd/yyyy")
'input box to ask the user to enter the rates for ODCs and Travel
BRate = InputBox("Please enter the rate for ODCs and Travel.", "Rate Input",
"i.e. 1.06")
'input box to ask the user to enter the rates for Materials, Subks, etc
NRate = InputBox("Please enter the rate to be used for Materials, Subks,
Consultants, and Temps.", "Rate Input", "i.e. 1.00")

'Use open Excel file
Dim xlApp As Excel.Application
Dim xlrange As Excel.Range
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then
MsgBox "Failed to connect to Excel, macro ended"
Else
MsgBox "Connected to exisitng Excel Application"
End If
xlApp.Visible = True
'Tidy up
xlApp.UserControl = True
Set xlApp = Nothing

'remove any filters on the resource sheet
If ActiveProject.AutoFilter Then
ActiveProject.AutoFilter = False ' removes any autofilters. If the list
is filtered it won't run on the undisplayed items.
End If

'go through each resource updating the Std. Rate
For Each Rsr In MRsr
y = 0 ' setting y to zero to clear it out
x = 0 ' setting x to zero to clear it out
p = Rsr.ID ' pulling in the resource id and setting it to p
SelectRow p, rowrelative:=False
SelectResourceField Row:=p, rowrelative:=False, Column:="ResourceType"
y = ActiveCell.Text 'setting y to whatever text appears in the
"ResourceType" field on the resource sheet for each individual resource
If "SAIC" = y Then ' only search for EmployeeNum of ResourceType "SAIC"
SelectRow p, rowrelative:=False
SelectResourceField Row:=p, rowrelative:=False, Column:="EmployeeNum"
x = ActiveCell.Text ' set x to employee number

If x = SEARCH EXCEL LIST OF EMPLOYEE NUMBERS Then
Rsr.CostRateTables("A").PayRates.Add iDate, ExcelRate,
ExcelRate, "0" ' changes "Std. Rate" of resources on the date entered in the
first inputbox and sets it to the rate in Excel that matches with the
employeeNum
End If
End If
If ("Travel" = y Or "ODCs" = y) Then
Rsr.CostRateTables("A").PayRates.Add iDate, BRate, BRate, "0" '
changes "Std. Rate" of resources on the date entered in the first inputbox
and sets it to the rate entered for Travel and ODCs
End If
If ("Subk" = y Or "Consultant" = y Or "Temp" = y Or "Material") Then
Rsr.CostRateTables("A").PayRates.Add iDate, NRate, NRate, "0" '
changes "Std. Rate" of resources on the date entered in the first inputbox
and sets it to the rate entered for Subk, Material, Consultant, Temp
End If
Next Rsr

End Sub
Click to expand...
John,
It looks like you're off to a good start with your macro. I'll offer a
few comments that hopefully will help you finish.

If I were approaching this task I would probably open the desired Excel
file and then read all the data, (Employee ID and rates), into a couple
arrays. It would be a lot easier if the Excel data is already sorted by
Employee ID. If not, it can be done when the arrays are populated but
that requires some additional coding to do the sorting in VBA. I have
examples of VBA array sorting routines if you're interested. You don't
have to use arrays, you could query the Excel data directly but if the
Excel data is not pre-sorted you will have to use a search command and
that is very inefficient and will really slow down the code.

Once the arrays are created you can then cycle through each resource in
project, make a comparison with the array data and then take whatever
action you want. I will assume that the Employee ID is independent of
the Resource ID assigned by Project (i.e. the Employee ID is in a spare
resource field). The Project Resource Sheet should be sorted by Employee
ID so comparison with the arrays will be easy and fast.

I see you open Excel but I don't see anywhere where you then open a
specific Excel Workbook. Perhaps you've already manually opened the
desired Excel workbook so all you are doing is creating a VBA object of
the open workbook.

The bulk of your main loop (For Each Rsr in MRsr) uses foreground
processing. Although that will work, the construct of your code isn't
doing what you think it is. Let me explain. When you record a macro the
code is basically built up of keystrokes to actively select various
actions, pieces of data, etc. That is foreground processing - working
with actively selected information. A much more efficient way to write
code is by using background processing. In background processing the
code works directly on the objects in the underlying database. It
doesn't matter what shows on the screen because the code in not
operating on actively selected objects. Sometimes it is desirable to use
a combination of foreground and background processing and due to the
fact that the resource ID and your Employee ID are likely not equal or
in lock-step, combination processing is what I recommend. Here is the
difference in syntax construct: For Each Rsr in MRsr, will cycle through
each sequential resource in the active project regardless of whether a
filter is applied or even if the current view is a task view. On the
other hand, if the following object is created:
SelectResourceColumn
Set MRsr=ActiveSelection.Resources, then For Each Rsr in MRsr, will
cycle through whatever resources were shown (i.e. selected) when the
object was created. For example, a filtered group of resources or a
sorted group of resources. In your case, you probably want a sorted
resource group to put the Employee IDs in ascending order.

Once the sorted resource collection object is created you can access
elements of that object using background processing. You do NOT need to
access elements by selecting rows, columns, fields, etc. For example,
let's say you've used the spare resource Text1 field to contain the
Employee ID and now you want to compare that ID with the first element
of the array created from the Excel Employee ID data. The syntax might
look something like this. (Note: "i" is the array index and needs to be
incremented within the loop doing the comparing to keep things in
"lock-step")
If Rsr.Text1 = Emp(i) Then [go do something with the rate]

Lots of verbiage here, hope it helps.

John
Project MVP
 
J

jack dahlgren

John,

Very good points.
I was going to write a response with reading excel into an array, but the
Project 2010 beta seems to have done something to my ability to read and
write to excel in VBA.

Depending on the size of the data, sorting the array is probably not
necessary.
Iterating through the array until you find a matching value is probably
going to be reasonably quick.
For an unsorted array, the number of items you need to read is n/2 * n

Sorting the array would take a fair number of reads and writes to begin
with.

-Jack

John said:
John4bank said:
I am trying to import employee rates from Excel into Project. The Excel
file
has a column of rates and next to it a column of employee IDs. I want to
compare the EmpID fields in Project to the EmpID in fields in Excel and
then
copy over the rate from Excel that corresponds to the EmpID.

I am not sure how to pull the data over from Excel though. I have
included
the code that I have written so far. My problem is where the "If x =
SEARCH
EXCEL LIST OF EMPLOYEE NUMBERS Then" appears in the code below. Can
someone
please let me know of how I can pull this information into Project via a
macro?

Thanks,

John


Sub ImportRates()

'First enter date for rates to take affect
'Call emp# in project scan for same emp# in Excel if it exists update
Std.
Rate
'If it doesn't appear in Excel highlight Red
'if it is a Material set cost to $1.06


Dim Rsr As Resource
Dim MRsr As Resources
Dim iDate As Date
Dim y As String
Dim x As String
Dim z As Integer
Dim ExcelRate As Integer
Dim BRate As Integer 'burdened base rate
Dim NRate As Integer 'no burden base rate
Dim Location As String

Set MRsr = ActiveProject.Resources

'input box to ask the user to enter the date rates should take affect
iDate = InputBox("Please enter the date the base rate should take
affect.",
"Date Input", "mm/dd/yyyy")
'input box to ask the user to enter the rates for ODCs and Travel
BRate = InputBox("Please enter the rate for ODCs and Travel.", "Rate
Input",
"i.e. 1.06")
'input box to ask the user to enter the rates for Materials, Subks, etc
NRate = InputBox("Please enter the rate to be used for Materials, Subks,
Consultants, and Temps.", "Rate Input", "i.e. 1.00")

'Use open Excel file
Dim xlApp As Excel.Application
Dim xlrange As Excel.Range
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then
MsgBox "Failed to connect to Excel, macro ended"
Else
MsgBox "Connected to exisitng Excel Application"
End If
xlApp.Visible = True
'Tidy up
xlApp.UserControl = True
Set xlApp = Nothing

'remove any filters on the resource sheet
If ActiveProject.AutoFilter Then
ActiveProject.AutoFilter = False ' removes any autofilters. If the
list
is filtered it won't run on the undisplayed items.
End If

'go through each resource updating the Std. Rate
For Each Rsr In MRsr
y = 0 ' setting y to zero to clear it out
x = 0 ' setting x to zero to clear it out
p = Rsr.ID ' pulling in the resource id and setting it to p
SelectRow p, rowrelative:=False
SelectResourceField Row:=p, rowrelative:=False,
Column:="ResourceType"
y = ActiveCell.Text 'setting y to whatever text appears in the
"ResourceType" field on the resource sheet for each individual resource
If "SAIC" = y Then ' only search for EmployeeNum of ResourceType
"SAIC"
SelectRow p, rowrelative:=False
SelectResourceField Row:=p, rowrelative:=False,
Column:="EmployeeNum"
x = ActiveCell.Text ' set x to employee number

If x = SEARCH EXCEL LIST OF EMPLOYEE NUMBERS Then
Rsr.CostRateTables("A").PayRates.Add iDate, ExcelRate,
ExcelRate, "0" ' changes "Std. Rate" of resources on the date entered in
the
first inputbox and sets it to the rate in Excel that matches with the
employeeNum
End If
End If
If ("Travel" = y Or "ODCs" = y) Then
Rsr.CostRateTables("A").PayRates.Add iDate, BRate, BRate, "0" '
changes "Std. Rate" of resources on the date entered in the first
inputbox
and sets it to the rate entered for Travel and ODCs
End If
If ("Subk" = y Or "Consultant" = y Or "Temp" = y Or "Material") Then
Rsr.CostRateTables("A").PayRates.Add iDate, NRate, NRate, "0" '
changes "Std. Rate" of resources on the date entered in the first
inputbox
and sets it to the rate entered for Subk, Material, Consultant, Temp
End If
Next Rsr

End Sub
Click to expand...
John,
It looks like you're off to a good start with your macro. I'll offer a
few comments that hopefully will help you finish.

If I were approaching this task I would probably open the desired Excel
file and then read all the data, (Employee ID and rates), into a couple
arrays. It would be a lot easier if the Excel data is already sorted by
Employee ID. If not, it can be done when the arrays are populated but
that requires some additional coding to do the sorting in VBA. I have
examples of VBA array sorting routines if you're interested. You don't
have to use arrays, you could query the Excel data directly but if the
Excel data is not pre-sorted you will have to use a search command and
that is very inefficient and will really slow down the code.

Once the arrays are created you can then cycle through each resource in
project, make a comparison with the array data and then take whatever
action you want. I will assume that the Employee ID is independent of
the Resource ID assigned by Project (i.e. the Employee ID is in a spare
resource field). The Project Resource Sheet should be sorted by Employee
ID so comparison with the arrays will be easy and fast.

I see you open Excel but I don't see anywhere where you then open a
specific Excel Workbook. Perhaps you've already manually opened the
desired Excel workbook so all you are doing is creating a VBA object of
the open workbook.

The bulk of your main loop (For Each Rsr in MRsr) uses foreground
processing. Although that will work, the construct of your code isn't
doing what you think it is. Let me explain. When you record a macro the
code is basically built up of keystrokes to actively select various
actions, pieces of data, etc. That is foreground processing - working
with actively selected information. A much more efficient way to write
code is by using background processing. In background processing the
code works directly on the objects in the underlying database. It
doesn't matter what shows on the screen because the code in not
operating on actively selected objects. Sometimes it is desirable to use
a combination of foreground and background processing and due to the
fact that the resource ID and your Employee ID are likely not equal or
in lock-step, combination processing is what I recommend. Here is the
difference in syntax construct: For Each Rsr in MRsr, will cycle through
each sequential resource in the active project regardless of whether a
filter is applied or even if the current view is a task view. On the
other hand, if the following object is created:
SelectResourceColumn
Set MRsr=ActiveSelection.Resources, then For Each Rsr in MRsr, will
cycle through whatever resources were shown (i.e. selected) when the
object was created. For example, a filtered group of resources or a
sorted group of resources. In your case, you probably want a sorted
resource group to put the Employee IDs in ascending order.

Once the sorted resource collection object is created you can access
elements of that object using background processing. You do NOT need to
access elements by selecting rows, columns, fields, etc. For example,
let's say you've used the spare resource Text1 field to contain the
Employee ID and now you want to compare that ID with the first element
of the array created from the Excel Employee ID data. The syntax might
look something like this. (Note: "i" is the array index and needs to be
incremented within the loop doing the comparing to keep things in
"lock-step")
If Rsr.Text1 = Emp(i) Then [go do something with the rate]

Lots of verbiage here, hope it helps.

John
Project MVP
Click to expand...
 
J

John

jack dahlgren said:
John,

Very good points.
I was going to write a response with reading excel into an array, but the
Project 2010 beta seems to have done something to my ability to read and
write to excel in VBA.

Depending on the size of the data, sorting the array is probably not
necessary.
Iterating through the array until you find a matching value is probably
going to be reasonably quick.
For an unsorted array, the number of items you need to read is n/2 * n

Sorting the array would take a fair number of reads and writes to begin
with.

-Jack
Click to expand...

Jack,
Maybe Microsoft is planning on eventually deleting VBA. They did so with
the latest release of Office for the Mac - Excel for Mac no longer
supports VBA.

With regard to sorting versus "iterate and search", I've found that a
simple sorting routine to rearrange an array runs very fast and once
it's done, using the data in the sorted array is essentially trivial.
However in a few cases I have also used the iterate and search method. I
find that method works well if I'm trying to build an array where the
body of elements going into the array has several duplicates.

John
Project MVP
John said:
John4bank said:
I am trying to import employee rates from Excel into Project. The Excel
file
has a column of rates and next to it a column of employee IDs. I want to
compare the EmpID fields in Project to the EmpID in fields in Excel and
then
copy over the rate from Excel that corresponds to the EmpID.

I am not sure how to pull the data over from Excel though. I have
included
the code that I have written so far. My problem is where the "If x =
SEARCH
EXCEL LIST OF EMPLOYEE NUMBERS Then" appears in the code below. Can
someone
please let me know of how I can pull this information into Project via a
macro?

Thanks,

John


Sub ImportRates()

'First enter date for rates to take affect
'Call emp# in project scan for same emp# in Excel if it exists update
Std.
Rate
'If it doesn't appear in Excel highlight Red
'if it is a Material set cost to $1.06


Dim Rsr As Resource
Dim MRsr As Resources
Dim iDate As Date
Dim y As String
Dim x As String
Dim z As Integer
Dim ExcelRate As Integer
Dim BRate As Integer 'burdened base rate
Dim NRate As Integer 'no burden base rate
Dim Location As String

Set MRsr = ActiveProject.Resources

'input box to ask the user to enter the date rates should take affect
iDate = InputBox("Please enter the date the base rate should take
affect.",
"Date Input", "mm/dd/yyyy")
'input box to ask the user to enter the rates for ODCs and Travel
BRate = InputBox("Please enter the rate for ODCs and Travel.", "Rate
Input",
"i.e. 1.06")
'input box to ask the user to enter the rates for Materials, Subks, etc
NRate = InputBox("Please enter the rate to be used for Materials, Subks,
Consultants, and Temps.", "Rate Input", "i.e. 1.00")

'Use open Excel file
Dim xlApp As Excel.Application
Dim xlrange As Excel.Range
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then
MsgBox "Failed to connect to Excel, macro ended"
Else
MsgBox "Connected to exisitng Excel Application"
End If
xlApp.Visible = True
'Tidy up
xlApp.UserControl = True
Set xlApp = Nothing

'remove any filters on the resource sheet
If ActiveProject.AutoFilter Then
ActiveProject.AutoFilter = False ' removes any autofilters. If the
list
is filtered it won't run on the undisplayed items.
End If

'go through each resource updating the Std. Rate
For Each Rsr In MRsr
y = 0 ' setting y to zero to clear it out
x = 0 ' setting x to zero to clear it out
p = Rsr.ID ' pulling in the resource id and setting it to p
SelectRow p, rowrelative:=False
SelectResourceField Row:=p, rowrelative:=False,
Column:="ResourceType"
y = ActiveCell.Text 'setting y to whatever text appears in the
"ResourceType" field on the resource sheet for each individual resource
If "SAIC" = y Then ' only search for EmployeeNum of ResourceType
"SAIC"
SelectRow p, rowrelative:=False
SelectResourceField Row:=p, rowrelative:=False,
Column:="EmployeeNum"
x = ActiveCell.Text ' set x to employee number

If x = SEARCH EXCEL LIST OF EMPLOYEE NUMBERS Then
Rsr.CostRateTables("A").PayRates.Add iDate, ExcelRate,
ExcelRate, "0" ' changes "Std. Rate" of resources on the date entered in
the
first inputbox and sets it to the rate in Excel that matches with the
employeeNum
End If
End If
If ("Travel" = y Or "ODCs" = y) Then
Rsr.CostRateTables("A").PayRates.Add iDate, BRate, BRate, "0" '
changes "Std. Rate" of resources on the date entered in the first
inputbox
and sets it to the rate entered for Travel and ODCs
End If
If ("Subk" = y Or "Consultant" = y Or "Temp" = y Or "Material") Then
Rsr.CostRateTables("A").PayRates.Add iDate, NRate, NRate, "0" '
changes "Std. Rate" of resources on the date entered in the first
inputbox
and sets it to the rate entered for Subk, Material, Consultant, Temp
End If
Next Rsr

End Sub
Click to expand...
John,
It looks like you're off to a good start with your macro. I'll offer a
few comments that hopefully will help you finish.

If I were approaching this task I would probably open the desired Excel
file and then read all the data, (Employee ID and rates), into a couple
arrays. It would be a lot easier if the Excel data is already sorted by
Employee ID. If not, it can be done when the arrays are populated but
that requires some additional coding to do the sorting in VBA. I have
examples of VBA array sorting routines if you're interested. You don't
have to use arrays, you could query the Excel data directly but if the
Excel data is not pre-sorted you will have to use a search command and
that is very inefficient and will really slow down the code.

Once the arrays are created you can then cycle through each resource in
project, make a comparison with the array data and then take whatever
action you want. I will assume that the Employee ID is independent of
the Resource ID assigned by Project (i.e. the Employee ID is in a spare
resource field). The Project Resource Sheet should be sorted by Employee
ID so comparison with the arrays will be easy and fast.

I see you open Excel but I don't see anywhere where you then open a
specific Excel Workbook. Perhaps you've already manually opened the
desired Excel workbook so all you are doing is creating a VBA object of
the open workbook.

The bulk of your main loop (For Each Rsr in MRsr) uses foreground
processing. Although that will work, the construct of your code isn't
doing what you think it is. Let me explain. When you record a macro the
code is basically built up of keystrokes to actively select various
actions, pieces of data, etc. That is foreground processing - working
with actively selected information. A much more efficient way to write
code is by using background processing. In background processing the
code works directly on the objects in the underlying database. It
doesn't matter what shows on the screen because the code in not
operating on actively selected objects. Sometimes it is desirable to use
a combination of foreground and background processing and due to the
fact that the resource ID and your Employee ID are likely not equal or
in lock-step, combination processing is what I recommend. Here is the
difference in syntax construct: For Each Rsr in MRsr, will cycle through
each sequential resource in the active project regardless of whether a
filter is applied or even if the current view is a task view. On the
other hand, if the following object is created:
SelectResourceColumn
Set MRsr=ActiveSelection.Resources, then For Each Rsr in MRsr, will
cycle through whatever resources were shown (i.e. selected) when the
object was created. For example, a filtered group of resources or a
sorted group of resources. In your case, you probably want a sorted
resource group to put the Employee IDs in ascending order.

Once the sorted resource collection object is created you can access
elements of that object using background processing. You do NOT need to
access elements by selecting rows, columns, fields, etc. For example,
let's say you've used the spare resource Text1 field to contain the
Employee ID and now you want to compare that ID with the first element
of the array created from the Excel Employee ID data. The syntax might
look something like this. (Note: "i" is the array index and needs to be
incremented within the loop doing the comparing to keep things in
"lock-step")
If Rsr.Text1 = Emp(i) Then [go do something with the rate]

Lots of verbiage here, hope it helps.

John
Project MVP
Click to expand...
Click to expand...
 
J

John4bank

Thanks for your help.

John said:
jack dahlgren said:
John,

Very good points.
I was going to write a response with reading excel into an array, but the
Project 2010 beta seems to have done something to my ability to read and
write to excel in VBA.

Depending on the size of the data, sorting the array is probably not
necessary.
Iterating through the array until you find a matching value is probably
going to be reasonably quick.
For an unsorted array, the number of items you need to read is n/2 * n

Sorting the array would take a fair number of reads and writes to begin
with.

-Jack
Click to expand...

Jack,
Maybe Microsoft is planning on eventually deleting VBA. They did so with
the latest release of Office for the Mac - Excel for Mac no longer
supports VBA.

With regard to sorting versus "iterate and search", I've found that a
simple sorting routine to rearrange an array runs very fast and once
it's done, using the data in the sorted array is essentially trivial.
However in a few cases I have also used the iterate and search method. I
find that method works well if I'm trying to build an array where the
body of elements going into the array has several duplicates.

John
Project MVP
John said:
I am trying to import employee rates from Excel into Project. The Excel
file
has a column of rates and next to it a column of employee IDs. I want to
compare the EmpID fields in Project to the EmpID in fields in Excel and
then
copy over the rate from Excel that corresponds to the EmpID.

I am not sure how to pull the data over from Excel though. I have
included
the code that I have written so far. My problem is where the "If x =
SEARCH
EXCEL LIST OF EMPLOYEE NUMBERS Then" appears in the code below. Can
someone
please let me know of how I can pull this information into Project via a
macro?

Thanks,

John


Sub ImportRates()

'First enter date for rates to take affect
'Call emp# in project scan for same emp# in Excel if it exists update
Std.
Rate
'If it doesn't appear in Excel highlight Red
'if it is a Material set cost to $1.06


Dim Rsr As Resource
Dim MRsr As Resources
Dim iDate As Date
Dim y As String
Dim x As String
Dim z As Integer
Dim ExcelRate As Integer
Dim BRate As Integer 'burdened base rate
Dim NRate As Integer 'no burden base rate
Dim Location As String

Set MRsr = ActiveProject.Resources

'input box to ask the user to enter the date rates should take affect
iDate = InputBox("Please enter the date the base rate should take
affect.",
"Date Input", "mm/dd/yyyy")
'input box to ask the user to enter the rates for ODCs and Travel
BRate = InputBox("Please enter the rate for ODCs and Travel.", "Rate
Input",
"i.e. 1.06")
'input box to ask the user to enter the rates for Materials, Subks, etc
NRate = InputBox("Please enter the rate to be used for Materials, Subks,
Consultants, and Temps.", "Rate Input", "i.e. 1.00")

'Use open Excel file
Dim xlApp As Excel.Application
Dim xlrange As Excel.Range
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then
MsgBox "Failed to connect to Excel, macro ended"
Else
MsgBox "Connected to exisitng Excel Application"
End If
xlApp.Visible = True
'Tidy up
xlApp.UserControl = True
Set xlApp = Nothing

'remove any filters on the resource sheet
If ActiveProject.AutoFilter Then
ActiveProject.AutoFilter = False ' removes any autofilters. If the
list
is filtered it won't run on the undisplayed items.
End If

'go through each resource updating the Std. Rate
For Each Rsr In MRsr
y = 0 ' setting y to zero to clear it out
x = 0 ' setting x to zero to clear it out
p = Rsr.ID ' pulling in the resource id and setting it to p
SelectRow p, rowrelative:=False
SelectResourceField Row:=p, rowrelative:=False,
Column:="ResourceType"
y = ActiveCell.Text 'setting y to whatever text appears in the
"ResourceType" field on the resource sheet for each individual resource
If "SAIC" = y Then ' only search for EmployeeNum of ResourceType
"SAIC"
SelectRow p, rowrelative:=False
SelectResourceField Row:=p, rowrelative:=False,
Column:="EmployeeNum"
x = ActiveCell.Text ' set x to employee number

If x = SEARCH EXCEL LIST OF EMPLOYEE NUMBERS Then
Rsr.CostRateTables("A").PayRates.Add iDate, ExcelRate,
ExcelRate, "0" ' changes "Std. Rate" of resources on the date entered in
the
first inputbox and sets it to the rate in Excel that matches with the
employeeNum
End If
End If
If ("Travel" = y Or "ODCs" = y) Then
Rsr.CostRateTables("A").PayRates.Add iDate, BRate, BRate, "0" '
changes "Std. Rate" of resources on the date entered in the first
inputbox
and sets it to the rate entered for Travel and ODCs
End If
If ("Subk" = y Or "Consultant" = y Or "Temp" = y Or "Material") Then
Rsr.CostRateTables("A").PayRates.Add iDate, NRate, NRate, "0" '
changes "Std. Rate" of resources on the date entered in the first
inputbox
and sets it to the rate entered for Subk, Material, Consultant, Temp
End If
Next Rsr

End Sub
John,
It looks like you're off to a good start with your macro. I'll offer a
few comments that hopefully will help you finish.

If I were approaching this task I would probably open the desired Excel
file and then read all the data, (Employee ID and rates), into a couple
arrays. It would be a lot easier if the Excel data is already sorted by
Employee ID. If not, it can be done when the arrays are populated but
that requires some additional coding to do the sorting in VBA. I have
examples of VBA array sorting routines if you're interested. You don't
have to use arrays, you could query the Excel data directly but if the
Excel data is not pre-sorted you will have to use a search command and
that is very inefficient and will really slow down the code.

Once the arrays are created you can then cycle through each resource in
project, make a comparison with the array data and then take whatever
action you want. I will assume that the Employee ID is independent of
the Resource ID assigned by Project (i.e. the Employee ID is in a spare
resource field). The Project Resource Sheet should be sorted by Employee
ID so comparison with the arrays will be easy and fast.

I see you open Excel but I don't see anywhere where you then open a
specific Excel Workbook. Perhaps you've already manually opened the
desired Excel workbook so all you are doing is creating a VBA object of
the open workbook.

The bulk of your main loop (For Each Rsr in MRsr) uses foreground
processing. Although that will work, the construct of your code isn't
doing what you think it is. Let me explain. When you record a macro the
code is basically built up of keystrokes to actively select various
actions, pieces of data, etc. That is foreground processing - working
with actively selected information. A much more efficient way to write
code is by using background processing. In background processing the
code works directly on the objects in the underlying database. It
doesn't matter what shows on the screen because the code in not
operating on actively selected objects. Sometimes it is desirable to use
a combination of foreground and background processing and due to the
fact that the resource ID and your Employee ID are likely not equal or
in lock-step, combination processing is what I recommend. Here is the
difference in syntax construct: For Each Rsr in MRsr, will cycle through
each sequential resource in the active project regardless of whether a
filter is applied or even if the current view is a task view. On the
other hand, if the following object is created:
SelectResourceColumn
Set MRsr=ActiveSelection.Resources, then For Each Rsr in MRsr, will
cycle through whatever resources were shown (i.e. selected) when the
object was created. For example, a filtered group of resources or a
sorted group of resources. In your case, you probably want a sorted
resource group to put the Employee IDs in ascending order.

Once the sorted resource collection object is created you can access
elements of that object using background processing. You do NOT need to
access elements by selecting rows, columns, fields, etc. For example,
let's say you've used the spare resource Text1 field to contain the
Employee ID and now you want to compare that ID with the first element
of the array created from the Excel Employee ID data. The syntax might
look something like this. (Note: "i" is the array index and needs to be
incremented within the loop doing the comparing to keep things in
"lock-step")
If Rsr.Text1 = Emp(i) Then [go do something with the rate]

Lots of verbiage here, hope it helps.

John
Project MVP
Click to expand...
Click to expand...
.
Click to expand...
 
J

John4bank

Alright, I now know what to do with the data from Excel, but how do I even
import it to begin with? I do not know how to get the data from excel.
 
J

John

John4bank said:
Alright, I now know what to do with the data from Excel, but how do I even
import it to begin with? I do not know how to get the data from excel.
Click to expand...

John4bank,
Well you don't "import" it at all. Once you have created the Excel
object, it is then just a matter of reading the data in the cell and
using it in the macro code. For example, let's say the first employee ID
in Excel is located in a workbook called "Bob" on sheet 1 in cell A2 and
the rate for that employee is in cell A3. The data for each of those is
read as:
EmpID = xlApp.Workbooks("Bob").Worksheets(1).Range("A2").value
EmpRat = xlApp.Workbooks("Bob").Worksheets(1).Range("A3").value

For convenience you might want to create another object,
Set s = xlApp.Workbooks("Bob").Worksheets(1)

Then the syntax to get you data is,
EmpID = s.Range("A2").value
EmpRat = s.Range("A3").value

By the way, your existing code sets the Excel visible property to true.
Don't need that. As a matter of fact the code will run faster if Excel
is completely in the background (i.e visible = False).

Hope this helps.

If you are fairly new to VBA you might want to consider getting a copy
of fellow MVP, Rod Gill's book on Project VBA. For more information see,
http://www.projectvbabook.com.

John
Project MVP
 

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

Delete row from Cost Rate Table 2
Word macro that can call cells out of Excel for a find and replace 0
Retrieve WRES_ID for a Resource via VBA 5
Paste values from 2 fields to an Excel sheet 4
Sending a mail via outlook excel 2003 0
Resource permissions to deny PMs to see hourly rates 1
Help with Macro in Excel 2003 3
manipulating text file from excel via vba 1

Top