Insert a Tracking Module in Excel Workbooks

;Winbatch - Excel Helper code
;insert a tracking module in Excel Workbooks
;
;When needed: Assume you create a workbook with protected
;ranges, but suspect certain users might be unprotecting
;making changes, the re-protecting.
;
;The 'scode' var contains an event function which detects if such
;a change was made, and writes output to a tracking file.
;
;you can change the path and filename, and if you remove the
;If Target.Locked you will track all changes
;
;if nothing else, the script ilustrates adding events into a
;workbook. It assumes some experience working with Excel and Winbatch
;
;ALTERNATE SCENARIOS:
; 1. For situations where users work from different servers and you
;    don't want to go chasing C:\tracker.txt files. Create an
;    xlveryhidden Tab named log on the sheet and log changes there.
; 2. E-Mail each violation (this would be slick if you could keep the
;    user from seeing it.)
;
;Stan Littlefield May 5, 2005
;////////////////////////////////////////////////////////////////////////

;Create Excel Object
oXL = CreateObject("Excel.Application")
oXL.visible = @TRUE
oXL.ScreenUpdating   = @TRUE  ; if running hidden, change this to @FALSE 
oXL.UserControl      = @TRUE
oXL.DisplayAlerts    = @FALSE

;for this example, just create a new Workbook
;Otherwise select and Open() individual files
;or loop through all files in a Subdir
oXL.Workbooks.add()

;create the event module
gosub makecode


; MUST BE inserted into ThisWorkbook section
; otherwise it won't work
; NOTE: next line will fail if the Trust 'Visual Basic Project'
; is unchecked under Macro security
oXL.ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.AddFromString(sCode)

;just close the Object reference
;if working with existing files, you would issue .save or .saveas
oXL=0
cMsg = StrCat("Right-Click on the Excel Icon located",@CRLF)
cMsg = StrCat(cMsg,"the upper-right corner",@CRLF)
cMsg = StrCat(cMsg,"by the 'File' Menu.",@CRLF)
cMsg = StrCat(cMsg,"Then select 'View Code'",@CRLF)


Message("To See The Code Just Inserted",cMsg)

Exit

:makecode
;modify the two vars below and check results
cFile = "C:\Tracker.txt"
useLocked = @TRUE
;useLocked = @FALSE

scode = StrCat("Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)",@CRLF)
If useLocked Then scode = StrCat(scode,"If Target.Locked Then",@CRLF)
scode = StrCat(scode,'   TrackFile = "%cFile%"',@CRLF)
scode = StrCat(scode,"   TargUser = Application.UserName",@CRLF)
scode = StrCat(scode,'   TargAddr = Sh.Name & "!" & Target.Address(False, False)',@CRLF)
scode = StrCat(scode,"   TargVal = Target.Resize(1, 1).Text",@CRLF)
scode = StrCat(scode,'   TargDate = Format(Now, "yyyy/mm/dd hh:mm")',@CRLF)
scode = StrCat(scode,"   x = TargDate & vbTab & TargUser & vbTab & TargAddr & vbTab & TargVal",@CRLF)
scode = StrCat(scode,"   Open TrackFile For Append As #1",@CRLF)
scode = StrCat(scode,"   Print #1, x",@CRLF)
scode = StrCat(scode,"   Close #1",@CRLF)
If useLocked Then scode = StrCat(scode,"End If",@CRLF)
scode = StrCat(scode,"End Sub",@CRLF)
Return
;//////////////////////////////////////////////////////////////////////////

Article ID:   W17157

File Created: 2007:07:03:14:28:32

Last Updated: 2007:07:03:14:28:32

Database Search

TechHome

Insert a Tracking Module in Excel Workbooks