CODEWITHSUNDEEP
excelziplogfilevba
engineerchange
engineerchange

Reputation: 446

Save logfile within Excel file using VBA

I am trying to develop a manner in VBA to track changes in a document without having to hide the contents in an extra sheet within a workbook.

I understand that if you change the extension of an Excel file to ".zip", you can access the Excel document as components sorted into directories. Is there a way to save and write to a text file within one of these directories so that I can access it every time the document is opened, without having to have the user drag a log file along with the Excel document?

Upvotes: 0

Views: 460

Answers (1)

Holger Leichsenring
Holger Leichsenring

Reputation: 811

Some facts:

  • When Excel opens the file, the file is blocked by Excel. There is no possibility to write to that file within VBA
  • You can store additional data into that file externally or after the Excel workbook has been closed
  • You would need to have code externally from the workbook to accomplish writing to that file after it has been closed. You may want to use VSTO or an oldschool Excel Addin.
  • you have to ensure that Excel will not destroy your changes when restructuring or repairing the file.

In the first run, your idea sounds very natural, to not use sheets from a programmer's point of view. You only have full control on Excel files when

  • you use external libraries (e.g. Spreadsheet Gear) or
  • you remote control Excel via automation.
  • you use openxml SDK for Excel
  • you use VBA

You could insert additonal information and take care that this information is not skipped by Excel.

When you want to do the tracking this way, I would suggest you to use an Excel Addin. There is actually no need for installation when using this kind of Addin. Attach to open workbook and close workbook events and ensure that all changes are written to the Excel Workbook after it has been closed. Certainly you would have to attach to all kind of other events to track all changes to the workbook. You may need to have in mind that there can be more than one workbook opened at a time.

Actually there are alternatives.

  • write your logging code in VBA or whatever fits
  • abstract away how your persist the code (e.g. use a data provider)

  • think about these two alternatives to store logging data:

    • You can save logging data in cells of excel. When using a "newer" version of excel, you have a limit of 1 million rows. You may want to implement a rolling mechanism that ensures that you never go over the border of 1 million records. (you may be dont want to track a million changes)
    • You can use the document properties to store you information as xml.

Last but not least, the most obvious: Why not using Excel's functionality of tracking changes? Understand track changes in Excel 2013

Upvotes: 1

Related Questions