I am trying to use the BefroeClose event to trigger a SaveAs. The code mostly works but it is going to the worng directory. The workbook is being saved as C:\Users\bkrukowski and has a file name of DesktopA12345667_555Main. Code Below
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim MyPath As String, myName As String
If Len(Worksheets("Sheet1").Range("B3").Value) < 1 Or Len(Worksheets("Sheet1").Range("D3").Value) < 1 Then
Cancel = True
Exit Sub
Else
MyPath = Application.ActiveWorkbook.Path
myName = Worksheets("Sheet1").Range("B3").Value & "_" & Worksheets("Sheet1").Range("D3").Value
ActiveWorkbook.SaveAs Filename:=MyPath & myName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End If
So I changed the SaveAs to
ActiveWorkbook.SaveAs Filename:=MyPath & "\Desktop"& myName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
The file goes to the desktop but the file name now starts with Desktop
Please do not use the Generate Patch Details button if you have Outlook Open as I will receive an email with the file.