none
MS Project 2013 Standard TableReset and TableEdit RRS feed

  • Question

  • Hi

    I am trying to TableReset() the "Entry" table and TableEdit() 22 fields which already there. When I run TableApply() I get the error "This is not a valid number for the ColumnPosition argument" but I when I switch from Gantt Chart view to Task Sheet view I can see 8 fields have been correctly updated. These fields are not displayed correctly in the Gantt Chart view when code is run.

    I think the error crops up when the number of fields edited exceeds the number displayed/visible. Is there a way around?

    Also I find the header row height reduced to single row in Task Sheet view after TableApply(). How can I prevent this?

            Application.TableReset()
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Indicators", Title:="Sketch", Width:=6, Align:=PjAlignment.pjLeft, ColumnPosition:=0, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="text5", Title:="Ref", Width:=8, Align:=PjAlignment.pjLeft, ColumnPosition:=1, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Text6", Title:="Buyer", Width:=10, Align:=PjAlignment.pjLeft, ColumnPosition:=2, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Name", Title:="Style", Width:=20, Align:=PjAlignment.pjLeft, ColumnPosition:=3, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Text1", Title:="PO #", Width:=15, Align:=PjAlignment.pjLeft, ColumnPosition:=4, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Text8", Title:="Color", Width:=15, Align:=PjAlignment.pjLeft, ColumnPosition:=5, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Number15", Title:="Seq", Width:=8, Align:=PjAlignment.pjRight, ColumnPosition:=6, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Number1", Title:="Order Qty", Width:=8, Align:=PjAlignment.pjRight, ColumnPosition:=7, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Number3", Title:="Split Qty", Width:=8, Align:=PjAlignment.pjRight, ColumnPosition:=8, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Date1", Title:="Delivery", Width:=15, Align:=PjAlignment.pjCenter, ColumnPosition:=9, AlignTitle:=PjAlignment.pjCenter)
            Application.TableApply(Name:="Entry")

    Thanks

    Irfan


    Tuesday, May 24, 2016 7:33 AM

