VBA code missing something

[N1k100]

I have spent hours working on this coding, it's basic and picked up bits and pieces from everywhere. It was working until I added the Time. I am missing something and maybe spent too many hours staring at it but have tried every way I can think, must be having senior moments! Can anyone see where I have gone wrong?

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Msg As String

Dim ans As Integer

Dim fname As String

Dim MyDate

Dim MyMonth
Dim MyTime

MyDate = Date

MyMonth = Month(MyDate)
MyTime = Now

Msg = "Would you like to back up file?"

ans = MsgBox(Msg, vbYesNo)

If ans = vbYes Then

ThisWorkbook.SaveAs Filename:="C:\Backup\" & MonthName(MyMonth) & "-" & Day(Date) & "-" & Year(Date) & "-" & MyTime & "-" & ThisWorkbook.Name


End If


End Sub

 

[lowtechman]

When you set MyTime to Now it actually includes the date with it. So, your filename string will look something like this:

C:\Backup\August-31-2010-8/31/2010 1-:58:49 .....

The slashes that separate the date are causing confusion in the pathname.

If you change your MyTime to:

MyTime = DateTime.Time

It should only assign the time to that variable and your pathname and filename string should work.