save as(current file name,cell"a1")to current loca

capple123

New Member
Joined
Sep 5, 2007
Messages
13
I would like to create a macro to save a excel file to the current file location. I would like it to add to the end of the current file name data from cell(a1) that is a date format.

Here is the project. A excel document is creating a work schedule. The document is called "St Cloud.xls". It is a weekly schedule Mon-Sun. At the top is the date of the week. Cell(a1) is Mondays date"9/14" After completing the schedule I would like to save it as "St Cloud9_14.xls" or "St Cloud09142007.xls" or other date formats.

One thing if no to tough would be to edit last weeks schedule called "St Cloud20070914.xls" for week 9/21/2007 and just strip off the "20070914" and replace with the new cell(a1) value of "20070921"

I looked at many codes and like how many of them work only seems most people just want current date of save or cell value in the file name. Problems I have is.
1.Will save as but to the default save to location. I want to save to current file location
2 will grab data from cell(a1) but it is date format and tries to save as 9/14/2007.xls *not a good file name with "/" in it*

I would like to be prompted like this code:

*****UPDATE I"M GETTING THERE*****

Sub SaveSheet()
'error trap
On Error GoTo Etrap

Dim MyCell
Mydate = ActiveWorkbook.Name & Format(Range("a1").Value, "yyyymmdd")


'ask user to save
If MsgBox("Save new workbook as " & CurDir & "\" & Mydate & ".xls?", vbYesNo) = vbNo Then
Exit Sub
End If

'check value of activecell
If Mydate = "" Then
MsgBox "Please check the Cell Value", vbInformation
Exit Sub
End If

'save activeworkbook as new workbook
ActiveWorkbook.SaveAs FileName:=Mydate & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

Etrap:

Beep
Exit Sub

End Sub

Now If I could get the ActiveWorkbook.name not to return the .xls
and also get it to strip off the last date "20070914" for week "20070921"
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Glove_Man

Well-known Member
Joined
Feb 20, 2005
Messages
578
Sounds to me like you need to use a combination of Left and Len

Left returns only the first X charcaters of the string and Len asks the length of the string. So replace

Mydate = ActiveWorkbook.Name & Format(Range("a1").Value, "yyyymmdd")

With


Mydate = Left(ActiveWorkbook.name,Len(ActiveWorkbook.name)-14) & Format(Range("a1").value,"yyyymmdd")


The 14 represens the 8 characters in the date and 2 spaces and .xls
 

capple123

New Member
Joined
Sep 5, 2007
Messages
13
thanks

That code worked well. Only used 12 not 14. Any how trial and error got me there. This forum is very knowledgeable and a great resource. One last thing My code seems to save to the files current location most of the time. Only some of the time it seem to save to Excel's default file location. I have not dove deep into this problem so I don't completely have all the details.
 

Glove_Man

Well-known Member
Joined
Feb 20, 2005
Messages
578
Search for things like "ChDir" and "CurDir"

You have a directory called CurDir which is the current directory, if you don't specify the file to save the diretory to it will end up there. Your two options are to save the file name more fully by including the path:

eg: C:/My Documents/Excel97/Book1.xls

or to change the current directory using ChDir then utilise the fac that it saves to the CurDir

Philip
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

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