CODEWITHSUNDEEP
excelvbaafter-save
Danny1991
Danny1991

Reputation: 3

Run a macro after saving excel-file won't work

I can't get my macro running in AfterSave or BeforeClose event.

I export one sheet to a .csv file, that's already working well with the next code when I link this macro to a button:

Sub CopyToCSV()

Worksheets("LastLots").UsedRange.Rows("1:5").Calculate
ThisWorkbook.Save
Dim MyPath As String
Dim MyFileName As String
  'The path and file names:
MyPath = "D:\Data\PW\2018\"
MyFileName = "LastLots-exported"
  'Makes sure the path name ends with "\":
If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
  'Makes sure the filename ends with ".csv"
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
  'Copies the sheet to a new workbook:
Sheets("LastLots").Copy
  'The new workbook becomes Activeworkbook:
With ActiveWorkbook
Application.DisplayAlerts = False
'Saves the new workbook to given folder / filename:
    .SaveAs Filename:= _
        MyPath & MyFileName, _
        FileFormat:=xlCSV, _
        CreateBackup:=False
  'Closes the file
    .Close False
Application.DisplayAlerts = True
End With
End Sub

The code above is in a module. As I said, when linked to a button and press that button, it works very well. But I want it running when the user saved the file. Fot this, I put the next code in ThisWorkbook: 

Private Sub Workbook_AfterSave(ByVal Success As Boolean) 
If Success = True Then
Call CopyToCSV
End If 
End Sub

I tried various things, like putting the code of the macro inside the AfterSave-event, and disable/enable Application.EnableEvents before and after calling CopyToCSV but nothing works for me...

Does anyone have a suggestion? I'm out of mind, and every topic I find on Google say to put the code in ThisWorkbook, but already done that.

Upvotes: 0

Views: 4209

Answers (1)

Daniel Möller
Daniel Möller

Reputation: 86600

Just typing the code somewhere does not make it an event handler.

You must enter "ThisWorkbook", look at the top bar, select "ThisWorkbook" and the desired event.

Excel will then automatically create the empty Sub for you, associated with the event. You insert your code inside that Sub.

Image

More details: https://www.excelcampus.com/vba/code-modules-event-procedures/

Hints: before any if, test if the script is being run, add a message such as MsgBox "After save!!!"

This will help you identify if the problem is the event not being called or your code not being run properly.

Upvotes: 1

Related Questions