Based on some reading around the forums, I came up with this, but it only exports a single line from Excel:
Option Explicit
Sub Add_Data_Project()
'Add a reference to Microsoft Project x.x Object Library
'in the VB-editor through the command Tools | References..
'The x.x stands for version like 11.0 or previously.
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim stRig As String, stWell As String, stComment As String
Dim lnDuration As Long
Dim dtStart As Date
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets(1)
'Populate the variables with values from the worksheet.
With wsSheet
stRig = .Range("A2:A40")
stWell = .Range("B2").Value
stComment = .Range("C2").Value
lnDuration = .Range("K2").Value
dtStart = .Range("L2").Value
End With
'Instantiate and open MS Project and the project.
Dim prApp As MSProject.Application
Dim prProject As MSProject.Project
Set prApp = New MSProject.Application
prApp.FileOpen "Forecast_2013.mpp"
Set prProject = prApp.ActiveProject
'Add task and other wanted information to the project.
With prProject
.Tasks.Add stRig
.Tasks.Add stWell
.Tasks.Add stComment
.Tasks.Add lnDuration
.Tasks.Add dtStart
With .Tasks(stRig)
.Text1 = stRig
.Text2 = stWell
.Text4 = stComment
.Duration = lnDuration & " days"
.Start = dtStart
End With
End With
'Save the project and close MS Project.
With prApp
.FileSave
.Quit
End With
MsgBox "The project VBAX has successfully been updated!", vbInformation
'Release objects from memory.
Set prProject = Nothing
Set prApp = Nothing
End Sub
It works fine if I only include one cell in the range per column:
Example: stRig = .Range("A2")
However - as soon as I try: stRig = .Range("A2:A40").Value
I get a type mismatch error... can't figure it out.
Even tried: stRig = .Range("A2,A3").Value
This doesn't error and completes successfully - but only adds a single line in ms project, not the 2 lines I would expect.. I have about 40 rows of data across these column I need to include.
Also, not sure what this line is: With .Tasks(stRig)
Im sure what this is for...some kind of index, but how do I use it?
I'm guessing I need some kind of variable to take each row from excel and move it to project, just not sure what that looks like.
Option Explicit
Sub Add_Data_Project()
'Add a reference to Microsoft Project x.x Object Library
'in the VB-editor through the command Tools | References..
'The x.x stands for version like 11.0 or previously.
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim stRig As String, stWell As String, stComment As String
Dim lnDuration As Long
Dim dtStart As Date
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets(1)
'Populate the variables with values from the worksheet.
With wsSheet
stRig = .Range("A2:A40")
stWell = .Range("B2").Value
stComment = .Range("C2").Value
lnDuration = .Range("K2").Value
dtStart = .Range("L2").Value
End With
'Instantiate and open MS Project and the project.
Dim prApp As MSProject.Application
Dim prProject As MSProject.Project
Set prApp = New MSProject.Application
prApp.FileOpen "Forecast_2013.mpp"
Set prProject = prApp.ActiveProject
'Add task and other wanted information to the project.
With prProject
.Tasks.Add stRig
.Tasks.Add stWell
.Tasks.Add stComment
.Tasks.Add lnDuration
.Tasks.Add dtStart
With .Tasks(stRig)
.Text1 = stRig
.Text2 = stWell
.Text4 = stComment
.Duration = lnDuration & " days"
.Start = dtStart
End With
End With
'Save the project and close MS Project.
With prApp
.FileSave
.Quit
End With
MsgBox "The project VBAX has successfully been updated!", vbInformation
'Release objects from memory.
Set prProject = Nothing
Set prApp = Nothing
End Sub
It works fine if I only include one cell in the range per column:
Example: stRig = .Range("A2")
However - as soon as I try: stRig = .Range("A2:A40").Value
I get a type mismatch error... can't figure it out.
Even tried: stRig = .Range("A2,A3").Value
This doesn't error and completes successfully - but only adds a single line in ms project, not the 2 lines I would expect.. I have about 40 rows of data across these column I need to include.
Also, not sure what this line is: With .Tasks(stRig)
Im sure what this is for...some kind of index, but how do I use it?
I'm guessing I need some kind of variable to take each row from excel and move it to project, just not sure what that looks like.
