/// <summary>
/// Before the workbook is saved, finalize the elapsed time, write the ElapsedTime CustomDocumentProperty
/// </summary>
/// <param name="Wb"></param>
/// <param name="Cancel"></param>
public void WorkbookBeforeSaveHandler(Excel.Workbook Wb)
{
log.Info("WorkbookBeforeSaveHandler - Start");
// Checks that the Workbook is being timed
if (!timers.ContainsKey(Wb.FullName)) { return; }
// Save the current Workbook FullName as previous name CustomDocumentProperty
// We will check this value after the save to update the timers Dictionary
if (CustomDocumentPropertiesContain(Wb, PREVFULLNAME))
{
// Delete existing CustomDocumentProperty if it exists
Wb.CustomDocumentProperties(PREVFULLNAME).Delete();
}
// Add a new CustomDocumentProperty saving the current FullName of the workbook
Wb.CustomDocumentProperties.Add(
Name: PREVFULLNAME, LinkToContent: false,
Type: Office.MsoDocProperties.msoPropertyTypeString, Value: Wb.FullName);
double elapsedSeconds;
// Finalize the elasped time
// First treat as a SheetChange and make sure the timer is up to date
WorkbookChangeHandler(Wb);
elapsedSeconds = timers[Wb.FullName].SaveSession();
// Write the elapsed time to a the CustomDocumentProperty
Wb.CustomDocumentProperties(ELAPSEDTIME).Value = elapsedSeconds;
log.Info("WorkbookBeforeSaveHandler - Finish");
}
/// <summary>
/// Initializes the Workbook timers upon opening or creating a new workbook
/// </summary>
/// <param name="Wb"></param>
private void Application_WorkbookOpenOrNew(Excel.Workbook Wb)
{
try
{
log.Info(String.Format("Application_WorkbookOpenOrNew: <{0}>", Wb.Name));
// Check if the workbook name is blacklisted (or in XLSTART)
if (blacklist.Contains(Wb.Name.ToLower()) || Wb.Path == Application.StartupPath) { return; }
double elapsedMinutes;
// Try to get the stored elapsed time for this Workbook, or initialize it to zero if it doesn't exist
if (CustomDocumentPropertiesContain(Wb, ELAPSEDTIME))
{
elapsedMinutes = Wb.CustomDocumentProperties(ELAPSEDTIME).Value;
}
else
{
// The CustomDocumentProperty does not exist, so created it and initialize to zero
elapsedMinutes = 0;
Wb.CustomDocumentProperties.Add(
Name: ELAPSEDTIME, LinkToContent: false,
Type: Office.MsoDocProperties.msoPropertyTypeNumber, Value: elapsedMinutes);
}
// Create a new WorkbookTimers instance for the opened Workbook
if (!timers.ContainsKey(Wb.FullName))
{
timers.Add(Wb.FullName, new WorkbookTimers(elapsedMinutes));
}
else
{
// The timer for this Workbook already existed. This should never happen, but if so, just overwrite previous timer.
timers.Remove(Wb.FullName);
timers.Add(Wb.FullName, new WorkbookTimers(elapsedMinutes));
}
log.Info("Application_WorkbookOpenOrNew - Finish");
}
catch (Exception ex)
{
log.Error(ex);
}
}
public void WorkbookAfterSaveHandler(Excel.Workbook Wb, bool success)
{
log.Info("WorkbookAfterSaveHandler - Start");
if (!CustomDocumentPropertiesContain(Wb, PREVFULLNAME)) { return; }
string prevFullName;
prevFullName = Wb.CustomDocumentProperties(PREVFULLNAME).Value;
// Check if any timer was counting for the file with the previous name
// If so, change the Dictionary key to be the new Workbook's FullName
if (timers.ContainsKey(prevFullName))
{
WorkbookTimers tempWorkbookTimers = timers[prevFullName];
timers.Remove(prevFullName);
timers.Add(Wb.FullName, tempWorkbookTimers);
}
// Delete the PrevFullName CustomDocumentProperty
Wb.CustomDocumentProperties(PREVFULLNAME).Delete();
log.Info("WorkbookAfterSaveHandler - Finish");
}
