PDA

View Full Version : How to creata a History file without sharing



davidw
11-08-2008, 06:02 PM
I want to create a history of changes I make to a data file. Other people use this to run macros out of. The sheet must remain protected (except when running macros and when I am updating the information). I want to list the file/sheetname/cell location/old value/new value/date/time. This will create an electronic trail I or someone else can follow when trying to resolve why numbers are not matching up in my dept. I do like the history file created when sharing a file however I cannot share. The data sheet must be protected. Any help is greatly appreciated.

Demosthine
11-08-2008, 08:36 PM
Good Evening.

Though far from perfect, here is an example file for what you can do.

In the attached file, a Text Stream is opened upon Workbook_Open and the initial entries for a valid XML File are added.

Upon any cell changing in the Workbook, a new Node (or entry) is added to the Text Stream.

When the workbook closes, it adds the final line of code to the XML File so that you can open it correctly and then closes the Text Stream.


I used XML because Excel has the ability to quickly and easily import XML data into a usable format. The second attached workbook, ViewHistory.Xls, shows how easily this data is imported.

Hope this helps.
Scott

Demosthine
11-08-2008, 09:11 PM
Ok, I went back to make a small change and found some errors in my last post. Nope, I'm definitely not perfect...

I mis-typed the Workbook_Close declaration, so the XML File was not finalized and closed correctly, thus preventing it from being opened correctly in an XML Viewer.

I deleted all of the contents of the Worksheet and the Loop began processing all of the entries. Umm, what's the Maximum Columns (83 I think?) times the Maximum Rows (65536 I think?) That's a lot of entries!!!

So I added in a small check for development purposes only, that asks if you want to update the history file with this large amount of data.

I've re-attached the new workbooks and hope I caught everything.
:dau:

Scott

davidw
11-10-2008, 09:38 AM
Sorry it took so long to get back with you. Ive been working 14 hr days. anyway I downloaded your file and yes I was having problems with it. I am going to look at the corrections you made and get back to you. Its hard to believe that there a people like yourself who are willing to spend their own time to help someone like me. I just want to thank you.

davidw
11-10-2008, 10:03 AM
I downloaded the file. I copied the macros into another file because the one i downloaded was read only. It opens takes data and closes without any problems. It creates a history.xml file. How do i view the xml file. I tried several methods to no avail.

Demosthine
11-10-2008, 10:57 AM
Good Morning.

The easiest way to open an XML File is to use Internet Explorer.

Using My Computer or Windows Explorer, navigate to your History.xml file.
Right click on it and select Open... to display the Open With Dialog.
Select or Browse for Internet Explorer.
Optionally, you can set it so that Internet Explorer is "Always used to open files of this type."
It will display the log into a special collapsible format.


Alternately, open up a new instance of Excel or a new Workbook.
In Office 2003, select Data and XML and Import...
Select the History.xml file and follow the prompts. Use the default schema if it asks.


In Office 2007, select the Data Ribbon and click From Other Sources. Select From XML Data Import.
Select your History.xml file and follow the prompts if they appear. Use the default schema when it asks.
It will import to a nice little table with AutoFilters.

Enjoy.
Scott

davidw
11-10-2008, 11:46 AM
Below is the data retrieved from the history.xml file. It contains the new input data in both old and new value columns. Beside old value should be "old original data"



<OldValue>is new data</OldValue> <NewValue>is new data</NewValue>

Demosthine
11-10-2008, 12:09 PM
Good Morning.

You are absolutely correct and I'm sorry I forgot to mention that in my first post. I did not include the "old" data because it it is a little more complicated.

As far as I know (thanks to recommendation from GTO), the only way to do this is would be to use a SelectionChange Event to store the "old" values into a temporary variable. As far as I know (and I would love for someone to prove me wrong), there is no "LastValue" or "BeforeUpdate" value for a cell or range.

Then, using the Change Event, you would compare the temporary variable value to the new values. If they are different, log it to the history file. If not, nothing was actually changed, so ignore it.

As I said, this is a little more complicated because you have to contend with Ranges that may have multiple cells and be on multiple sheets. For example, you can select Sheet1 and Sheet 2 and delete the contents of Cell A1 on both sheets at once. Or you can select Range("A1:G10") and delete the contents of all cells.

This method only saves the values. It'll have to be modified to include Formats or Formulas if you want it that detailed.

Hope this explains it better.
Scott