[SOLVED] Search for 13-digit UNIX Timestamp and Add Time in Conventional Format After Each

Search for 13-digit UNIX Timestamp and Add Time in Conventional Format After Each

I have a Word document that is an export of a long chat history. Each entry includes a 13-digit UNIX timestamp.

I need to convert each timestamp to conventional/readable date/time (e.g. Friday, January 17, 2025 2:47 AM EST) and append the conventional conversion after the original timestamp.

I have a macro intended to:

Search for each 13-digit timestamp
Convert that timestamp to conventional date/time
Add the conventional date/time after the timestamp
Iterate and find the next timestamp and repeat

I can't figure out how to get the Range variable to paste/enter the appended value to the entry and then iterate to find the next timestamp.

For example:
The first timestamp is

1702654591899

The intended replacement text is:

1702654591899; Friday, December 15, 2023 - 10:36:32 AM EST

'Function converts UNIX 13 Digit Timestamp to date/time in Eastern Standard Time
'Subtracts 18,000,000 miliseconds to subtract 5 hours for Eastern Standard Time
'Adapted from FaneDuru's Solution at:
'https://stackoverflow.com/questions/73011816/excel-vba-convert-unix-timestamp-to-date-time

Function fromUNIX13DigitsEST(uT) As Date
   
   ''Original line that converts to UTC or GMT (I'm not sure which but it's
   ''5 hours later than intended so I commented it out but left it for info purposes)
   'fromUNIX13DigitsEST = CDbl(uT) / 86400000 + DateSerial(1970, 1, 1)
   
   'Modified from line above to account for Eastern Standard Time
   fromUNIX13DigitsEST = (CDbl(uT) - 18000000) / 86400000 + DateSerial(1970, 1, 1)

End Function


'Loops and Finds all 13 digit Unix Timestamps and replaces them with the Timestamp plus
'conventional date/time

Sub FindUnix13DigitTimestampsAndAddConventionalDateAndTime()

    Dim TimestampInstance As Range
    Dim TimestampAndConverted As String 'Artifact of the learning process kept to prevent loops

    Set TimestampInstance = ActiveDocument.Range
    
    With TimestampInstance.Find
        
        'Do I need more of these? Is this section the problem?
        .Text = "[0-9]{13}"
        .MatchWildcards = True
    
    Do While .Execute(Forward:=True) = True
      
      TimestampInstance.Select
      
      ' Used to test before adding the "TimestampInstance.Text = ..." line below
      ' Sets string variable equal to the original Timestamp and adds the standard date/time/time zone 
      ' Uses the Function above to convert the UNIX TimeStamp
      ' Kept to use in the MsgBox line below as a failsafe against a runaway loop
      ' Adapted from FaneDuru's Solution at
      ' https://stackoverflow.com/questions/73011816/excel-vba-convert-unix-timestamp-to-date-time

      TimestampAndConverted = TimestampInstance + "; " + Format(fromUNIX13DigitsEST(TimestampInstance), "dddd, mmmm d, yyyy - hh:nn:ss AM/PM") + " EST"
      
      ' PROBLEMATIC LINE THAT PARTIALLY DOES WHAT I INTENDED IT TO DO
      ' Duplicates the Function call above and adds standard date items
      ' Meant to replace each 13-digit timestamp with the timestamp plus standard date/time/time zone
      ' If commented out then loop iterates as intended and the MsgBox below displays each successive 
      ' output from the TimestampAndConverted variable

      TimestampInstance.Text = TimestampInstance + "; " + Format(fromUNIX13DigitsEST(TimestampInstance), "dddd, mmmm d, yyyy - hh:nn:ss AM/PM") + " EST"
      
      'Displays variable TimestampAndConverted above in a message box & helps prevent a runaway loop
      MsgBox TimestampAndConverted
      
      '
      '
      ' Should there be code here to allow the loop to iterate to next 13-digit timestamp
      ' or reset "TimestampInstance" variable??
      '
      '
      
      ''This isn't the solution since it ends up replacing the 13-digit timestamps with "" so I commented it out
      'TimestampInstance.Text = ""
    
    Loop
  
  End With

End Sub

I can display the intended output in a MsgBox for each iteration using a string variable.
When I try to repeat the process with the Range variable and enter the replacement text into the Range it gets stuck on the first timestamp.

Using the first timestamp ("1702654591899") as an example.
The text it adds is "; Friday, December 15, 2023 - 10:36:32 AM EST" but the loop re-adds "; Friday, December 15, 2023 - 10:36:32 AM EST".

It ends up as:

"1702654591899; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST..."

I CTRL+Break the loop and end the macro.

How do I add the conventional date/time once after each found timestamp and then continue to the next timestamp?

Solution

Your script is nearly complete. Adding just one line will resolve the issue.

Sub FindUnix13DigitTimestampsAndAddConventionalDateAndTime()
    Dim TimestampInstance As Range
    Dim TimestampAndConverted As String
    Set TimestampInstance = ActiveDocument.Range
    With TimestampInstance.Find
        .Text = "[0-9]{13}"
        .MatchWildcards = True
        Do While .Execute(Forward:=True) = True
            ' TimestampInstance.Select
            TimestampAndConverted = TimestampInstance + "; " + Format(fromUNIX13DigitsEST(TimestampInstance), "dddd, mmmm d, yyyy - hh:nn:ss AM/PM") + " EST"
            TimestampInstance.Text = TimestampAndConverted
            ' MsgBox TimestampAndConverted
            TimestampInstance.Collapse Word.wdCollapseEnd  ' ** change
        Loop
    End With
End Sub