Solved: Save as date

[Keith58]

Hi

I am trying to save a work book with a date that is in a cell, the cell date will change automaicaly every week: for example
Monday 12-5-11 needs to be saved as 12-5-11
and the following Monday
Monday 19-5-11 needs to be saved as 19-5-11

Any help will greatly appreciated

Keith

[Bob Phillips]

[vba]

Activeworkbook.SaveAs Filename:=Worksheets("Sheet1").Range("A2").Text[/vba]

[Keith58]

Thanks for the reply, i am getting this error

Run-Time error '1004':
Microsoft Office Excel cannot access the file 'C:\test\15\08'.

this is where the original file is

[VBA]Sub EnterMonday()
'show userform for Monday date to be chosen
frmMonday.Show
'on return from userform, put date in cell
Sheets("Monday").Range("B3").Value = DateValue(MonChoice)

ActiveWorkbook.SaveAs Filename:=Worksheets("Monday").Range("B3").Text

Exit Sub[/VBA]

[Bob Phillips]

How does it get the value C:\test\15\08?

[Keith58]

The original file called SAVE DATE is where the file is saved on my pc
C:\Test, this has the macro, when it is run, it gives the error mentioned above

Keith

[Aussiebear]

Your system is trying to find a file within the folders \15\08 which do not exist. you need to change the way you name the file for saving.

[Keith58]

I am total new to VBA, why is it trying to find the file within the folder when i am asking it to save as, i am trying to save the file with last mondays date (this will change very7 day) inthe folder "TEST" on C Drive, sorry if i am nor being very clear

Thanks
Keith

[GTO]

[vba]Sub EnterMonday()
'show userform for Monday date to be chosen
frmMonday.Show
'on return from userform, put date in cell
Sheets("Monday").Range("B3").Value = DateValue(MonChoice)

ActiveWorkbook.SaveAs Filename:=Worksheets("Monday").Range("B3").Text

Exit Sub[/vba]

A total guess, but by chance is '12/5/11' getting plunked into the cell, rather than '12-5-11'?

If not, could you attach the file, or, if sensitive info, an example file, that accurately replicates the code and userform? Preferably in .xls format, as there are still plenty of us (yours truly included) that do not have continuous access to 2007 or later.

[Keith58]

Have up loade the test file, hope it is OK

Thanks
Keith

[Paul_Hossler]

You'll pbobably have to cusoonize this

[vba]
Sub test()

Dim sFileName As String
With Worksheets("Sheet1").Range("A2")
If IsDate (.Value) Then

sFileName = "C:\" & _
Format(Day(.Value), "#0") & "-" & _
Format(Month(.Value), "#0") & "-" & _
Right(Format(Year(.Value), "0000"), 2)

MsgBox sFileName
' ActiveWorkbook.SaveAs Filename:=sFileName
End If
End With
End Sub
[/vba]

Paul

[GTO]

Greetings Keith,

At least as shown in your included workbook, you might fix most of this by changing the NumberFormat of the cell that the date is getting plunked into.
[vba]Sub EnterMonday()

'show userform for Monday date to be chosen
frmMonday.Show
'on return from userform, put date in cell
Sheets("Monday").Range("B3").NumberFormat = "dd-mm-yyyy"
Sheets("Monday").Range("B3").Value = DateValue(MonChoice)

ActiveWorkbook.SaveAs Filename:=Worksheets("Monday").Range("B3").Text
End Sub[/vba]

That said, I would humbly mention a few things I noticed:

You fail to use Option Explicit at the top of your modules. Inclusion of this will save you headaches down the road, as mis-spelled variables and such are caught, rather than becoming mystery "Kaboom's!"

Quickly read, but if the user dismisses the userform via the 'x' button in the top/right of the form, depending on the value of a cell will go 'Thunk' as well.

I would think on using the code within the form to both set the value of the cell (if actually needed), as well as to do the SaveAs. This way, if the user cancels the form, the code doesn't fall over, running into the empty cell. Maybe:

In the Standard Module:
[VBA]Option Explicit

Sub EnterMonday()
'show userform for Monday date to be chosen
frmMonday.Show
End Sub[/VBA]

In the UserForm's Module:

[VBA]Option Explicit

Private Sub UserForm_Initialize()
'option button date choices supplied by variables
optPrev.Caption = Date - Weekday(Date, 2) + 1 - 7
' If DayNum = vbMonday Then
' txtMon.Value = Format(Date, "dd/mm/yyyy")
' optPrev = True
' txtMon.Value = ""
' End If
End Sub

Private Sub cmdOK_Click()
Dim strPath As String

'// assign the desired path. //
strPath = ThisWorkbook.Path & "\" '<--- Change to suit: strPath = "C:\Test\"

