Dates in document macros

Hi @Martin Hulsenboom 

That is a very strange case indeed. Would you mind sharing the macro code here?

Thanks

Hi @Amila Samarasinghe,

It's macro code from the Ventechi environment:

IFS_01_08_OBJECT_CONNECTIONS

PUBLIC FUNCTION OBJECT_CONNECTIONS(ATTR)

'ON ERROR RESUME NEXT

    NROWNO = 1

    NTABLEROWNO = 2

    SLUNAMES = ATTRVALUE(ATTR)

    SARRAYLUNAME = SPLIT(SLUNAMES, ";", -1, 1)

    FOR EACH SLU IN SARRAYLUNAME

        NKEY = ""

        NCOUNTER = 0

        NROWNO = NROWNO + 1

        STAG = SLU & ".COUNT"

        NCOUNTER = ATTRVALUE(STAG)

        NROWNO = NROWNO + 1

        STAG = SLU & ".ATTRIBUTES"

        SATTRIBUTES = ATTRVALUE(STAG)

        SARRAYATTRIBUTE = SPLIT(SATTRIBUTES, ",", -1, 1)

        IF NCOUNTER > 0 THEN

            FOR NRECCOUNT=1 TO NCOUNTER

                FOR EACH SATTRIBUTE IN SARRAYATTRIBUTE

                    STAG = SLU & "." & NRECCOUNT & "." & SATTRIBUTE

                    NROWNO = NROWNO + 1

                    IF O_APP = "Microsoft Word" THEN

                       IF LEN(ATTRVALUE(STAG)) > 254 THEN

                          N = 0

                          FOR I = 1 to LEN(ATTRVALUE(STAG)) STEP 254

                             IF N > 0 THEN

                               O_APP.ACTIVEDOCUMENT.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG & "_" & N, FALSE, "4",  MID(ATTRVALUE(STAG),I,254)

                             ELSE

               O_APP.ACTIVEDOCUMENT.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG, FALSE, "4",  MID(ATTRVALUE(STAG),I,254)

                             END IF

                                 N = N + 1

                                 NEXT

                       ELSE

IF LEN(ATTRVALUE(STAG)) = 19 AND MID(ATTRVALUE(STAG),5,1) = "-" AND MID(ATTRVALUE(STAG),8,1) = "-" AND MID(ATTRVALUE(STAG),11,1) = "-" AND MID(ATTRVALUE(STAG),14,1) = "." AND MID(ATTRVALUE(STAG),17,1) = "." THEN

O_APP.ACTIVEDOCUMENT.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG, FALSE, "3",  MID(ATTRVALUE(STAG),1,10)

ELSE

                          O_APP.ACTIVEDOCUMENT.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG, FALSE, "4",  ATTRVALUE(STAG)

END IF

                       END IF

                    END IF

                    IF O_APP = "Microsoft Excel" THEN

                       IF LEN(ATTRVALUE(STAG)) > 254 THEN

                          N = 0

                          FOR I = 1 to LEN(ATTRVALUE(STAG)) STEP 254

                             IF N > 0 THEN

                                              OBJWORKBOOK.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG & "_" & N, FALSE, "4",  MID(ATTRVALUE(STAG),I,254)

                             ELSE

                                              OBJWORKBOOK.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG, FALSE, "4",  MID(ATTRVALUE(STAG),I,254)

                             END IF

                                 N = N + 1

                                 NEXT

                       ELSE

                          OBJWORKBOOK.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG, FALSE, "4",  ATTRVALUE(STAG)

                       END IF                

                    END IF

                NEXT

                NTABLEROWNO = NTABLEROWNO + 1

                NKEY = ""

            NEXT

        END IF

    NEXT

    NROWNO = NROWNO + 1

END FUNCTION

PUBLIC FUNCTION ATTRVALUE(SVAL)

ON ERROR RESUME NEXT

    ATTRVALUE = SCRIPTVALUES.ITEM(UCASE(SVAL)).VALUE

END FUNCTION

Add a message box before this code line:

O_APP.ACTIVEDOCUMENT.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG, FALSE, "3", MID(ATTRVALUE(STAG),1,10)

The message box should display this:

MID(ATTRVALUE(STAG),1,10)

Then we will see if the value as extracted from the macro is wrong or if Word is doing something with the value.

I suspect the value is already wrong when the macro gets it, but that remains to be seen.

Which attribute did you pick in the basic data for macro attributes?

Hi Mathias,

Thanks for your reaction. I have added the code you mentioned, but I cannot see a message. What I do notice, is that the system doesn't automatically open Word anymore. When I open the document manually, I still see the wrong values.

As for the attributes: I have kept it very simple. It was a first test in order to give the customer an impression of what we are able to. Note, that I have defined two date fields: a custom field (the jubilee hire date) and a core field (birth date). In both cases, the shown values are wrong.

@Mathias Dahl , And to verify what I've done in the code itself:

​Hi @Mathias Dahl , The message is showing the correct date… So, this implies that it should be something in Word?
Best regards,
Martin

Good finding!

I have a crazy idea for you to try. Not tested and not thought through...

I noticed something about the code that handles that separate case:

O_APP.ACTIVEDOCUMENT.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG, FALSE, "3",  MID(ATTRVALUE(STAG),1,10)

See the "3" there? Other properties are added with "4". My guess is that "3" might mean that the property is a date and not a string. See what happens if you change it to "4"…

Seems I was right :) 

The third parameter there is the type and these are the ones available:

https://docs.microsoft.com/en-us/office/vba/api/office.msodocproperties

As you can see, the number 3 stands for a date.

So, Word is probably interpreting the value we give it in the wrong way, or we give it on the wrong format.

Hi @Mathias Dahl,

Your suggestion proved successful. I have changed the 3 into 4, and when testing the document macro again, the dates were shown correctly!

Thanks, Mathias!

/Martin

Great 😃 , thanks for reporting back!