change data in excel with links/objects
Featured Partner
Top Posters
Zine Eddine
2 posts
Alex Lyaschenko
27 posts
mxsiegel
0 posts
Mattvtek
0 posts
Gareth Evans
3 posts
PP Admin
3, 129 posts
Rajkamal Tangirala
5 posts
Rahul Kumar
1 posts
blake333_
0 posts
Jeamiell Ostrovsky
2 posts
Our Partners
This can be done with vba, but even then you have to manually run the macro.
Here’s some vba code that shows you how to talk to Excel from project. You should be able to work out what it is doing. It is the basic structure of how to do this cleanly and reliably that is important here. Get this basic code working and you can do most anything in Excel from a MS Project macro.
Sub WriteDataToExcel()
’This is a test macro to write data from project to a new
’instance of an MS Excel file.
’ Declare an object variable to hold the object
’ reference. Dim as Object causes late binding.
Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")
’This code starts the application creating the object, in this case, a Microsoft Excel spreadsheet. Once an object is created, you reference it in code using the object variable you defined. In the following example, you access properties and methods of the new object using the object variable, ExcelSheet, and other Microsoft Excel objects, including the Application object and the Cells collection.
’ Make Excel visible through the Application object.
ExcelSheet.Application.Visible = True
’ Place some text in the first cell of the sheet.
ExcelSheet.Application.Cells(1, 1).Value = "This is column A, row 1"
Dim RowNumber As Integer
RowNumber = 3
For Each t In ActiveProject.Tasks ’ loop sequentially through all the tasks in the project
ExcelSheet.Application.Cells(RowNumber, 1).Value = t.Name
ExcelSheet.Application.Cells(RowNumber, 2).Value = t.Start
ExcelSheet.Application.Cells(RowNumber, 3).Value = t.Number1
RowNumber = RowNumber + 1
Next t
’===
’ Save the sheet to C:\test.xls directory.
ExcelSheet.SaveAs "C:\TEST.XLS"
’ Close Excel with the Quit method on the Application object.
ExcelSheet.Application.Quit
’ Release the object variable.
Set ExcelSheet = Nothing
End Sub
yes it is not a reliable way.
if you have a big amount of data,you can try to save your .mpp as Access database (.mdb),then make query in excel.
or make it simple,just do what you want in Access.
Thanks, i tried this but it seems to take ages to open or pretty much do anything and also crashes all the time.
Does project need installing again? (msp 2000)
copy cell in ms-project, and paste special (link) in Excell