If txtMon.Value <> vbNullString Then
'// Ensure the user has not goobered the text box value into something unreadable//
If IsDate(txtMon.Value) Then
'// Optional: change the format of the cell. Especially if you stay with //
'// assigning the new filename based on the cell's text. //
ThisWorkbook.Worksheets("Monday").Range("B3").NumberFormat = "dd-mm-yyyy"
ThisWorkbook.Worksheets("Monday").Range("B3").Value = DateValue(txtMon.Value)
'// As we have assured that the textbox contains a valid date, I see no //
'// reason to depend on cell formatting. //
ThisWorkbook.SaveAs _
Filename:=strPath & Format(DateValue(txtMon.Value), "dd-mm-yyyy") & ".xls", _
FileFormat:=&HFFFFEFD1
Else
MsgBox "You must enter a valid date in 'dd/mm/yyyy' format, either by" & vbCrLf & _
"selecting one of the option buttons, or typing in a date.", _
vbCritical, vbNullString
Exit Sub
End If
Else
MsgBox "You must select or enter a date", vbCritical, vbNullString
Exit Sub
End If
End Sub
Private Sub optPrev_Click()
txtMon.Value = optPrev.Caption
End Sub[/VBA]

I wasn't sure where 'DayNum' came from or what else it was used for, so REM'd for the moment...

Hope that helps

Mark

[Keith58]

If i run this i get this error 1004 cannot access file "C:"


[vba]Sub EnterMonday()
'show userform for Monday date to be chosen
frmMonday.Show
'on return from userform, put date in cell
Sheets("Monday").Range("B3").Value = DateValue(MonChoice)

'Sub test()

Dim sFileName As String
With Worksheets("Monday").Range("B3")
If IsDate(.Value) Then

sFileName = "C:\test" & _
Format(Day(.Value), "#0") & "-" & _
Format(Month(.Value), "#0") & "-" & _
Right(Format(Year(.Value), "0000"), 2)

'MsgBox sFileName
ActiveWorkbook.SaveAs Filename:=sFileName
End If
End With
End Sub[/vba]
Thanks for your help

[Paul_Hossler]

Mark, your answer is more through, but if I "might humbly mention" I took:

Monday 12-5-11 needs to be saved as 12-5-11
and the following Monday 19-5-11 needs to be saved as 19-5-11

to mean the file name should be "0d-0m-yy" (sort of) format

Because of Regional formatting issues, I suggest it'd be better to explicitly create the filename

[VBA]
sFileName = "C:\" & _
Format(Day(.Value), "#0") & "-" & _
Format(Month(.Value), "#0") & "-" & _
Right(Format(Year(.Value), "0000"), 2)
[/VBA]

This assumes that you can create file in c:\, which is think is more different (differenter?) in Win7 than before


Paul

[GTO]

Mark, your answer is more through, but if I "might humbly mention" I took:

...Monday 12-5-11 needs to be saved as 12-5-11
and the following Monday
Monday 19-5-11 needs to be saved as 19-5-11

to mean the file name should be "0d-0m-yy" (sort of) format

Because of Regional formatting issues, I suggest it'd be better to explicitly create the filename

[vba]
sFileName = "C:\" & _
Format(Day(.Value), "#0") & "-" & _
Format(Month(.Value), "#0") & "-" & _
Right(Format(Year(.Value), "0000"), 2)
[/vba]

Hi Paul ,

Thank you for catching that. Shucks, I was happy I remembered to get the days ahead of the month, but I certainly blew that bit.

Not specific to the thread and just my opinion of course (so more at a tiny rant), but whenever I can, I try and get coworkers to save in yyyymmdd, or at minimum, mm-dd-yyyy format. It mystifies me (when saving daily type files) why some just seem to love creating monthly folders named 'Jan,' 'Feb' and so on, vs. just using a more logical file naming convention, where all the files would correctly sort in the same folder for years. Okay, I'm done venting...

This assumes that you can create file in c:\, which is think is more different (differenter?) in Win7 than before

Differenter? I like it! You sure you ain't from here in the SouthWest?

I suppose we'll have to wait on the OP to see if saves to the hard drive are a concern. Nice point though. Even in XP (at work), I do not believe this is allowed (not utterly sure at the moment).

With each "upgrade" or change of course there seem to be little glitches here and there. I don't recall exactly what I tried, but several years ago, I was testing to see if I could create a folder in C:\ and place a couple of files there, so that anyone at the workstation could use them. The PC let me create, but after testing, when I went to delete...Permission Denied!

[Paul_Hossler]

whenever I can, I try and get coworkers to save in yyyymmdd, or at minimum, mm-dd-yyyy format. It mystifies me (when saving daily type files) why some just seem to love creating monthly folders named 'Jan,' 'Feb' and so on, vs. just using a more logical file naming convention, where all the files would correctly sort in the same folder for years. Okay, I'm done venting...

1. We must have the same co-workers.

2. I just finished making a template 'International' and I learned more about different date formats than I ever wanted to know

Paul

[GTO]

1. ROFL!
2. I am so sorry, that sounds painful

[Keith58]

Thanks for your input guys, as i am total new to VBA this is way above my head , so i might look at doing manualy for now untill i can undersatnd it more and what is happening :-(,
Once again thanks for your time help

Keith

[Paul_Hossler]

Keith --

You can use Tread Tools at the top to mark this 'Solved'

Paul

[Keith58]

Have done, once again thanks for all your help

Keith