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

[capple123]

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"

 

[Glove_Man]

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]

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]

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