Can code in an Add-In evaluate every workbook opened?

EdFromAZ · Apr 23rd 2013

Many of my users are sporadic about making back-up copies of their workbook files. These are workbooks based on a template that I built, and all use an add-in that I created and keep up-to-date. I'm trying to think of ways to "help them" make back-ups.

It's too late to put code in This Workbook for the exisiting files (but the template has been updated for new files). But I wondered if I jcould put code in the add-in that would do this? The code would have to evaluate every Excel file the user opened; if a certain named range was in the workbook, then I could simply use SaveCopyAs to make a back-up and write the date or some other "code word" into the file so it only gets done once on any given day.

If this were on my own computer, I know I could put code using the Application object in my Personal macro workbook. And I did see Chip Pearson's page on doing things like that. But I couldn't see if it could be adapted for use in an add-in.

Has anyone else done this?
Ed

**royUK** · Apr 24th 2013

Re: Can code in an Add-In evaluate every workbook opened?

If you can put code in your Personal workbook then you can put it into an add-in

cytop · Apr 24th 2013

Re: Can code in an Add-In evaluate every workbook opened?

Out of interest, which Pearson page are you referring to...?

EdFromAZ · Apr 24th 2013

Re: Can code in an Add-In evaluate every workbook opened?

royUK: When I put the code in my add-in, it only ran when the add-in loaded. It wouldn't see every Excel file as it opened after the add-in loaded.

cytop: http://www.cpearson.com/excel/appevent.aspx

Ed

cytop · Apr 24th 2013

Re: Can code in an Add-In evaluate every workbook opened?

To summarise CPs page (This is taken from something I knocked up myself)

Add the following to the ThisWorkBook class module

Code

Option Explicit


Private WithEvents oXL As Excel.Application


Private Sub Class_Initialize()


    Set oXL = Excel.Application
    
End Sub




Private Sub oXL_NewWorkbook(ByVal Wb As Workbook)


    MsgBox "The user opened a new workbook...", vbInformation, "New"
    
End Sub


Private Sub oXL_WorkbookOpen(ByVal Wb As Workbook)


    '// Ignore this workbook opening...
    If Wb.Name <> ThisWorkbook.Name Then
        MsgBox "The user opened workbook " & Wb.FullName
    End If
    
End Sub


Private Sub Workbook_Open()


    Set oXL = Excel.Application
    
End Sub

Display More

Or have a look at the attached - that is an XLSM file rather than an add-in, but the principal is the same.

This uses a variable (oXL) declared as WithEvents. This caused Application events to be mirrored to the Workbook class. You can then do what you want. There's sample code (a fancy term for a couple of MegBox's) that execute when a Workbook is loaded or a new blank workbook opened. If you were to use this then I'd suggest the WorkBook_BeforeSave event would be a good one to use to track which workbooks are saved. After all, workbooks might be opened, but closed again without saving...

EdFromAZ · Apr 24th 2013

Re: Can code in an Add-In evaluate every workbook opened?

Thanks, cytop. I tried something like this last night, but as I mentioned it would only fire for workbooks openeing as the add-in was initially loading. Once the add-in was opened, the code didn't fire for any other workbook that opened up.

I'll give it another shot in a little bit and we'll see what happens.
Ed

cytop · Apr 24th 2013

Re: Can code in an Add-In evaluate every workbook opened?

Worked fine for me... either as an add-in or regular workbook.

snb · Apr 24th 2013

Re: Can code in an Add-In evaluate every workbook opened?

If you save Cytop's file or an addin in the Excel startup directory it will be loaded before anything else.

EdFromAZ · Apr 28th 2013

Re: Can code in an Add-In evaluate every workbook opened?

Sorry for the delay. Couldn't download the file so I created an add-in and manually added the code. Works great!! Thank you!!

Ed

Can code in an Add-In evaluate every workbook opened?

Participate now!

Share