daboid
07-14-2014, 08:52 AM
Hi,
Thank you to those who provide answers on this forum. If this topic has been covered I sure can't find where. I am working with Excel 2010 and Project 2010 (Windows 7). Excel is being used to keep track of people's tasking. There are 13 users each with individual worksheets that are updated every two weeks in a shared, macro-enabled spreadsheet. After the updates are made, I use vba (shown below) to successfully combine all the tasks from each user's worksheet to a new worksheet in the same workbook (initiated with a command button on a worksheet called "All_Tasking"). From there the boss sorts the tasks usually by priority or name then should be able to click a command button to export them to a new MS Project.
In the code below I am able open Project and export the Task Name, Start and End dates, and Resource (I intentionally don't put the duration) but I need help adding columns to Project from Excel.
Please how do I add columns to Project (using the VBA below which is in Excel). One column will be titled "Status" and have either an A (Active), I (Inactive not complete), or C (Complete). Another column will be called "Priority" (1 highest - 5 lowest).
I am not very good with VBA so please provide details. Any help you can give is greatly appreciated. Thank you. I apologize for submitting the following VBA as plain text but after I selected the code I did not find the green VBA emblem above the pane where I was typing.
Option Explicit
Private Sub cmdPrjct_Click()
Dim LDate As String
Dim iCnt As Long
Dim FleDte As String
Dim NumRows As Long
Dim sStrtDte() As Variant
Dim sEndDte() As Variant
Dim TskNm() As Variant
Dim DdLn() As Variant
Dim sRsrcNm() As Variant
Dim sAIC() As Variant ' active, inactive not complete, or complete
Dim ILvl() As Variant ' indent level of task
Dim sPrity() As Variant ' priority 1-5, 1 is the highest, 5 is the lowest priority
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim prApp As MSProject.Application
Dim prProject As MSProject.Project
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets(27) ' the All_Tasking work sheet
wsSheet.Select
wsSheet.Activate
ActiveSheet.Range("A3").Select
Selection.CurrentRegion.Select ' I have two rows of headers
Selection.Offset(2, 0).Resize(Selection.Rows.Count - 2).Select
NumRows = Selection.Rows.Count 'total numbe
NumRows = NumRows + 2
'Populate the variables with values from the worksheet.
With wsSheet
sPrity = .Range("A3:A" & NumRows).Value
sAIC = .Range("B3:B" & NumRows).Value
sStrtDte = .Range("F3:F" & NumRows).Value
sEndDte = .Range("G3:G" & NumRows).Value
TskNm = .Range("I3:I" & NumRows).Value
sRsrcNm = .Range("AB3:AB" & NumRows).Value
ILvl = .Range("AC3:AC" & NumRows).Value
End With
' open MS Project and save with date.
iCnt = 0
LDate = Format(Date, "mm-dd-yyyy")
FleDte = Left(LDate, 2) & "_" & Mid(LDate, 4, 2) & "_" & Right(LDate, 4)
Set prApp = New MSProject.Application
prApp = Shell("C:\Program Files\Microsoft Office\Office14\WINPROJ.EXE")
Set prProject = prApp.ActiveProject
prApp.ActiveProject.SaveAs "C:\Users\Jan\Desktop\Scheduling \Excel_Project\Trial_" & FleDte & ".mpp"
' I would like to add the two columns here. Please help with plenty of detail.
'Add task and other wanted information to MS Project.
With prProject
For iCnt = 1 To NumRows - 2
.Tasks.Add TskNm(iCnt, 1)
With .Tasks(TskNm(iCnt, 1))
' keep formatting (make subtasks; subtasks)
.OutlineLevel = ILvl(iCnt, 1)
.Start = sStrtDte(iCnt, 1)
.Finish = sEndDte(iCnt, 1)
.ResourceNames = sRsrcNm(iCnt, 1)
' .Status = SAIC(iCnt, 1)
' .Priority = sPrity(iCnt, 1)
End With
Next iCnt
End With
'Save MS Project.
With prApp
.FileSave
End With
Set prProject = Nothing
Set prApp = Nothing
End Sub
Thank you for your help. I appreciate your time and expertise.
v/r,
Jan
Thank you to those who provide answers on this forum. If this topic has been covered I sure can't find where. I am working with Excel 2010 and Project 2010 (Windows 7). Excel is being used to keep track of people's tasking. There are 13 users each with individual worksheets that are updated every two weeks in a shared, macro-enabled spreadsheet. After the updates are made, I use vba (shown below) to successfully combine all the tasks from each user's worksheet to a new worksheet in the same workbook (initiated with a command button on a worksheet called "All_Tasking"). From there the boss sorts the tasks usually by priority or name then should be able to click a command button to export them to a new MS Project.
In the code below I am able open Project and export the Task Name, Start and End dates, and Resource (I intentionally don't put the duration) but I need help adding columns to Project from Excel.
Please how do I add columns to Project (using the VBA below which is in Excel). One column will be titled "Status" and have either an A (Active), I (Inactive not complete), or C (Complete). Another column will be called "Priority" (1 highest - 5 lowest).
I am not very good with VBA so please provide details. Any help you can give is greatly appreciated. Thank you. I apologize for submitting the following VBA as plain text but after I selected the code I did not find the green VBA emblem above the pane where I was typing.
Option Explicit
Private Sub cmdPrjct_Click()
Dim LDate As String
Dim iCnt As Long
Dim FleDte As String
Dim NumRows As Long
Dim sStrtDte() As Variant
Dim sEndDte() As Variant
Dim TskNm() As Variant
Dim DdLn() As Variant
Dim sRsrcNm() As Variant
Dim sAIC() As Variant ' active, inactive not complete, or complete
Dim ILvl() As Variant ' indent level of task
Dim sPrity() As Variant ' priority 1-5, 1 is the highest, 5 is the lowest priority
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim prApp As MSProject.Application
Dim prProject As MSProject.Project
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets(27) ' the All_Tasking work sheet
wsSheet.Select
wsSheet.Activate
ActiveSheet.Range("A3").Select
Selection.CurrentRegion.Select ' I have two rows of headers
Selection.Offset(2, 0).Resize(Selection.Rows.Count - 2).Select
NumRows = Selection.Rows.Count 'total numbe
NumRows = NumRows + 2
'Populate the variables with values from the worksheet.
With wsSheet
sPrity = .Range("A3:A" & NumRows).Value
sAIC = .Range("B3:B" & NumRows).Value
sStrtDte = .Range("F3:F" & NumRows).Value
sEndDte = .Range("G3:G" & NumRows).Value
TskNm = .Range("I3:I" & NumRows).Value
sRsrcNm = .Range("AB3:AB" & NumRows).Value
ILvl = .Range("AC3:AC" & NumRows).Value
End With
' open MS Project and save with date.
iCnt = 0
LDate = Format(Date, "mm-dd-yyyy")
FleDte = Left(LDate, 2) & "_" & Mid(LDate, 4, 2) & "_" & Right(LDate, 4)
Set prApp = New MSProject.Application
prApp = Shell("C:\Program Files\Microsoft Office\Office14\WINPROJ.EXE")
Set prProject = prApp.ActiveProject
prApp.ActiveProject.SaveAs "C:\Users\Jan\Desktop\Scheduling \Excel_Project\Trial_" & FleDte & ".mpp"
' I would like to add the two columns here. Please help with plenty of detail.
'Add task and other wanted information to MS Project.
With prProject
For iCnt = 1 To NumRows - 2
.Tasks.Add TskNm(iCnt, 1)
With .Tasks(TskNm(iCnt, 1))
' keep formatting (make subtasks; subtasks)
.OutlineLevel = ILvl(iCnt, 1)
.Start = sStrtDte(iCnt, 1)
.Finish = sEndDte(iCnt, 1)
.ResourceNames = sRsrcNm(iCnt, 1)
' .Status = SAIC(iCnt, 1)
' .Priority = sPrity(iCnt, 1)
End With
Next iCnt
End With
'Save MS Project.
With prApp
.FileSave
End With
Set prProject = Nothing
Set prApp = Nothing
End Sub
Thank you for your help. I appreciate your time and expertise.
v/r,
Jan