Excel – Is it possible to sort a table by entry modification date? Specifically, how to access from VBA the last modification time of an object

date-modifiedmicrosoft excelmicrosoft wordsortingvisual basic

I have a MS Word document where I accumulate and frequently modify certain data in a very long table. So I would like to sort the data by latest modification time in the rows. Of course if I add a column where I enter the modification time by hand this would work but I would like to avoid this if possible.

OK as the question has been put on hold with the request to make it more concrete, I am trying to modify it accordingly.

What I specifically want (and then I can do it myself) is this:

Since MS Word has features like change tracking, each modified portion of text must have its last modification time recorded in some form.

My question is then – how can I access this information from within a Visual Basic macro? I mean something like ThisTable.ThisCell.ModificationTime() or ThatTable.CurrentRow.LastModificationSessionTime() or something similar.

Best Answer

This is for an Excel worksheet. Data is entered or modified in column A. The macro automatically enters the date/time in column B. The macro then sorts column A & B by column B, putting the most recent at the top.

Enter the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range
    Dim AB As Range
    Set A = Range("A:A")
    Set AB = Range("A:B")

    If Intersect(Target, A) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
        Target.Offset(0, 1) = Now
        AB.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

right-click the tab name near the bottom of the Excel window
select View Code - this brings up a VBE window
paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

bring up the VBE windows as above
clear the code out
close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Related Solutions

Excel – Updating Microsoft Word link item through VBA

I found a work arround to this. Instead of changing the Itens, I created a VBA script to name all the ranges linked. So instead of changing the link item in Word, I change the named range value in excel. The link item remains the same, but it points to a range that can be pragmatically modified. Here's the code I put together to put names to ranges:

Sub CreateNamedRanges()

Dim i As Integer
For i = 1 To Worksheets.Count
     sheetName = "Mysheet" & i
     varName = "Myvar" & CStr(i)
     Set Rng = Sheets(sheetName).Range("G6:I9")
     ActiveWorkbook.Names.Add Name:=varName, RefersTo:=Rng
Next i

End Sub

The downside of this approach is that I had to recreate (link) manually all the connections I did previously. To change the file Source I used the code in the question link. For completion's sake I am writing it here:

Sub changeSource()
Dim dlgSelectFile As FileDialog  'FileDialog object
Dim thisField As Field
Dim selectedFile As Variant    'must be Variant to contain filepath of selected item
Dim newFile As Variant
Dim fieldCount As Integer



'create FileDialog object as File Picker dialog box
Set dlgSelectFile = Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)



With dlgSelectFile
'use Show method to display File Picker dialog box and return user's action
    If .Show = -1 Then

        'step through each string in the FileDialogSelectedItems collection
        For Each selectedFile In .SelectedItems
            newFile = selectedFile    'gets new filepath
        Next selectedFile
    Else   'user clicked cancel
    End If
End With
Set dlgSelectFile = Nothing



'update fields
fieldCount = ActiveDocument.Fields.Count
For x = 1 To fieldCount
    ActiveDocument.Fields(x).LinkFormat.SourceFullName = newFile
Next x



End Sub

Finally, to update all the links at once:

Sub AutoOpen()
    ActiveDocument.Fields.Update
End Sub

Mac – Find all instances of a text and make it a hyperlink with a macro

This will find the word "google" (not googles or googled) and hyperlink it to http:\\google.com

It also applies the style

Sub FindAndHyperlink()
    'define the style
    Dim strStyle As String
    strStyle = "Subtle Emphasis"
    'set the search range
    Dim rngSearch As Range
    Set rngSearch = ActiveDocument.Range
    'set the search string
    Dim strSearch As String
    strSearch = "google"
    'set the target address for the hyperlink
    Dim strAddress As String
    strAddress = "http:\\google.com"

    With rngSearch.Find
        Do While .Execute(findText:=strSearch, MatchWholeWord:=True, Forward:=True) = True
            With rngSearch 'we will work with what is found as it will be the selection
                ActiveDocument.Hyperlinks.Add Anchor:=rngSearch, Address:=strAddress
                .Style = ActiveDocument.Styles(strStyle) 'throw the style on it after the link
            End With
            rngSearch.Collapse Direction:=wdCollapseEnd
            'keep it moving
        Loop
    End With
End Sub

Obviously, if you want to pass arguments you can do that so it can be called with your parameters, but this is the basis of what you'd need.

Best Answer

Related Solutions

Excel – Updating Microsoft Word link item through VBA

Mac – Find all instances of a text and make it a hyperlink with a macro

Related Question