Answered by:
Use VBA to determine visible field columns in Project and send data to Excel

Question
-
I have a conundrum. I know the proper syntax to send data from a particular field to Excel, if I know in advance that I will be using that field. For example if I wanted to send information from the "Text 1" field I could use something along these lines:
TotalSelection=ActiveSelection.Tasks.Count
for i=1 to TotalSelection
xlRow.Offset(i, 0) = ActiveSelection.Tasks(i).Text1
Next iWhat I want to know is, how would I go about transferring data to Excel through VBA when I don't know the fields in advance? If I want to be able to export whatever project fields a particular project has open, and only those fields, and I don't know what those fields are in advance (as they could change from project file to project file) how could I code that? How can I make the Task.Property variable? (or is that even possible?) I found some old that determines what fields are already visible. See below:
Sub FindFieldNames()
Dim TB As Table
Dim CTR As Integer
For Each TB In ActiveProject.TaskTables
If TB.Name = ActiveProject.CurrentTable Then
Exit For
End If
Next
For CTR = 1 To TB.TableFields.Count
If CTR = 1 Then
FieldName = FieldConstantToFieldName(TB.TableFields(CTR).Field)
Else
FieldName = FieldName & Chr(13) & FieldConstantToFieldName(TB.TableFields(CTR).Field)
End If
Next CTR
MsgBox FieldName
End SubFieldName obviously is a string (that increments each time through), listing all the field columns that were visible. Can I use this approach to find the field name (as a string) and somehow use that to specifiy the property I want to invoke for a task in the ActiveSelection?
TotalSelection=ActiveSelection.Tasks.Count
for i=1 to TotalSelection
xlRow.Offset(i, 0) = ActiveSelection.Tasks(i).FieldConstantToFieldName(TB.TableFields(CTR).Field) <--x know this is not valid but can something like this be done?
Next iIf this is not possible, is there a way to reference the field column by index or fieldconstant?
Answers
-
Hi B Fennell,
To retreive the content of a task field in VBA, you can use the 'GetField' method.
The GetField method requires a field constant as its parameter. So you even don't need to use the FieldConstantToFieldName constant.So you could achieve what you want by using something like:
xlRow.Offset(i, 0) = ActiveSelection.Tasks(i).GetField(TB.TableFields(CTR).Field)
Some other remark about your code:
'TotalSelection=ActiveSelection.Tasks.Count' will fail if the active view is not a task view. So you might want to check for that.
An empty task row will make your code fail. You could this using:
Dim TSK as Task
For each TSK in ActiveSelection.Tasks
If Not TSK Is Nothing Then
xlRow.Offset(i, 0) = TSK.GetField(TB.TableFields(CTR).Field)
End If
NextI hope this helps,
Hans
My EPM blog: Projectopolis- Marked as answer by B Fennell Friday, July 22, 2011 4:56 PM
-
here is some code that should do what you want. you will just need to 'attach' excel.
Sub FindFieldNames() Dim TB As Table Dim Counter As Integer Dim FieldCount As Integer Dim FieldNames() As String Dim T As Task For Each TB In ActiveProject.TaskTables If TB.Name = ActiveProject.CurrentTable Then Exit For End If Next TB '2010 has an "Add new column" field a the end that throws off this code so im 'checking to see if this is version 14 or not. If it is then I reduce the Fieldcount by 1 If Application.Version = "14.0" Then FieldCount = TB.TableFields.Count - 1 Else FieldCount = TB.TableFields.Count End If '________________________________________________________________________________________ 'Redim the Array ReDim FieldNames(1, FieldCount) As String '_________________________________________ 'Get the names of the fields and put them into the array For Counter = 1 To FieldCount FieldNames(1, Counter) = FieldConstantToFieldName(TB.TableFields(Counter).Field) Next Counter '_______________________________________________________ 'Loop through tasks and get the data for the visible fields and do something with it. For Each T In ActiveProject.Tasks If Not (T Is Nothing) Then For Counter = 1 To FieldCount 'I did not have excel 'attached' to my code so I just tested with a debug.print. ' xlRow.Offset(ActiveProject.Tasks.Count, 0) = T.GetField(Application.FieldNameToFieldConstant(FieldNames(1, Counter))) Debug.Print T.GetField(Application.FieldNameToFieldConstant(FieldNames(1, Counter))) Next Counter End If Next T End Sub
Brian Kennemer – DeltaBahn Senior Architect
Blog | Twitter | LinkedIn- Marked as answer by B Fennell Friday, July 22, 2011 4:58 PM
All replies
-
Hi B Fennell,
To retreive the content of a task field in VBA, you can use the 'GetField' method.
The GetField method requires a field constant as its parameter. So you even don't need to use the FieldConstantToFieldName constant.So you could achieve what you want by using something like:
xlRow.Offset(i, 0) = ActiveSelection.Tasks(i).GetField(TB.TableFields(CTR).Field)
Some other remark about your code:
'TotalSelection=ActiveSelection.Tasks.Count' will fail if the active view is not a task view. So you might want to check for that.
An empty task row will make your code fail. You could this using:
Dim TSK as Task
For each TSK in ActiveSelection.Tasks
If Not TSK Is Nothing Then
xlRow.Offset(i, 0) = TSK.GetField(TB.TableFields(CTR).Field)
End If
NextI hope this helps,
Hans
My EPM blog: Projectopolis- Marked as answer by B Fennell Friday, July 22, 2011 4:56 PM
-
here is some code that should do what you want. you will just need to 'attach' excel.
Sub FindFieldNames() Dim TB As Table Dim Counter As Integer Dim FieldCount As Integer Dim FieldNames() As String Dim T As Task For Each TB In ActiveProject.TaskTables If TB.Name = ActiveProject.CurrentTable Then Exit For End If Next TB '2010 has an "Add new column" field a the end that throws off this code so im 'checking to see if this is version 14 or not. If it is then I reduce the Fieldcount by 1 If Application.Version = "14.0" Then FieldCount = TB.TableFields.Count - 1 Else FieldCount = TB.TableFields.Count End If '________________________________________________________________________________________ 'Redim the Array ReDim FieldNames(1, FieldCount) As String '_________________________________________ 'Get the names of the fields and put them into the array For Counter = 1 To FieldCount FieldNames(1, Counter) = FieldConstantToFieldName(TB.TableFields(Counter).Field) Next Counter '_______________________________________________________ 'Loop through tasks and get the data for the visible fields and do something with it. For Each T In ActiveProject.Tasks If Not (T Is Nothing) Then For Counter = 1 To FieldCount 'I did not have excel 'attached' to my code so I just tested with a debug.print. ' xlRow.Offset(ActiveProject.Tasks.Count, 0) = T.GetField(Application.FieldNameToFieldConstant(FieldNames(1, Counter))) Debug.Print T.GetField(Application.FieldNameToFieldConstant(FieldNames(1, Counter))) Next Counter End If Next T End Sub
Brian Kennemer – DeltaBahn Senior Architect
Blog | Twitter | LinkedIn- Marked as answer by B Fennell Friday, July 22, 2011 4:58 PM
-
Hans and Brian
A thousand thank yous to you both for that "GetField" method. That did the trick. Also thanks for the head's up on the "If Not Task" contingency. I hadn't run into that but I was bound to at some point, so it's like I got 2 solutions for the price of one. Thank you both once again.