Saving a spreadsheet in specific directory

Revelation

New Member
Joined
Mar 2, 2004
Messages
42
I would like to create a button that will save the spreadsheet as a different name in the format "Surname-Month.xls". Surname has the value from cell B10 and is in sheet 'Estimate'. The month will be able to calculated from the computers clock and in the format of 3 letters long. The spreadsheet will be saved in a directory named the year (2004) that directory will be in the directory the current spreadsheet is held. I hpe you understand me.
Eg. system.xls is currently saved in C:\spreadsheet\
cell B10 in sheet 'Estimate' has the value 'smith'
The month is March.
The save button is clicked:
C:\spreadsheet\2004\smith-Mar.xls is created.
Hope this is possible :) thanks in advance Adam.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
Saving as a file with Name, variable date, is easy enough.... it's the directory creation that gives me a headache.

:unsure:
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello

Is this OK?

Code:
Sub SAVE_AS()
ActiveWorkbook.SaveAs ("c:\spreadsheet\2004\" & Sheets("Estimate").Range("B10").Value & "-" & Format(Month(Now), "mmm"))
End Sub

This assumes c:\spreadsheet\2004 directory exists.
 

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
Try This
Code:
Sub SaveIt()

Dim MyPath as String
Dim MyName as String

    MyPath = thisworkbook.Path & "\2004"
    MyName = Sheets("Estimate").Range("B10").Value & " " & Format(Date, "mmm") & ".xls"

    ActiveWorkbook.SaveAs Filename:= MyPath & "\" & MyName

End Sub
 

Revelation

New Member
Joined
Mar 2, 2004
Messages
42

ADVERTISEMENT

Part of it is yes. Its just the directory part. I was hoping you could have more flexibility and not specify the directory, it finds it from where the system is saved.
edit: DRJ that might be what I'm after :) tyvm ill try it now. :D
is there any way you can change this to a variable that changes over time? "/2004" thx.
 

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
Your Welcome. If you run into any problems let me know.

Take Care
 

Revelation

New Member
Joined
Mar 2, 2004
Messages
42
Ok, i edited it abit to make a variable year directory (2004, 2005 etc.) and changed the save format to: Surname-day-month.xls but i get run time error 1004. what have I messed up? :)
Code:
Dim MyPath As String
Dim MyName As String

    MyPath = ThisWorkbook.Path & "\" & Format(Date, "yyyy")
    MyName = Sheets("Estimate").Range("B10").Value & "-" & Format(Date, "dd") & "-" & Format(Date, "mmm") & ".xls"

    ActiveWorkbook.SaveAs FileName:=MyPath & "\" & MyName
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

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