Track changes

Hello All

I am using Windows 2K Professional & Office97.
I have a macro in an access mdb that outputs various files in excel format.

Is it possible either:

to use access VBA instead of the access macro to specify that the excel
'Track Changes' option is automatically set to 'True'; or
to specify elsewhere that all new excel files to have the 'Track Changes'
option automatically set to 'True'

In either case, the sub-options 'When' should be set to All, 'Who' set to
Everyone, and 'Where' should be left blank (i.e. applies to the whole
sheet)?

I'm sure this can be done, but just can't see how!
I would be grateful for any help.

Many thanks
Les

Les,

It's not as easy as you think...stay tuned

theDude

Automate Excel ‘Track Changes’ functionality from an Access database using VBA

Les,

I don’t think there’s a way to specify your parameters in Access when you export the data as an Excel file, so you will need to use ‘automation’ through Access to Excel to achieve your goal. I have had success in the past using Access VBA to automate Excel (and vice versa), but I haven’t used it for your requirement. What I thought would be a relatively simple solution didn’t pan out that way (kudos for what has turned out to be a really good challenge for me!)…

Hopefully I can explain this clearly…

The functionality you require in VBA to set Change Tracking properties in Excel workbooks was new to Excel ’97 and in my coding/testing efforts so far (I’m using the same Office/Excel platform as you), I’ve found that attempting to set most of those ‘new’ VBA properties either have no effect or generate a fatal error during VBA execution under ‘automation’ vs. setting them with VBA when you’re running VBA code while in the Excel application. Additionally, I’ve noted in the code below where the example provided in VB Help doesn’t work as supplied.

Finally, the change tracking functionality in Excel is ‘different’ because it creates a change log worksheet named ‘History’ if you specify it in the Highlight Changes option settings which includes the Who, When, Where (that always get reset to Excel defaults when accessed manually, so setting them w/VBA seems pointless, also noted in my code), and the ‘History’ sheet gets deleted when you save the file.

That being said, here’s my solution…

Use ‘automation’ to export a module (that contains Excel VBA code to do what you need) from your Access database and import it into the Excel file you generate from your Access database, then send a command to run the code in the export file to set the change tracking properties. It’s a bit convoluted, but it works. My code assumes that you already have the data exported to a file in Excel format (.xls).

Add a module to your Access database and copy the following code (change my references to yours as needed) that exports the next module and runs the automation of Excel (I defined it as a function so you can assign it to the RunCode action in an Access macro):

Function excelFileSetup()
Dim xlApp As Object
    ' Export VB module that 'works' for Excel file...
    ChDir ("C:\Temp")
    Application.VBE.ActiveVBProject.VBComponents(2).Export ("xlConfig.bas")
    ' Start Excel application and format the file...
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    ' Open the Access db output file...
    xlApp.Workbooks.Open fileName:="C:\Temp\MyBook.xls"
    ' Import VB module that 'works' for Excel file...
    xlApp.Application.VBE.ActiveVBProject.VBComponents.Import ("C:\Temp\xlConfig.bas")
    ' Run macro in Excel to format the Excel file...
    xlApp.Run ("setXLFileParameters")
    ' Temporarily disable Excel alerts (allows for saving over existing filename)...
    xlApp.DisplayAlerts = False
    ' Save & Close the Excel file...
    xlApp.ActiveWorkbook.Save
    xlApp.ActiveWorkbook.Close SaveChanges:=True
    ' Reinstate Excel alerts...
    xlApp.DisplayAlerts = True
    ' Quit Excel and release the reference to the application object...
    xlApp.Application.Quit
    Set xlApp = Nothing
End Function

Add another module to your Access database & rename it to ‘xlConfig’, then copy the following code that has the Excel VBA code to set the change tracking properties:

Sub setXLFileParameters()
    ' Temporarily disable Excel alerts (allows for saving over existing filename)...
    Application.DisplayAlerts = False
    ' Turn on file sharing (allows Change Tracking)...
    If Not ActiveWorkbook.MultiUserEditing Then
        ActiveWorkbook.SaveAs fileName:=ActiveWorkbook.FullName, _
        accessMode:=xlShared
    End If
    ' Reinstate Excel alerts...
    Application.DisplayAlerts = True
    ' Settings on the 'Advanced' tab of the Track Changes dialog in Excel...
    With ActiveWorkbook
        ' Turn on change history in Excel file...
        .KeepChangeHistory = True
        ' Set number of days of change history to store in Excel file (14 days)…
        .ChangeHistoryDuration = 14
        ' Set how often (in minutes) changes are evaluated/updated...
        .AutoUpdateFrequency = 5
        ' Set automatic saving of changes...
        .AutoUpdateSaveChanges = True
        ' Set how to resolve change conflicts if they occur when the file is
        ' in use by multiple users (prompt user to decide)
        .ConflictResolution = xlUserResolution
        ' Set print preview setting (not saved for user)...
        .PersonalViewPrintSettings = False
        ' Set list view setting (not saved for user)...
        .PersonalViewListSettings = False
    End With
    ' Set Who, When, etc. (I'm not sure what good this does since Excel resets
    ' these if you access them manually to generate the History log)...
    With ActiveWorkbook
        .HighlightChangesOptions _
            When:=xlAllChanges, _
            Who:="Everyone"
        ' Set if changes are highlighted on screen (No)
        .HighlightChangesOnScreen = False
        ' Set if changes are logged on new 'History' worksheet (Yes)
        .ListChangesOnNewSheet = True
    End With
End Sub

Maybe some of the change tracking ‘features’ have changed in later versions of Excel…

Hope this helps,
theDude