Workbook_BeforeSave not working in excel 2007 (works fine in 2010 and 2013)

[George]

Hi All,

Really simple question, is Workbook_BeforeSave a new feature in 2010, or has it always existed?

I've created some code that should run on saving the document (and works fine in 2010/13) but does not run in 2007 - if this is a missing feature is there a way of duplicating it with the available resources?

Code excerpt below in case I've used anything else that could be causing the issue.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
Dim sSaveAsFilePath As String                                                              'where we're going to put the file
Dim Lastrow As Long
 
    On Error GoTo ErrHandler:
 
    sSaveAsFilePath = ThisWorkbook.Path & "\2013\" & Left(ActiveSheet.Name, 3) & ".txt"    'says where to save - the name of the worksheet in the 2013 folder
 
    If Dir(sSaveAsFilePath) <> "" Then
            Kill sSaveAsFilePath
    End If
 
    ActiveSheet.Copy                                                                        'Copy active sheet to new workbook
 
    ActiveWorkbook.SaveAs sSaveAsFilePath, xlTextMSDOS                                      'Save as text (tab delimited) file
 
    If ActiveWorkbook.Name <> ThisWorkbook.Name Then                                        'Double sure we don't close this workbook
        ActiveWorkbook.Close False
    End If
 
My_Exit:                                                                                    'exit the macro if it all breaks - should put something in to close the new workbook we created without saving if it's open
Exit Sub
 
ErrHandler:                                                                                'in case something goes wrong
MsgBox (Err.Description & ", call")  'so the user knows who to contact to fix this
Resume My_Exit
End Sub

Thanks guys

 

[Wookiee]

George, I use 2007 at work and I've got a file with a Workbook_BeforeSave macro that works every time. Are you getting an error message, or is the macro just completely failing to initialize?

 

[George]

it's completely failing to initialise (tried whacking a message box in at the top and nothing happened).

 

[Wookiee]

I'm sorry to say I can't think of much of a reason why the same file would work in Excel 2010/2013 but not in 2007. The only possible problems I could think of are that either you didn't enable macros for that particular workbook or the code isn't located in ThisWorkbook?

 

[George]

I think it must be a privileges thing, tried it on a pc from home and it worked fine. Thanks anyway Wookiee .