Application.SaveAs Marco

KP_SoCal
KP_SoCal used Ask the Experts™
on
For File1.xlsm, I would like to save file as (Application SaveAs) File2.xlsm in the same sub folder that File1.xlsm exists in.  If this possible to accomplish through a macro?  I assume it is.  There must be a way to save the current directory path as a variable and then modify the file name by means of a len function.  I’m just not sure how to piece it together.  Any help would be greatly appreciated.  Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
Something like this?

Dim sPath As String

sPath = ActiveWorkbook.Path & "\"

ActiveWorkbook.SaveAs Filename:=sPath & "File2.xlsm", FileFormat _
    :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Open in new window

Author

Commented:
Looks like this is going to do the trick.  I'm going to test this and post the points in a few minutes.  Thanks!
Hello,

This module will save the current workbook i the same sub-folder with current timestamp, so that you can distinguish:


Sub SaveWithTimeStamp()
Dim awb As Workbook, BackupFileName As String, i As Integer, OK As Boolean
    If TypeName(ActiveWorkbook) = "Nothing" Then Exit Sub
    Set awb = ActiveWorkbook
        BackupFileName = Left(awb.FullName, Len(awb.FullName) - 5)
        BackupFileName = BackupFileName & Format(Time, " DD-MM-YYYY hh-mm") & ".xlsm"
        awb.Save
        awb.SaveCopyAs BackupFileName
    Set awb = Nothing
MsgBox "Save As File Path with Name is : " & BackupFileName
End Sub

Open in new window

Author

Commented:
Great solutions!  Thanks guys!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial