Vba to save as with path and file name

Xalova

Board Regular
Joined
Feb 11, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
What i ultimatly want to achive is that i save the active workbook under the name of the date today in the path i choose. I wrote a little code for that, that partly helped me.

the code is as follows:
VBA Code:
Sub save()
Dim location As String
location = ThisWorkbook.Path

ChDir location & "\History"
ThisFile = Format(Date, "dd-mm-yyyy") & ".xlsm"
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub

now this works. better than i hoped, actually. one little thing though. I cant override the current file, if this file already exists. When i try to save again with this macro i somehow get the error that the path could not be found. Is there any workaround for that?

Also, and that would be the cherry on top, it would be perfect if it didnt open the newly saved file, but let the original file open. What i mean is that if i save the file named "Historymaker" with this macro, it would automatically open the file "04-12-2021.xlsm", which i want to avoid. <--- is no must tho.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

KOKOSEK

Active Member
Joined
Apr 8, 2019
Messages
312
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
What do you mean that you can't overwrite already existed file?You get message about it, right?
You can avoid it by:

VBA Code:
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=ThisFile
Application.DisplayAlerts = True

with this you will overwrite existed file without message.
Running macro from file named for ex. 04-12-2021.xlsm gives you error about path because there is no \History\History path. I hope you know what I mean.
 

Xalova

Board Regular
Joined
Feb 11, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
What do you mean that you can't overwrite already existed file?You get message about it, right?
You can avoid it by:

VBA Code:
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=ThisFile
Application.DisplayAlerts = True

with this you will overwrite existed file without message.
Running macro from file named for ex. 04-12-2021.xlsm gives you error about path because there is no \History\History path. I hope you know what I mean.
i figured out why it doesnt work.

VBA Code:
ChDir location & "\History"
thats why. When i save the file i have the new file open instead of the original. And since the file is now in the folder "history" it wont find that folder again.

(im sorry if you cant understand me :/ )

how do i avoid that though?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,524
Office Version
  1. 2016
Platform
  1. Windows
I think this is what you meant. This will help
 
Solution

Forum statistics

Threads
1,141,001
Messages
5,703,656
Members
421,308
Latest member
NewBlood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top