Track changes

[Leslie Isaacs]

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

[theDude]

Les,

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

theDude

[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):

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Hope this helps,
theDude