All replies

  • Irfan,

    What are you trying to do? I assume you want to create a new "Entry" table but that's not quite what your code does. For example, you change the title of the Indicators field but that only changes the title of the column, it does not change the field. But, maybe that's what you intended. However, if you intended to change the fields (i.e. create a new view table), then you need to include the OverwriteExisting and NewFieldName arguments in the FieldEdit Method. And by the way, you probably want to give your new table it's own unique name instead of re-defining the existing Entry table. It's just good practice not to mess with the built-in Views, Tables, etc.

    Okay, so why does your code fail when the column position is 9? Because in the default Entry table, the first column is locked (i.e. the ID field). Column counting starts at zero and since the first column is locked, the first column (i.e. zero column) is the Indicators field. If you then look at the number of columns in the default Entry table, there are 0-8, the "Add New Column" is not counted as a column, it's an option. So when your code tries to change the title of the 9th column, there is none so the error occurs. So, the "way around" this is to create a new table, not simply edit the existing table, as I noted in the first paragraph.

    As far as the header row height, add the AutoHeaderRowHeightAdjustment argument to the TableEdit Method. Note, this argument, and some others like it, only need to be added to one TableEdit statement and not to each statement. I normally add common arguments like this to the first or last TableEdit statement, it keeps unnecessary bulk out of the statements.

    Hope this helps.

    John

    Tuesday, May 24, 2016 4:36 PM
  • I wouldn't bother resetting the table, I would create a new table called Data or whatever is relevant. Entry table is the default entry table. For customised stuff like this, always create a new table.

    Record a macro of you manually creating it to get working code.

    As John said re naming fields. To just name a custom field for the current table do what you have done. Much better though to rename the fields though using the CustomFieldRename method.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Thursday, May 26, 2016 9:37 AM
    Moderator
  • John

    I need to rearrange the table to its initial arrangement including the position of each column, width, alignment, etc. Some users tend to alter these while working and I need to do a reset. I do not need to change the  fields. I tried to record a macro moving the fields as Rod suggested but when the recording is on the moving is disabled. Even if I create a separate table instead of the default entry table I would need to the same rearrangement.

    Noted that I cannot edit fields beyond the number of columns in the default or existing view.

    I set both the AutoHeaderRowHeightAdjustment and HeaderTextWrap to true in the last TableEdit statement. I run the code in Gantt Chart. When I change to Task Sheet the title row is only single row height and part of the title is hidden. The same occurred when I set True and False respectively but when I set them to True and False or False and False the title row height remained double and fully visible.

    Thanks

    Irfan

    Thursday, May 26, 2016 1:34 PM
  • Irfan,

    I'm confused by the first sentence in your last reply. What is the "initial arrangement"? Is that the default Entry table or your custom view table (i.e. "Sketch", "Ref", "Buyer" "Style", etc.)?

    Since a view is saved with a file, if users are editing (i.e. adding, deleting, re-arranging field columns), I suggest you set up the view table as you want it and transfer it to your Global. Then if a user modifies the view table you can simply use the OrganizerMoveItem Method to reset the view table to the one stored in your Global. You can even do that with an Open Event macro so each time the file is opened, it automatically resets the view table.

    I'm also confused by your last paragraph. Do you still have a problem with column header row heights/text wrapping when switching between Gantt Chart and Task Sheet views? If you do still have a problem, a snippet of your current macro code would be helpful.

    John
    Thursday, May 26, 2016 2:57 PM
  • John

    Sorry, I was bit vague there. I mean my custom view before I let other use.

    I will try the OrganizerMoveItem Method and update you the progress.

    Please find the code used below to set the title row. My earlier reply was based on the following 4 combinations.

    HeaderAutoRowHeightAdjustment:=False, HeaderTextWrap:=False

    HeaderAutoRowHeightAdjustment:=True, HeaderTextWrap:=False

    HeaderAutoRowHeightAdjustment:=False, HeaderTextWrap:=True

    HeaderAutoRowHeightAdjustment:=True, HeaderTextWrap:=True

    The code used is as below:

        Sub ColumnReset()
    
            On Error GoTo ColumnReset_Err
    
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Indicators", Title:="Sketch", Width:=6, Align:=PjAlignment.pjLeft, ColumnPosition:=0, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="text5", Title:="Ref", Width:=8, Align:=PjAlignment.pjLeft, ColumnPosition:=1, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Text6", Title:="Buyer", Width:=10, Align:=PjAlignment.pjLeft, ColumnPosition:=2, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Name", Title:="Style", Width:=20, Align:=PjAlignment.pjLeft, ColumnPosition:=3, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Text1", Title:="PO #", Width:=15, Align:=PjAlignment.pjLeft, ColumnPosition:=4, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Text8", Title:="Color", Width:=15, Align:=PjAlignment.pjLeft, ColumnPosition:=5, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Number15", Title:="Seq", Width:=8, Align:=PjAlignment.pjRight, ColumnPosition:=6, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Number1", Title:="Order Qty", Width:=8, Align:=PjAlignment.pjRight, ColumnPosition:=7, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Number3", Title:="Split Qty", Width:=8, Align:=PjAlignment.pjRight, ColumnPosition:=8, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Date1", Title:="Delivery", Width:=15, Align:=PjAlignment.pjCenter, ColumnPosition:=21, AlignTitle:=PjAlignment.pjCenter, HeaderAutoRowHeightAdjustment:=False, HeaderTextWrap:=False)
            Application.TableApply(Name:="Entry")
    
    ColumnReset_Exit:
            Exit Sub
    
    ColumnReset_Err:
            MsgBox("ColumnReset: " & ErrorToString())
            Resume ColumnReset_Exit
    
        End Sub

    Thanks

    Irfan

    Thursday, May 26, 2016 4:56 PM
  • Irfan,

    It looks like you may have found another glitch in the application. Apparently for Project 2010, which I have, and Project 2013, which you have, when the Entry table is applied to the Task Sheet view, it is unresponsive to the HeaderAutoRowHeightAdjustment argument of the TableEdit Method. I tried with Project 2007 and it seems to work as expected so it must be something that crept into Project 2010 when it was released and has stayed with Project 2013. I'd be interested to know if the issue is present with Project 2016 so I'll have to ask. I'll get back to you.

    John

    Thursday, May 26, 2016 8:01 PM
  • Irfan,

    I got confirmation that this issue also exists in Project 2016 so I will report it to Microsoft. I'll post a reply if and when I hear back.

    Meanwhile I have a question. Both times when I attempted to run your macro code I got a compile error (list separator error). I had to correct the error before the macro would run. Did you actually run the code you posted? If so, exactly how did you do it?

    John

    Friday, May 27, 2016 1:52 AM
  • John

    I do not get any errors when running the build. I just copied and pasted the code.

    Following is the full code. Please let me know where and what correction you did.

    I run the ColumnReset() subroutine from a button in the custom ribbon.

        Sub ColumnReset()
    
            On Error GoTo ColumnReset_Err
    
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Indicators", Title:="Sketch", Width:=6, Align:=PjAlignment.pjLeft, ColumnPosition:=0, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="text5", Title:="Ref", Width:=8, Align:=PjAlignment.pjLeft, ColumnPosition:=1, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Text6", Title:="Buyer", Width:=10, Align:=PjAlignment.pjLeft, ColumnPosition:=2, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Name", Title:="Style", Width:=20, Align:=PjAlignment.pjLeft, ColumnPosition:=3, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Text1", Title:="PO #", Width:=15, Align:=PjAlignment.pjLeft, ColumnPosition:=4, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Text8", Title:="Color", Width:=15, Align:=PjAlignment.pjLeft, ColumnPosition:=5, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Number15", Title:="Seq", Width:=8, Align:=PjAlignment.pjRight, ColumnPosition:=6, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Number1", Title:="Order Qty", Width:=8, Align:=PjAlignment.pjRight, ColumnPosition:=7, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Number3", Title:="Split Qty", Width:=8, Align:=PjAlignment.pjRight, ColumnPosition:=8, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Date1", Title:="Delivery", Width:=15, Align:=PjAlignment.pjCenter, ColumnPosition:=9, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Number2", Title:="Target", Width:=8, Align:=PjAlignment.pjRight, ColumnPosition:=10, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Number4", Title:="Feed Hrs", Width:=8, Align:=PjAlignment.pjRight, ColumnPosition:=11, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Duration", Title:="Duration", Width:=12, Align:=PjAlignment.pjRight, ColumnPosition:=12, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Start", Title:="Start", Width:=15, Align:=PjAlignment.pjCenter, ColumnPosition:=13, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Finish", Title:="Finish", Width:=15, Align:=PjAlignment.pjCenter, ColumnPosition:=14, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Date7", Title:="Cut-off", Width:=15, Align:=PjAlignment.pjCenter, ColumnPosition:=15, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Date6", Title:="Fabric In", Width:=15, Align:=PjAlignment.pjCenter, ColumnPosition:=16, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Date5", Title:="RFP", Width:=15, Align:=PjAlignment.pjCenter, ColumnPosition:=17, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Text2", Title:="Flag", Width:=8, Align:=PjAlignment.pjLeft, ColumnPosition:=18, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Text7", Title:="Emb", Width:=8, Align:=PjAlignment.pjLeft, ColumnPosition:=19, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Text3", Title:="Wash", Width:=8, Align:=PjAlignment.pjLeft, ColumnPosition:=20, AlignTitle:=PjAlignment.pjCenter)
            Application.TableEdit(Name:="Entry", TaskTable:=True, FieldName:="Date8", Title:="Date Serial", Width:=15, Align:=PjAlignment.pjCenter, ColumnPosition:=21, AlignTitle:=PjAlignment.pjCenter, HeaderAutoRowHeightAdjustment:=False, HeaderTextWrap:=False)
            Application.TableApply(Name:="Entry")
    
    ColumnReset_Exit:
            Exit Sub
    
    ColumnReset_Err:
            MsgBox("ColumnReset: " & ErrorToString())
            Resume ColumnReset_Exit
    
        End Sub

    Thanks

    Irfan



    Friday, May 27, 2016 4:36 AM
  • Irfan,

    In VBA all methods include an argument list. The syntax is similar to:

    expression.TableEdit([list of arguments, some required and some optional])

    Note the parenthesis around the list of arguments. When that method is used in a macro as a statement with no return value, the parenthesis must be deleted or a compiler error (syntax error) will occur. For example, with no return value the TableEdit Method needs to be,

    Application.TableEdit Name:="Entry", TaskTable:=true, FieldName:="Text1"

    However if a return value is captured for the method (for the TableEdit Method the return value is boolean), then the parenthesis must be included. For example, with a return value the TableEdit Method needs to be,

    TbEditState = Application.TableEdit (Name:="Entry", TaskTable:=true, FieldName:="Text1")

    Since your macro does not return any values for the TableEdit Method, the parenthesis for the argument list must be deleted. That's why I don't understand how you can be executing the macro without getting a compile error. Just for reference, our VBA expert, Rod Gill, wondered the same thing when I asked him to verify performance using Project 2016. He also had to remove the parenthesis to get your code to run.

    Also, a compiler error is generated for the missing "ErrorToString()" procedure in the error sub. Where or what is the "ErrorToString()"?

    None of this has any bearing on the glitch you uncovered, but there are some questions about code execution.

    John
    Friday, May 27, 2016 3:18 PM
  • John

    I am not using VBA. The code is written in Visual Studio 2015 Project 2013 VSTO Add-in using VB.

    Thanks

    Irfan

    Friday, May 27, 2016 4:20 PM
  • Irfan,

    Okay, that explains the discrepancy. You didn't mention that so both Rod and I assumed you were doing this in VBA, hence the question.

    I don't use Visual Studio so I'm not familiar with its style and syntax. Just for reference I'm not sure why you would be using an Add-in for this process as it can be done very readily in VBA and normally VBA runs faster than code in an Add-in, so I've been told by Rod.

    Anyway, I'm glad we got that cleared up. Again I'll let you know if and when I hear back from Microsoft with respect to the Entry table not quite working when in the Task Sheet view. It might have something to do with the timescale header when in the Gantt Chart view but that's only a guess on my part.

    Johh

    Friday, May 27, 2016 6:01 PM
  • Okay, for an add-in firstly you can only do what VBA can do. So the behavior in VBA is the same, so no immediate solution.

    Secondly, for an Add-in it is not safe to assume anything about what's available. So I strongly recommend you create and then apply a new table. Do not use the Entry table as you will up=set some users when their familiar setup gets trashed. Use training to show that they can apply the Entry table to restore the columns they prefer.

    I would always create a new View and Table. That way I can control exactly what is displayed. For example the Reset method I think restores the table to what's in the Global.mpt version. You can't know what that is, even though for 90%+ of users it will be the default.

    VBA is faster than an Add-in whenever fields are read or written to, as the Add-in runs in a different windows process, making reading the value of a field a slow inter-process operation. VBA runs in the same process as Project so although VBA code runs slower because its interpreted, not compiled, its so much faster reading and writing data that overall VBA usually runs faster.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Friday, May 27, 2016 8:52 PM
    Moderator
  • Rod

    Noted VB can do only what VBA can do, and VBA is overall faster.

    I created a new Table(Plan) and a new View (Plan Sheet) based on the same table (Plan). Then I run the ViewApply("Plan Sheet") followed by ViewReset() method. The code updated the view based on the global copy of "Plan Sheet" view. Everything worked fine and as expected.

    The reason I am using VB is because of the following.

    1) I could give users a stand-alone set-up file to update the system without the project file thereby retaining the user data/updates.

    2) I have used a custom ribbon created in vb.

    3) Always worried in the possibility of Microsoft discontinuing VBA.

    4) I get familiar with one common development environment and keep myself updated with the latest releases.

    Thanks

    Irfan


    Saturday, May 28, 2016 4:04 AM
  • No worries.

    You can create custom ribbon tabs using project VBA.

    If Microsoft discontinued VBA there would be a riot. It has to continue for Office as there are way too many organizations with mission critical files controlled by VBA.

    In Project, if you don't user Project Online/Server then without VBA there is no useful advantage to Project compared to a number of competing tools. I've also heard from several Microsoft high level managers that VBA is her to stay. That can always change but I very much doubt it within the next couple of releases.

    Do have to note that the web based Office version does not support VBA.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Tuesday, May 31, 2016 10:51 PM
    Moderator