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
BJKruk (rep: 2) Aug 5, '16 at 3:17 pm
You want this to save to your desktop right?
don (rep: 1322) 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