Save file in new location.

[Nanogirl21]

Is there a VBA code that I can add the end of my macro that will save the file with todays date (YYYY-MM-DD format) at a specific location? The location is different than the orginal document.

Location: C:\Users\nanogirl\Desktop\2018 WIP\Reports
File Name Example: Metrics 2018-05-24.xlsb

Thank You

 

[ranman256]

Re: Save file in new locaiton.

VFile = "\\server\folder\NewFile" & Format("yyyy-mm-dd") & ".xls"
activeworkbook.saveAs vFile

 

[Nanogirl21]

Re: Save file in new locaiton.

VFile = "\\server\folder\NewFile" & Format("yyyy-mm-dd") & ".xls"
activeworkbook.saveAs vFile

Thank you.

I am assuming

 \\server\folder\NewFile

would be my file path of the location. Would the part that says "new file" be called whatever I want before the date? In my example above "new file" would be "Metrics".

Also, I just thought of an error that may occur.
If there is already a document in the folder with the same name (in my example there would already be a document called Metrics 2018-05-24.xlsb) is there a way to overwrite/replace the "old" file without getting a pop up to confirm?

 

[Nanogirl21]

Re: Save file in new locaiton.

I tried the below code and it did not work. The report saved at

C:\Users\nanogirl\Desktop\2018 WIP

with the name

Reportsyyy-mm-dd.xlsb

VFile = "C:\Users\nanogirl\Desktop\2018 WIP\Reports" & Format("yyyy-mm-dd") & ".xlsb"
activeworkbook.saveAs vFile

How do I get the report to save in the folder called Reports with todays date at the end of the file name?

Thank you.

 

[John_w]

Re: Save file in new locaiton.

Put a back slash after Reports.

 

[Nanogirl21]

Re: Save file in new locaiton.

Put a back slash after Reports.

Thank you. Adding the \ did save the file in the correct location. However, the file name is incorrect. I'd like to keep the orginal file name and add todays date to the end (example: Group Lunch 05-20-2018.xlsb). Instead the file is saving as the name mm-dd-yyyy.xlsb. The name is exactly like that and not even populating the date. Do you know how to fix this?

 

[John_w]

Re: Save file in new locaiton.

Try this:

    Dim p As Long, fileName As String
    
    With ActiveWorkbook
        p = InStrRev(.FullName, ".")
        fileName = Left(.FullName, p - 1) & Format(Date, " yyyy-mm-dd") & Mid(.FullName, p)
        .SaveAs fileName
    End With

 

[Nanogirl21]

Re: Save file in new locaiton.

Try this:

    Dim p As Long, fileName As String
    
    With ActiveWorkbook
        p = InStrRev(.FullName, ".")
        fileName = Left(.FullName, p - 1) & Format(Date, " yyyy-mm-dd") & Mid(.FullName, p)
        .SaveAs fileName
    End With

Where do I input the file path of WHERE I want the document to save?

 

[John_w]

Re: Save file in new locaiton.

The file path is already there in the above code because the FullName property includes the full path and file name of the workbook, in this case the active workbook.

 

[Nanogirl21]

Re: Save file in new locaiton.

The file path is already there in the above code because the FullName property includes the full path and file name of the workbook, in this case the active workbook.

This would not work. The orginal file path is on a server that I don't want to save in. That is why there is a location that is on my desktop to save the file after I make my updates with my macro. I cannot move the file from the server and the file on the server is constantly refreshed.