Programatically Amending a Lookup Table in OutlineCode1 RRS feed

  • Question

  • So here is my challenge....I have a lookup table defined in OutlookCode1 which I am using as an export field for a report in excel.. I have recently expanded the table considerably and now fall foul of a 255 char limit for a custom sort in MS Excel.  To resolve this I have dramatically shortened the codes stored in the lookup of outlinecode1 and used a select case to associate the codes with the full name when running the extract code.  All sounds good...only I need to programmatically change the lookup table as I am not the only one using the plan and I don't have full access to it.

    I have tried the sample code from MSDN...but struggled to actually be able to reference and amend single items in the lookup table.

    A basic example would be: Find "LONDON" in the lookup table and replace with "LO".  Additionally I would need to refresh the plan once the table had been updated to reflect the new values (although I believe this is automatic?)...

    Any assistance appreciated


    Monday, November 13, 2017 7:50 AM

All replies

  • Chris,

    No problem. The following macro should do what you need. I've included the two Debug.Print statements strictly for demonstration. The results will show in the Immediate window of the VBEditor. I also included the Case Else message. It's good practice to have an "else" condition but if the user message gets too cumbersome, that statement can be deleted.

    One thing you want to careful about when examining strings, a text string or partial string may appear where you don't expect it. For example, if you are testing for "LONDON", the strings "london", "landon", "LONDONTOWN" will not be found. The first, lower case entry, is addressed by the Option Compare Text statement. The other two however don't match exactly so they will not be found.

    Anyway, here's the macro. Hope it helps.

    Oh, and yes, once you run the macro and change the table entries, everything is updated.


    Option Explicit
    Option Compare Text
    Sub EditOutCode()
    Dim LTE As LookupTableEntry
    For Each LTE In ActiveProject.OutlineCodes(1).LookupTable
    Debug.Print LTE.Name
        Select Case LTE
            Case "london"
                LTE.Name = "LO"
            Case "paris"
                LTE.Name = "PA"
            'Add more cases as necessary to cover all values
            Case Else
                MsgBox "No matching entry found", vbInformation
        End Select
        Debug.Print LTE.Name
    Next LTE
    End Sub

    Monday, November 13, 2017 6:59 PM