Trying to save as 'filename'

viggie79

New Member
Joined
Dec 10, 2015
Messages
14
Hello, I'm having trouble with what should be a very easy excel problem.

I am opening a file from my desktop and running it through a macro. I then need to save it to a specific folder and would like the macro to do this for me.
I tried just recording a macro:
Code:
 ActiveWorkbook.SaveAs Filename:= _
"C:\Users\rr235108\Desktop\xmacrostuffx\Export_12-18-15.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

The name of this file is "Export_12-18-15" but each day it will be a different file name: export_12-19-15, export_12-20-15 etc. but this macro will save every file as "export_12-18-15.xls", correct? How do I work around this?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
try
Code:
Sub savename()
    ActiveWorkbook.SaveAs Filename:= _
"C:\Users\rr235108\Desktop\xmacrostuffx\Export_" & Format(Date, "mm-dd-yy") & ".xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
 
Upvote 0
This works great for my problem, thank you so much! What if my filename was something that I couldn't fix up using the Date format, like 'Day1File', 'Day2File', 'Day3File' etc.? There must be a simple way to just save as the current filename. For example, when I save as by clicking manually, the filename field above the file type is populated automatically with the current name of the file. How can I replicate that in a macro?
 
Upvote 0
I found what I was looking for here Save as File with Same File Name | Chandoo.org Excel Forums - Become Awesome in Excel
For anyone else looking for the same thing I was; ActiveWorkbook.Name works as a variable in this case.
Final product would look something like this
Code:
ActiveWorkbook.SaveAs Filename:= _        "C:\folder\location\path\" & ActiveWorkbook.Name, FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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
Back
Top