Issues with Activeworkbook.saveas


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
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.




Try changing "\Desktop" to this: "\Desktop\"



After changing to "\Desktop\" i get a run time error the dreded 1004 error. Possible reasons File name or path does not exist, fiel name being used by another program or the workbook you are trying to save has the same name as a ...
The debugger puts the error on
ActiveWorkbook.SaveAs Filename:= MyPath & "\Desktop\" & myName 

Sorry I am  attahcing a screen shot of the error message.
BJKruk (rep: 2) Aug 5, '16 at 3:17 pm
You want this to save to your desktop right?
don (rep: 1492) Aug 5, '16 at 3:23 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login